worksheets as series

[Guest]

I am using the topsheet of my workbook to reflect the information on all subsequent worksheets - each worksheet is represented by a row. I have a ton of worksheets that have already been filled out, and would like to be able to make the worksheet numbers update sequentially, while keepign the cell value at a constant. ( =('1142'!C8) in A1 to change to =('1143'!C8) in A2)
When I hold down the CTRL button and drag down the column, the cell value changes sequentially, and the worksheet stays the same (=('1142'!C8) in A1 goes to =('1142'!C9)in A2).

This is driving me crazy! Can you help?

 

[Ken Wright]

Something along the lines of

=INDIRECT("'"&ROW()&"'!$C$8")

If you were starting this formula on say Row 2, and the first sheet you wanted
to trap was '1142'!C8 then you would need something like

=INDIRECT("'"&(ROW()+1140)&"'!$C$8")

and then copy down. NOTE though, that any inserting or deleting of rows prior
to these formulas will adjust them all, so you may want to build the formula, eg
in say B2 you had 1140 and then filled down so you had 1141, 1142 etc, then in
A2 you could use something like:-

=INDIRECT("'"&B2&"'!$C$8")

You have to build the reference as a string, and then the INDIRECT function
allows you to convert that to a valid formula reference.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I am using the topsheet of my workbook to reflect the information on all

subsequent worksheets - each worksheet is represented by a row. I have a ton of
worksheets that have already been filled out, and would like to be able to make
the worksheet numbers update sequentially, while keepign the cell value at a
constant. ( =('1142'!C8) in A1 to change to =('1143'!C8) in A2)

When I hold down the CTRL button and drag down the column, the cell value

changes sequentially, and the worksheet stays the same (=('1142'!C8) in A1 goes
to =('1142'!C9)in A2).

 

[Guest]

Sorry to sound like a dope - but just so I'm clear, I type =INDIRECT("'"&(ROW()+1140)&"'!$C$8") in the cell where I want to reflect info onto row 2 from sheet 1142 in cell C8?
I think I understand that, but - each cell in the row references a specific cell in the various worksheets, so while Column A needs C* to stay constant, Column B needs J12 to stay constant. What now? Also, some ofthe info is text, is there a way to allow that?
Thank you again for your help - I really appreciate it!
Virginia

 

[Ken Wright]

No problem, and you should just be able to edit the formula with the other data.
The ROW() bit by the way, simply returns a value of 2, so ROW()+1140 gives you
1142 in the string you are building. If the sheet names are in some kind of
sequential order that you can build with a pattern, ie they increment by 1 each
time, or 2 etc, then this works fine.

If your formula in Col A, eg

=INDIRECT("'"&(ROW()+1140)&"'!$C$8")

brings back cell C8 from the right sheet, then

=INDIRECT("'"&(ROW()+1140)&"'!$J$12")

in Col B should bring back the contents of J12 for you, regardless of whether it
is text or numeric.


As I said, the ROW()+1140 bit simply gives you 1142, but when copied down, will
increment by 1 for each row, such that

In cell
A2 you have ROW()+1140 which equals 2 + 1140 = 1142
A3 you have ROW()+1140 which equals 3 + 1140 = 1143
A4 you have ROW()+1140 which equals 4 + 1140 = 1144
A5 you have ROW()+1140 which equals 5 + 1140 = 1145

These in turn build the following strings within the INDIRECT function

=INDIRECT("'"&1142&"'!$C$8") = INDIRECT("'1142'!$C$8")
=INDIRECT("'"&1143&"'!$C$8") = INDIRECT("'1143'!$C$8")
=INDIRECT("'"&1144&"'!$C$8") = INDIRECT("'1144'!$C$8")
=INDIRECT("'"&1145&"'!$C$8") = INDIRECT("'1145'!$C$8")

which is the same as typing in

='1142'!$C$8"
='1143'!$C$8"
='1144'!$C$8"
='1145'!$C$8"

You don't really need the single quotes around the 1142,1143,1144,1145 bit, but
if your sheet name had spaces in it you would, so safer to leave them in for
syntax purposes.

Once you get your head round that, you can do anything you like with numbers to
build those strings, as long as you can identify a pattern, otherwise you will
be building them manually, or using VBA to do it.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Sorry to sound like a dope - but just so I'm clear, I type

=INDIRECT("'"&(ROW()+1140)&"'!$C$8") in the cell where I want to reflect info
onto row 2 from sheet 1142 in cell C8?

I think I understand that, but - each cell in the row references a specific

cell in the various worksheets, so while Column A needs C* to stay constant,
Column B needs J12 to stay constant. What now? Also, some ofthe info is text,
is there a way to allow that?

Thank you again for your help - I really appreciate it!
Virginia

<snip>