Locking sheet number when copying cells

[Roosters]

I have 4 identical sheets in layout with data and have a 5th totals sheet. I have formula taking data from sheet one to the totals sheet and wanted to copy them down to retrieve the info from the other 3 sheets - can I 'pin' the cell address and copy down changing the sheet # - ie

='Sheet 2'!$D$3 <--copy down
='Sheet 3'!$D$3 <--resulting in this
='Sheet 4'!$D$3 <--and this

 

[SkipVought]

Hi,

Take a look at the INDIRECT function

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Roosters]

Hi Skip
Sorry but I don't understand - I looked at the INDIRECT function but am unsure of how to apply it to my problem - any clues you could give would be appreciated.

Phil

 

[Lilliabeth]

If you had the worksheet names listed in Column A of the fifth sheet:

=INDIRECT("'"&A1&"'!$d$3")

 

[Roosters]

Works fine in this eg but can this method be used to allow the column reference to change if copied across a sheet ie change from =INDIRECT("'"&A1&"'!d3") to =INDIRECT("'"&A1&"'!d4")
I need it to do this but I can't get it to work
Phil

 

[Lilliabeth]

There certainly may be a cleaner way, but here's what will work:

Sheet names down col A.
Put d3 in cell B1 and copy across

Use this formula:
=INDIRECT("'"&$A1&"'!"&B$1)

 

[byundt]

The CELL function will return an address that increments as you copy it across. Assuming that sheet names are in column A:
=INDIRECT("'" & $A1 & "'!" & CELL("address",D$3))

The preceding formula will return a value from row 3 on each of the worksheets listed in column A. It will return values from different columns as you copy the formula across. If the referenced cell is blank, the formula returns 0. If this is a problem, then try:
=IF(INDIRECT("'" & $A1 & "'!" & CELL("address",D$3))="","",INDIRECT("'" & $A1 & "'!" & CELL("address",D$3)))

Brad
)