Question about Combo Box (Form Control)

ScottGem · Feb 17, 2011, 02:39 PM

Do you have Office Pro? This is a lot easier in Access.

jakester · Feb 17, 2011, 03:28 PM

Quote:

Originally Posted by ScottGem

Do you have Office Pro? This is a lot easier in Access.

Scott - unfortunately, no.

I've been thinking more about this and I believe my problem could be solved by a UDF maybe? I'm not sure.

Here's what I have been thinking. When I select a month from the drop down menu, the Cell Link will put a value (1-12) into cell J2 on my Comparison spreadsheet. Let's say I choose March; it will return a 3 in cell J2. I built a small table with values from 1-12 and a corresponding Column Number and Letter.

Value Column # Column Letter
1 2 B
2 3 C
3 4 D
4 5 E

And so on.

I could use a vlookup to check the value in J2 against the above table and return either the column # or the column letter in K2.

I'd enter a formula in cell A4 that would look like:

=Actual!&K2&5

K2 is the Column Number (or Letter) and 5 is the cell number. I just don't know which is more elegant of an approach.

Does this make sense?

That's really all I need to do.

JBeaucaire · Feb 17, 2011, 03:39 PM

I think you want the INDIRECT() function. It lets you piece together a reference that way:

=INDIRECT("Actual!" & K2 & "5")

However, INDIRECT() is volatile, which is fine in small doses, which may be all you need here.

But if you want to post a desensitized copy of your wb and point out the cells you're trying make dynamic, I'll see if there's a better approach.

Question about Combo Box (Form Control)

Add your answer here.

Check out some similar questions!