Excel - Number hidden beneath a cell's displayed Text...Possible?

[jman995x]

Hello,

Is there a way to have a cell display text, but behind the text is actually a number figure that I can ask Excel to use in calculations?

I'll explain what I mean to give you a visual so you'll understand what I'm trying to do.
I want to create a monthly budget in an Excel spreadsheet.
I'll have one row for each day of the month, and maybe 3 or 4 columns for in which to put expenditures for each day.

Let's say on the 4th of the month I have a Visa bill due, I usually put gas in my car and I pick up some Dry Cleaning. I'd like to be able to type Visa, Gas and Dry Cleaning within the row of cells for the 4th, and have the dollar amounts for those expenditures hidden beneath the text. Then I'd do a summation formula in the last column to tabulate all of the hidden dollar amounts to give me a total for that day. This way, I can see a total expenditure figures for that day (in the summation column) and what the money went to, without having to do the spreadsheet the old-fashioned way of putting the Text in one cell and the dollar figures in an adjoining cell (which in this case wouldn't work in a columnar sense since the text cell would have to be either above the dollar cell, thus making the text cell in the previous day's row...or, putting the text in say, cell B2, and the dollars in row B3, and doing the same for multiple charges per day, which would make for a very wide spreadsheet.

Any suggestions?

Thanks,

J.

 

[sushi]

I'm not sure I follow your concept.

FWIW, it is possible to hide columns and/or rows. Would that help?

You could also look at creating a Macro for data entry. Text entry on the 1st worksheet, then on the 2nd worksheet, corresponding values.

 

[jman995x]

I'm not sure I follow your concept.

FWIW, it is possible to hide columns and/or rows. Would that help?

You could also look at creating a Macro for data entry. Text entry on the 1st worksheet, then on the 2nd worksheet, corresponding values.

Maybe that's what I need to do.

Here are some screenshots of what I'm trying to do:

 

[Gregg2]

I'm pretty confident that the answer is no. You can't have two pieces of information entered in the same cell, with one being visible and the other hidden, much less have the hidden bit used in a formula elsewhere. Option 2 is the way to go, whether you like it or not. Sorry.

 

[GGJstudios]

You could set up a table and use VLOOKUP to find corresponding values within the table. You can also assign names (VISA) to values (125) and then use the names in formulas. Use your Excel Help function and check out the VLOOKUP formula and research using Names.

 

[mkrishnan]

You could set up a table and use VLOOKUP to find corresponding values within the table. You can also assign names (VISA) to values (125) and then use the names in formulas. Use your Excel Help function and check out the VLOOKUP formula and research using Names.

This is probably the best way to go... a macro or VBA is another possibility, depending on what version of Excel you're using.

 

[Gregg2]

You could set up a table and use VLOOKUP to find corresponding values within the table. You can also assign names (VISA) to values (125) and then use the names in formulas. Use your Excel Help function and check out the VLOOKUP formula and research using Names.

Ok, this is different than what the OP asked, which I still don't think you can do. But, perhaps it gets the desired result, and that's cool. It seems to be very close to the "hidden value" the OP is looking for.

My question, and one I'm sure the OP will have, is using Names, will one only be able to assign one value per word? In other words, if there are multiple VISA bills on the page, would one have to be VISA1 and another VISA2, etc. (for example) to assign different values to them? I assume that would indeed be the case.

 

[GGJstudios]

Ok, this is different than what the OP asked, which I still don't think you can do. But, perhaps it gets the desired result, and that's cool. It seems to be very close to the "hidden value" the OP is looking for.

My question, and one I'm sure the OP will have, is using Names, will one only be able to assign one value per word? In other words, if there are multiple VISA bills on the page, would one have to be VISA1 and another VISA2, etc. (for example) to assign different values to them? I assume that would indeed be the case.

Each name is like a shortcut that points to a cell or range of cells.