4

I would like to have a a cell that has the value 1,2,3 ... or 10 but it will show some text - but when I use the cell value in formulas it should use the numbers!

For example:

  • Cell A1 displays a word Apple but has a value of 1.
  • Cell B1 displays a word Orange but has a value of 2.
  • Cell C1 will have a formula =A1+B1, but would display a value of 3.
  • 1
    Why do you want to do this? – David Mar 21 '13 at 21:32
  • I want to add a $ value to items but diisplay their Name instead of actual $ value. Later I can make lists such as "Customer basket contains Aplle, Orange" total value = $3. – user209378 Mar 21 '13 at 21:44
7

You can do this with custom format:

Select cell A1, go to Format Cells->Number->Category->Customer and enter "Apple"in the Type field.

Repeat the process for all other cells.

However, note that this "information hiding" is not best practice, as this makes your file hard to audit/debug later. Better use a hidden column to store the values - and type the names directly!

  • @ Peter Albert - how exactly do I use a hidden column to store the values - and type the names directly? – user209378 Mar 21 '13 at 21:49
  • Store the numbers 1 to 10 in A1 to A10. Select column A, right click the column header and select "Hide". Type "Apple", ... in B1 to B10. Use A1 to A10 in any formula you need to refer to the "underlying" value. – Peter Albert Mar 21 '13 at 22:25
3

This is a valuable thing to be able to do, because it can make your spreadsheet more readable.

  1. Define Apple and Orange to be names whose values are 1 and 2. To do this, the best way is to put the words Apple and Orange in cells A1 and A2.
  2. Then in cells B1 and B2, put the values 1 and 2.
  3. Now use Insert->Name->Create to tell Excel you want these four cells to be a dictionary. (They don't use that word.)
  4. Now you can use "Apple" and "Orange" anywhere in a formula and they will be replaced during calculation with the numbers 1 and 2.
  5. Type the words Apple and Orange into, say, cells D1 and D2. Then in, say, cell F1, enter the formula: =INDIRECT(D1)+INDIRECT(D2). The result displayed in F1 will be 3.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.