Value or formula in same cell?

December 8, 2013 at 15:31:07
Specs: Windows XP
Lookup value or user value in same cell
I have created a nested if statement that either references a lookup table or requests users to input data ( into cell E25 ). The the nested if statement decision is triggered by the selection from a drop down menu above ( E9 - lookup table or user entered data ). The lookup table function works just fine to retrieve a value into E25. Also, the use enter data works well to display the words "enter data here" in cell E25. PROBLEM - as soon as the user enters data in E25, the formula is over-written. How can I make this work? Whether its a look up value or user value, I want them to both be listed in the same cell.

If I can't have either or for the cell mentioned above, could this be an alternative? Can the lookup reference send the value to a cell other than the cell where the formula is listed ( ie - to M25 rather than E25 )?

See More: Value or formula in same cell?

December 8, 2013 at 16:23:07
As you have come to realize, you can't enter data in a cell and have the previous contents (e.g. the formula) remain. A cell can only contain one thing at a time.

As for your second question, the answer is No. A formula can't "send" data anywhere. Data can only be "pulled" from one cell to another by using a simple equality formula, e.g. in M25 you enter =E25. Of course, this won't work in your situation because you still have your original issue of wanting to put a formula and a value in the same cell.

If you really want to use the same cell for either the value from a look-up table or from user input, you'll need to use a macro. A Worksheet_Change macro could be triggered by the drop-down and either perform the lookup or ask the user for input.

We can't offer any code just yet because we don't know the specifics of your operation, such as what does the Nested If do, where is the lookup table, etc.

Is it possible that you could accomplish your goal with 2 cells, one for the lookup and one for user input if needed?


Report •

December 9, 2013 at 11:12:16
Value or Formula in Same cell
Updated the screencast video to show nested if cell formulas and their links to lookup table and calculations tab. The spreadsheet has been significantly simplified ( there could be 20 different scenarios with each having 200+ lines of lookup/entered data plus 350+ lines of calculations on each of 3 different tabs ). No, I do not want 2 different columns to represent lookup data or user entered data. I want the user to be able to see all scenarios at the same time so they can make minor changes to each scenario to see the reletive differences. This will utlimately be used to optimize a process. I am interested in the Worksheet_Change Macro mentioned by DerbyDad03. Also, if there are other ideas, please let me know.

Report •
Related Solutions

Ask Question