Changing Cell References inside INDIRECT function.

[Zox Tomana]

Howdy. So I'm fairly inexperienced with Excel, but have gotten myself into a project where something I want to do seems to have turned out to be something of a chore. I have multiple sheets, and I want to have a single summary sheet that pulls data from one of the other sheets based upon the contents of a specified cell on the summary sheet. Thanks to Google and some posts on this very site, I've figure out how to do that:

Please Login or Register  to view this content.

where the second "A1" is just an example. Now here comes the problem that brought me here: I need to 1. be able to drag-autofill the formula across many cells such that the second A1 is changing to refer to new, specified cells on the other sheet, and 2. have the cell references increment by more than 1 when filling down the column (specifically I need it to increment by 4).

This summary sheet it going to be pulling whether or not a radio has been signed out, and who has had that radio, during a particular day. It is the "who" part that involves the trouble, because each "who" is four rows down from the last. From my table:

C3: Who:
C4: Out:
C5: In:
C6: [BLANK ROW]
C7: Who:
C8: Out:
C9: In:

Therefore the cells (say) B2, B3, B4 need to pull from C3, C7, and C10.

So, any clues on how to get the second A1 to increment, and how to get it to increment by 4?

[Jonmo1]

Try

=INDIRECT($A$1&"!A"&(ROWS($A$1:$A1)-1)*4+3)

[Zox Tomana]

That works perfectly going down the column once I get it switched out to the right row I want to pull from (changed the A's past the first to C's). Can it be modified to, at the same time, vary as you pull it across a row? Or does it need to have one (either row or column) be specified for the other to work? I can compromise and manually edit it for the start of each column.

EDIT: Please pardon the mistake, I said "down the row" instead of "down the column". I've fixed it.

[Lemice]

If you want it to change when you drag it up/down (row) but not left/right (column), change $A$1 to $A1
If you want it to change when you drag it left/right (column) but not up/down (row), change $A$1 to A$1
If you, well, want it to change however it is when you drag it, change to ... A1

Or are you trying to change the A in "!A" part in the INDIRECT formula that Jonmo1 gave you? Can you clarify a bit more on what part you want to change when you "drag" the formula?

[Zox Tomana]

I am trying to change the A in the "!A" portion, yes.

[Lemice]

You can try changing "!A" to

"!"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

In Column A, the SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"") formula will return "A", if you want it to return "A" in Column C you will need to change it to

SUBSTITUTE(ADDRESS(1,COLUMN()-2,4),1,"")

Notice the "-2" there? I suggess messing around with it. If you want to see what it does, just put a "=" in front of it and it will be a normal formula, and you can paste it in any cell to see what it does / what you can change to make it do what you want.

[Zox Tomana]

That pretty solidly solves it, Lemice, thanks =)

My final formula: posted in cell A1, taking the sheet name from G1, and drawing data a table whose useful info starts in column F is this:

Please Login or Register  to view this content.

And a quick test of dragging it around and seeing if it grabs the right data from some test inputs makes it seem like it works... so... Thank y'all so very much for all y'all's help!