Afternoon all,

A colleague has a spreadsheet that contains data about training sales and revenue. There is a lot of data and columns. 2 of the columns are GBP and Euro. If the customer pays in Euro, the euro amount is entered and zero shows in the GBP column and vise versa.

They would like to be able to get all the GBP data onto a seperate sheets and the Euro data onto another, but they want it to update when a new entry is placed on the original sheet.

If it was a one-off I could do it easily, but getting it to update is the thing I am struggling with.

Any help would be great.

Thanks

You can use copy and Paste special, then select paste link.

That way you can have the same data in a different sheet but it will always be updated by the prime sheet.

I did think of that, but the number of rows are going to increase in a non-uniform manor. It's actually a very poorly built spredsheet hence being in this situation. If it was my Dept I would be rebuilding or databasing it.

Thinking a recorded Macro might work hhhhmmmm

I wouldn't use a recorded macro - they're really not good at adjusting to sizes in the sheet change.

I'm happy to help out, if you want to wing me a copy of the sheet.

Cheers AndrewR wanna PM me your email?

What about using pivot tables to update from the master?

Pivot tables

I would set a pivot table up had the data not been so badly laid out.

What the reason why they want seperate sheets?

best not ask that one

The reason I asked is if user(s) don't understand whats data and whats referenced there is a risk is people make "corrections" in the copied/referenced sheet which would not be reflected in the master copy.

I was wondering if putting filtering on might achieve what they want as an alternative to pivot tables, at least your only looking at one version of the truth.

You could always protect the copied/reference sheet to stop any meddling on that front.

You can cross reference information between sheets using sheet1! Sheet2! Etc in your formula