using indirect throughout a sheet

[Guest]

I've spent the last couple hours reading through the threads related to
INDIRECT and that's been beneficial. However, I have a sheet, sheet1,
populated with a number of different formulas that reference another sheet,
sheet2. I'd like to make every reference in sheet 1 indirect
'Sheet1'!A1 -> INDIRECT("'Sheet1'!A1")
and I'm running into 2 problems.
1. I can't do a search and replace since excel won't let me partially
update the formula.
2. If I hand adjust one instance of a formula and then try to drag it down,
it just keep repeating the A1 (instead of A2, A3,...)

thanks for the consideration.

 

[Guest]

Some thoughts ..

1. Assume we want to reflect on call, what's within A1:E10 in Sheet1,
Sheet2, etc in a summary sheet

In the summary sheet,
Let's say B1 will house the sheetname of interest, eg: Sheet1

Then we could place in say, B2:
=IF($B$1="","",OFFSET(INDIRECT("'"&$B$1&"'!A1"),ROW(A1)-1,COLUMN(A1)-1))
and copy B2 across to F2, fill down to F11 to cover an equivalent grid (for
A1:E10)

B2:F11 will return the contents of A1:E10 from the sheetname input in B1, ie
from Sheet1. Changing the input to Sheet2 returns correspondingly. We could
also create a simple DV in B1 to ease the selection of the desired sheet via
selecting B1, then click Data > Validation, Allow: List, Source: Sheet1,
Sheet2, Sheet3 then click OK.

2. Instead of using: =INDIRECT("'Sheet1'!A1"),
use: =INDIRECT("'Sheet1'!A"&ROW(A1))
Then you can copy down to increment accordingly.

 

[Guest]

Thanks Max. Option 2 worked well for modifying formulas along the top of my
sheet and then dragging them down. I'm assuming I could replace ROW() with
Column():
use: =INDIRECT("'Sheet1'!A"&ROW(A1))
becomes
use: =INDIRECT("'Sheet1'!A"&COLUMN(A1))

and get the desired result when I drag a formula left to right across a sheet.
appreicate the tips.

qdave

 

[Max]

qdave, you're welcome.

Yes, ROW(A1) and COLUMN(A1) can be used as incrementers within formulas when
copying down / across. But do tinker with it to ensure that the results
returned are exactly as desired.

For example: =INDIRECT("'Sheet1'!A"&COLUMN(A1))

The above effectively returns a dynamic transpose of Sheet1's col A as we
copy it across. It returns the "vertical" contents of A1,A2,A3 ... etc in
Sheet1, in a horizontal fashion. And sometimes, that's exactly what's
wanted.

But if we wanted it to return Sheet1's A1,B1,C1... as we copy across
we could use instead either:

=INDIRECT("'Sheet1'!"&CHAR(COLUMN(A1)+64)&"1")
above can be copied across 26 cols [returns for cols A - Z]
(there are other, more complex variations to handle beyond col Z)

Instead of the above, a better one might be:
=INDEX(INDIRECT("'Sheet1'!1:1"),COLUMN(A1))
above can be copied / will work right across all 256 cols

or the versatile but volatile:
=OFFSET(INDIRECT("'Sheet1'!A1"),ROW(A1)-1,COLUMN(A1)-1)
which allows "straight-through" copy across and down for "as-is" linking (no
transposing)

Of course, the real flexibility / benefit would be to point to a cell(s) for
the text parts (parts within quotes) within the INDIRECT instead of
hardcoding it as shown in the examples above.