Indirect reference help

[CROY1985]

Hi all

In column b i need a formula which usues whatever is in column a, as the sheet reference. Hereis what i have so far.

=INDIRECT("'" & $A13 & "'!" & a1)

In cell a1, i have the cell reference i want to look at.

I need to adapt it so that it takes the sheet name from column a, but i give the cell reference as part of the formula - opposed to having the formula look at a cell where the cell reference is.

Thanks

 

[jasonb75]

=INDIRECT("'" & $A13 & "'!A1")

Although this doesn't give relative cell references if you fill it down.

 

[CROY1985]

Update:

=INDIRECT("'" & $A10 & "'!" & "b17")*Inputs!$E24

I now have this. I need to change it so when i copy pste into the next column, the b17 changes to c17, so i need that part of te indirect formula to be dynamic.

Thanks

 

[jasonb75]

Which cell is the formula going into?

 

[CROY1985]

Hi

Sorry for late reply. I had a change of plan,and wanted to do the sheet differently. However, out of interest though id still like to know how to get around this. The first formula would be placed in column B and then pulled across.

Thanks

 

[jasonb75]

There are a couple of ways of doing it, personally I would use R1C1 references.

Assuming that the first formula is going in B10, then

=INDIRECT("'" & $A10 & "'!R17C",0)*Inputs!$E24 would give absolute row, relative column (R17 = row 17, C = same column as formula).

=INDIRECT("'" & $A10 & "'!R[7]C",0)*Inputs!$E24 would give both row and column as relative (R[7] offsets 7 rows below the formula, i.e. 10 + [7] = row 17).

 

[CROY1985]

First one works a treat, and it turns out i did need it in the end too!

Thank you very much.