pulling data from a master worksheet

Hi, all seems like I stumbled across a gem of a forum! Anyways, was hoping you could help...

I am constructing a model and I have a master worksheet which contains all my raw data and inputs, and then about 50 worksheets (each representing a year 1960-2008) which perculate the data through 40 years of assumptions. I have created each worksheet at the same time, so that even though each sheet contains a different years values, the same cell on every sheet represents the same year, value, etc.

I was wondering if there is anyway to pull data from the master worksheet into each worksheet in order simply, such that in worksheet "1960" cell B12 = "'master worksheet' cell c4", and then worksheet "1961" cell B12 = "'master worksheet' cell c5", etc for the 50 (sheets) I have.

Thank you very much in advance and please let me know if I am not being entirely clear about anything

Hi, and welcome to the forum.

If the sheet tab names are all called 1960, 1961 etc. then you could use the following.

Please Login or Register  to view this content.

It works out what the sheet name is called and then deducts 1956 from that. So if the sheet is 1960, it evaluates to 4, and if the sheet name is 1961 it evaluates to 5 etc. The Indirect adds the column 'C' reference.

If the sheet holds the name of the year in a consistent cell, say C1, then you could simplify this and use:

Please Login or Register  to view this content.

HTH

hey thanks so much, great intuitive thinking and useful formula!


For a different part of this workbook I have an input that moves over one cell in each subsequent year i.e. '1965' it is in J15, then in '1966' it is in K15; I still need to pull the raw data from the same master worksheet, any suggestions to make this easier so I dont have to go in and do it manually for each year?

Hi,

Use the same Indirect technique as in the earlier post. The trick here is to work out the formula for the J15 cell, then when you're happy that's correct, you need to wrap the master sheet reference in an OFFSET() function. So suppose the relevant column on the master sheet was M, for the Reference element specify $M plus whatever the row is; for the row offset put 0; and for the column offset enter "COLUMN(M1)-13". The Column(M1) will evaluate to 13, from which 13 is deducted resulting in a zero. When this is copied to K15, the offset will make the column offset to result in a '1' i.e. Column N (14) - 13 = 1, indicating column N on the master sheet.

HTH

hey thanks again for all the help! I have applied your technique in various ways now!...

Now for a simple ( i think ) question...

As indicated earlier I wrote the year pages so each one is identicle except for what year it represents, that way I was able to highlight all 40 pages and just do all the work on one page, thus saving a lot of time....

However, now that I am adding these formulas that you so kindly helped me with when I highlight all the pages sometimes it appears to write on all of them (as I am trying to do) and sometimes it doesn't seem to write on all of them, Is there some minor detail I am neglecting, because currently there appears to be no rhyme or reason as to when it will actually write on all of them (esp when I am pasting formulas) and when it wont,, I feel like i am on the verge of a time saving epiphany and this is the last barrier to my success!

Hi,
Can you explain what you mean by highlighting? Do you mean you're selecting all the sheets and copying and pasting? If so it sounds like you're missing one or two out.

Rgds

By selecting, I mean highlighting. SO i would start with 1960 and SHIFT+click to 2007 to highlight all the sheets and then do work in one of the sheets. This is how I originally created the workbook and when I did so all the sheets that were highlighted would produce a carbon copy of the sheet I was working on. This is what I intend to do now, but as mentioned sometimes not all the sheets are appearing to show the work I am doing even when it shows that all the sheets are highlighted