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.

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"),RO W(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"qdave" wrote:
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.

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" wrote:

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"),RO W(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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"qdave" wrote:
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.

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"qdave" wrote in message
...
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