Referencing a cell using a specified letter

[paul41]

Hi

I'm looking for advice on the following.

Each week i wish to 'manually' change a letter within a field. Say this is set to 'B' at the moment and is held in cell A1 on sheet2.

For various other cells i have a formula which references another sheet and i wish to use that letter as part of the new formula.

So say i have the formula within sheet2 referencing a field B2 field in sheet1 stating: =sheet1B2, this correctly returns me the value held.

What I wish to do is to save me updating many formula's, for this to 'reference' the value B from cell A1 on sheet2. So later if i was to manually change the value of cell A1 on sheet to to 'F' then my formula would pick up the value from sheet1F2 instead. If i change A1 to 'D', then it will reference sheet1D2 instead.

Hope this makes sense and thanks in advance, i think the answer lies within using the index or references but i could be wrong.

Thanks

P

 

[Joe4]

Try this formula:

=INDIRECT("Sheet1!" & Sheet2!A1 & "2")

 

[paul41]

Thank you for the quick reply - that works perfectly, thanks again!

 

[Joe4]

You are welcome.
Glad I was able to help.

 

[paul41]

Sorry just a further question, keeping with the same formula which works perfectly. Say i want to drag down this formula which has "2" hard coded but make this increase by 1 so dragging down would change:

=INDIRECT("Sheet1!" & Sheet2!$A$1 & "2")
=INDIRECT("Sheet1!" & Sheet2!$A$1 & "3")
=INDIRECT("Sheet1!" & Sheet2!$A$1 & "4")

Can this be done? At the moment the 2 remains static...

Thanks again

P

 

[Joe4]

You can use the ROW() function, which returns the current row number into the formula.

So, let's say that your first formula was going in row 1, and you want to return "2" for ro1 1, "3" for row 2, "4" for row 3, etc.
Then you would just replace the:

& "2"

in your formula with:

& ROW() + 1

Note: do you realize that you still have the row number in your formula?
Sheet2!$A$1 & "2"

So that would NOT be returning cell A2, but rather cell A12.
Is that what you want?

If so, if you are copying this down more than nine times, you are going to have issues, as it is going to go from A19 to A110 (not A20).

 

[paul41]

Thank you that has fixed my issue and its all working perfectly, obviously my real spreadsheet is slightly different from my example but this gave me the answer i was looking for. Thanks i did realise that but got it all working as i intended and fully tested it. Thank you again for your help on this very much appreciated.

 

[Joe4]

You are welcome.