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!
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!