Using Dropdown Box Outputs

[LastTrain]

So, I'm attempting to combine the results of 2 dropdown box selections (and an additional number) to create a new formula which will retrieve information from other tabs of the workbook.

In the first dropdown box, I choose the first option which gives an output of 1. I then have a lookup formula to convert this to a tab name plus the exclamation point (in this example the result of the lookup formula is "SC!". I do this in cell B4.

In the second dropdown box, I choose the first option which of course also gives an output of 1. I have another lookup formula to convert this to a column in the tab, in this case the result of the lookup formula is "E." I do this in cell F4.

I then want to add a standard row number, in this case 4.

In a random cell, I've then used concatenate to combine those three items into SC!E4.

My question is, how do I then convince Excel to read that as an actual formula? In this random cell, if I simply typed "=SC!E4", I'd get the results of cell E4 on tab SC. However, my current formula of =concatenate(B4,F4,4) simply gives the text output of SC!E4 (the cell is formatted as General).

I get this and am not suggesting Excel is messing it up, I'd just like to know how to get the result I want. Is there a way to now get Excel to act on this result as if it was a formula?

Thanks in advance!

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try
=INDIRECT(B4&F4&4)

M.

 

[LastTrain]

Works beautifully. Thanks for the welcome and the helpful reply.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.