Correcting consolidated Sheet that auto corrects all other sheets.

[TERRI LEE]

Hi

I have a macro that consolidates 31 sheets into one master sheet. Is it possible to make corrections on the consolidated worksheet that automatically will update all the other sheets and vice versa.

Thanks

[TMS]

Provided that there is some means of identifying the individual source sheets, I should think so.

You'd need worksheet change events or a workbook sheet change event to monitor cells and make the necessary updates.

Regards, TMS

[TERRI LEE]

How do I achieve this? i have very limited knowledge of macros.

[TERRI LEE]

Can Anyone help?

[arlu1201]

In your consolidated sheet, do you have a column that states which sheet that data came from?

Also, is there a unique identifier to quickly find that row in the sheet so that it can be updated?

I would say, to avoid all these complications, you can probably clear the contents of the 31 sheets and have them re-populated every time a change is done to the consolidated sheet. This will work only if there are no manual changes being done to the 31 sheets.

[TERRI LEE]

@ arlu1201

No

I am not sure I understand what you mean here.

The 31 sheets are the sheets that we manually capture data into on a daily basis. we then run the macro to consolidate these sheets into the "master" thus enabling us to quickly check for errors. However if we want to correct the errors we will need to then correct them on the 31 sheets as we have other sheets that get info from them. This is very time consuming so I was wondering if we could correct it in preferably the master sheet that then updates to the 31 sheets.

The macro for this workbook was written by yourself and is under the post of:
Re: Coping certain sheets in workbook into one sheet

[TERRI LEE]

I once again see an issue with the formulas in the master sheet. Instead of it just coping the values across it copies the formula but it then messes up my numbers in the master worksheet. It seems to try to recalculate the formulas with a different number.

[TERRI LEE]

Hi

Is it possible to make corrections on the macro created consolidated sheet that then will correct back to each sheet that the macro original read from?

Cheers
Terri

[arlu1201]

Is it ok if the macro clears the contents of the sheets 1-31 and re-feeds them from the master file?

[TERRI LEE]

Is it ok if the macro clears the contents of the sheets 1-31 and re-feeds them from the master file?

I am not sure as the original info is entered into sheets 1 - 31 and I don't want to loose any of the info here ( data, layout, formatting and formulas etc). How exactly will it work?

[arlu1201]

If there is a way to identify from which sheet the data came, it can clear the contents of that sheet and copy back the data from the master file.

[TERRI LEE]

The date within the schedule corresponds to the sheet tag I.e sheet 1. = 1 st of whichever month

[TERRI LEE]

Hi

Did you manage this one????

[TERRI LEE]

Hi

Can anyone help with this thread???

[arlu1201]

What if we clear the contents of Sheets 1-31 and then input the formatting and layout as you require ?

[TERRI LEE]

Hi Arlu

The issue is not with the formatting. It is with the data entered sometimes being incorrect. When we run the consolidation schedule we then use this schedule to quickly pick up errors. The problem is once you find the error you then need to go back into the source document and correct it there and then run the consolidation macro again to pull the correction thru. It would be easier if we could correct it on the consolidated schedule that then automatically corrects the source schedules.

I hope this makes sense

[arlu1201]

Ok i get your point. Will there be any issue if we clear the sheets 1-31 and load it from the master so that the corrections will flow thru as well?

[TERRI LEE]

There are alot of other schedules that pull info from sheets 1 - 31 so I am not sure how this would affect them.

[arlu1201]

Are there formulae which pull the values from sheets 1-31 or is it macros?

[TERRI LEE]

Hi

It's formulae. One day when I have time I want to change it to a macro as I think it will be easier as well as more fool proof.

[TERRI LEE]

I will try to post a blank copy of the entire work book but I will need to break it up into segments as it is too big.

[TERRI LEE]

Here are the workbooks. Please note that all of these would make up one workbook ONLY.

[TERRI LEE]

Hi

What do you think?

[arlu1201]

There are loads of formulae in your sheets. Is it ok if only the data (without formulae) is replaced in your file?

For e.g. the formulae can be left as is and the data can be replaced with the data from the master sheet.

[TERRI LEE]

There are loads of formulae in your sheets. Is it ok if only the data (without formulae) is replaced in your file?

For e.g. the formulae can be left as is and the data can be replaced with the data from the master sheet.

Yes that can work but then I guess all my macros would need to change again.

[arlu1201]

Why would your macros need to change?

[TERRI LEE]

Hi. Can you believe I just replied to the email instead of the forum.

I guess you are right because they would still take the info from the sheets numbered 1-31

[arlu1201]

If we clear the rows completely, we will need to input each formula. Instead, we can just replace the values.

[TERRI LEE]

Okay so will it be a macro that runs this

[arlu1201]

Which field in the data will help to identify from which sheet the data has come from?

[TERRI LEE]

I am not sure what you mean here. Each sheet is basically a summary of certain info from sheets 1-31. I guess if all the summary sheets could draw the info from the master sheet that could also work.

[arlu1201]

Sorry if i misunderstood. Your workbook contains of sheets 1-31 and the master sheet right? Any other sheets i am missing out?

[TERRI LEE]

Hi

Sorry for the delay. I have been on leave.

I attached all the docs in post 22. All these basically make up the one workbook.

Thanks
Terri