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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

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,351
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,351
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,170,946
Messages
5,872,885
Members
432,952
Latest member
CincyExcel1

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