Getting data from multiple sheets...

[passer-by]

Hey, guys!

I have a bit of a problem with my work, so any help would be greatly appreciated.

Here`s the deal. I have a huuuuuge column of numbers sorted in alphabetical order (I used simple 1-digit numbers in the example file (Sheet1, column B), no point uploading the real file i`m working on, it`s too big ). I also have the same number of worksheets in the file named after each and every number present in the column. In each of those sheets I have a number in cell A3.

What I need to do is somehow get that number into "Sheet 1" next to the one that is identical to the sheet name that number is originally from (sorry if it sounds too complicated, I don`t speak English too well ).

For example, in cell D2 there`s supposed to be the number from sheet "1" (123), in cell D3 - the number from sheet "2" (356) and so on...

Of course you can use a very simple referense like "='1'!A3" for cell D2, but when you drag your formula down to fill all the other rows it gets ruined because Excel starts modifying the last number (when it shouldn`t) and does not modify the sheet name (when it should). I guess I need something more complicated...

Any help?

Thanks.

[Bryan Hessey]

Hey, guys!

I have a bit of a problem with my work, so any help would be greatly appreciated.

Here`s the deal. I have a huuuuuge column of numbers sorted in alphabetical order (I used simple 1-digit numbers in the example file (Sheet1, column B), no point uploading the real file i`m working on, it`s too big ). I also have the same number of worksheets in the file named after each and every number present in the column. In each of those sheets I have a number in cell A3.

What I need to do is somehow get that number into "Sheet 1" next to the one that is identical to the sheet name that number is originally from (sorry if it sounds too complicated, I don`t speak English too well ).

For example, in cell D2 there`s supposed to be the number from sheet "1" (123), in cell D3 - the number from sheet "2" (356) and so on...

Of course you can use a very simple referense like "='1'!A3" for cell D2, but when you drag your formula down to fill all the other rows it gets ruined because Excel starts modifying the last number (when it shouldn`t) and does not modify the sheet name (when it should). I guess I need something more complicated...

Any help?

Thanks.

Hi,

as shown, in D2 put

=INDIRECT(ROW()-1&"!A3")

and formula fill that down as far as required.

To use the numbers from column B as Sheetnames, try

=INDIRECT(B2&"!A3")

To use Sheet1 Sheet2 Sheet3 etc, try

=INDIRECT("Sheet"&ROW()-1&"!A3")

hth
---