Changing a formula via text entry box

theboyfold

New Member
Joined
Sep 15, 2006
Messages
6
I have a reference in a cell which the user of the sheet will need to change depending on which cell they want to cross reference, the fomula is:
=VLOOKUP(Month!I$47,'Name Sheet'!$A$2:$B$47,2,FALSE)

The reference that needs to changed is the column in the 'Month' sheet so 1=C 2=D 3=E and so on until 31=AG

So I'd like a text box which asks for a number and then changes the column reference for cells B10:B26

is this possible?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

theboyfold

New Member
Joined
Sep 15, 2006
Messages
6
ok, I've not got a way of creating a cell with the correct column value. 1=C 2=D 3=E and so on until 31=AG etc etc

Is there anyway that I can get the column reference in =VLOOKUP(Month!I$47,'Name Sheet'!$A$2:$B$47,2,FALSE) to reference the cell with the correct column value?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
I don't understand your question, mainly because in your VLOOKUP there are only 2 columns in the table you are looking up - A and B on sheet Name Sheet.
 

theboyfold

New Member
Joined
Sep 15, 2006
Messages
6
Ok, I'll try again. I have the formula
=VLOOKUP(Month!I$47,'Name Sheet'!$A$2:$B$47,2,FALSE)
This references one sheet for a set of initials and then cross references this text to enter the full name into the cell.

But I want the value of the column after Month!, (in this example it's an I) to change depending on what the value of another cell is.

For example if the user enters in the value E into cell B29 I want the formula to read =VLOOKUP(Month!E$47,'Name Sheet'!$A$2:$B$47,2,FALSE)

Is this possilble?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Well you could use INDIRECT.

=VLOOKUP(INDIRECT("Month!" & B29 &"$47"),'Name Sheet'!$A$2:$B$47,2,FALSE)

Though there's maybe a more efficient way of doing what you want, perhaps one of the formula gurus will pop by with one.
 

Forum statistics

Threads
1,136,519
Messages
5,676,333
Members
419,619
Latest member
jalme

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