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.