Referencing a cell using a specified letter

paul41

New Member
Joined
Feb 12, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,447
Office Version
  1. 365
Platform
  1. Windows
Try this formula:
Excel Formula:
=INDIRECT("Sheet1!" & Sheet2!A1 & "2")
 
Solution

paul41

New Member
Joined
Feb 12, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you for the quick reply - that works perfectly, thanks again!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,447
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help.
 

paul41

New Member
Joined
Feb 12, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,447
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
& "2"
in your formula with:
Excel Formula:
& 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

New Member
Joined
Feb 12, 2022
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,447
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Forum statistics

Threads
1,175,662
Messages
5,898,753
Members
434,729
Latest member
Tej92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top