# Changing a formula via text entry box

#### theboyfold

##### New Member
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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?

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.

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?

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.

That works a treat!! Thanks for your help

Replies
3
Views
548
Replies
6
Views
269
Replies
1
Views
211
Replies
6
Views
368
Replies
1
Views
231

1,211,457
Messages
6,101,964
Members
447,766
Latest member
cool_aikon

### 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?

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