Hi,
I"m new to this forum. and here's my first question.
I have a large spreadsheet with multiple sheets - hundreds. SHEET1, SHEET2, SHEET3, etc (not these consecutive names though)
I have a master sheet which takes in information from all sheets. I would like to use a cell reference with the sheet name and the the cells, say around 10 columns and 200 rows. (so I need to make a formula for cell one that can be copied across and down to make the thing happen as a common formula for all cells. I think I'm half the way there now, but am stuck.
in the master sheet I have a cell in Column A, starting in row 1, with the entry "SHEET1" which I have copied down 200 rows. next I have "SHEET2" which is copied the next 200 rows.
In column B, i made a formula "=INDIRECT($A1&"!A1")" to go and grab the cell value in "SHEET1" Cell A1, this works for SHEET2, etc.
However.... T0his formula half works, because, when I copy it across the columns and down the columns, it still references cell A1, for all. I want it to do the array of cells:
A1, B1, C1, etc
A2, B2. C2 etc
but it doesnt work and i get
A1, A1, A1
A1, A1, A1,
I realize that the problem is that i told the function to do exactly this.... I can't figure out how to make it copy across and down like it normally would.
I ultimately want column A of the master sheet to simply have the sheet names down it say 1-200 are SHEET1, and 201 to 400 are SHEET2, etc, then the cell entries in the next 10 columns are simply formulas so that they can be copied all the way down to infinity.
Thanks, and I hope this is clear
Bookmarks