Csdrhrt

So I'm trying to make myself a food planner in Excel and I have everything set up great. I have a list of food items and for simplicity, I have the name of the food defined by the calories.

I am then trying to have it so I can just reference that item within a weekly planner - e.g. I can put 'Banana' for Saturday breakfast and it displays as Banana, and has the hidden or assigned value of 92 calories. These hidden values are then calculated by a simple SUM.

I was wondering if there was a way I could have these values display as their name while retaining their value?

The length of this answer alone will make this seem horrendous, but I'm just wordy... I beleieve a little (alright, a lot) of details help a lot and telling you why you are doing something makes it more accessible to you and more usable in other applications as well. This is EASY. I did it in five minutes at work with the boss talking to me. It's EASY. So bear with it?

You can do this without inordinate trouble, or even trouble of any kind if:

1) You have access to the CONCAT() function.

2) You accept that the food name/calorie information must be entered somehow, either in a lookup table like most folks would do this, or as Named Ranges and their values. Neither bit of typing is more obnoxious than the other so why not?
3) Your range of cells for entering the foods in not huge. By that I mean no more than perhaps 800 items. Even that could actually be extended but it's kind of "layering things" and that's sometimes obnoxious in Excel. I picture a limited, "this day" kind of entry need, or a week's worth, from my feel for your description, but... it is a difficulty if this is meant to run forever, or for a bunch of people for a week or what-have-you. Doable though, don't get me wrong, but I'll continue using the assumtion there's maybe 25 or 200 cells in the range you anticipate.

So... each food needs entered as a Named Range using no spaces or unnatural characters in the names. For example:

PorkChop
GreenBeans
MacaroniAndCheese

but NOT "Pork_Chop" and so on. Just drop the spaces is the idea.

For the "Refers to" area, instead of the usual formula or cell reference (like "=Sheet1!A5"), just put the calorie value: =180 for the pork chop perhaps.

Now, to build the formula you need:

You need to use INDIRECT() to turn the food name into a usable reference for Excel. For instance:

=INDIRECT(PorkChop)

would turn "PorkChop" into PorkChop (the Named Range), look it up in your NR's, and return the calorie value you entered.

Unfortunately, though I swear it HAS done so correctly for me, just not now, using INDIRECT() like so:

=INDIRECT(K10:K13)

yields the single value using the "upper left corner" of that range, K10. (I will use K10:K13 for your entry area in the example here.) Perhaps I achieved it with direct cell references in the target cells (as in K10:K13 had values, not NR's) and that was the difference. Anyway, CONCAT() makes that not matter so much.

So you have to enter each INDIRECT separately instead of the sweet and easy range like above. That would be a LOT of typing for a 500 cell entry range! But CONCAT() will solve that.

Alright. How to deal with your users typing spaces into the food names THEY enter? Problem #2, eh? Wrap each NR in SUBSTITUTE() befre passing it to INDIRECT():

=INDIRECT(SUBSTITUTE(K10," ","")

and that'd be an UNHOLY amount of work without CONCAT().

So, to build your summing formula we will craft a text string for each cell in the entry range and slip in a ROW() value to make them unique. Let's figure the range K10:K13 here, though you could have many more rows and use several columns by using the techniques that follow.

First, tha basic string. Pick a cell in row 10 (matching the row of the first cell, K10 to make it easier on you) that is out of the way. My example is set up with G10, but you'd really want something to the right of column K. Just sayin'. Enter the following in cell G10:

="INDIRECT(SUBSTITUTE(K"&ROW()&","&""" "","""")),"

(Notice how you have to use two " characters in it for each of them needed in a "real" SUBSTITUTE() function... this is a text string that will be passed into being a real boy, I mean formula, at which point Excel will strip off one of each pair of them... this is an aspect of what I refer to as "layering" and how it gets... funny sometimes.)

One entered, copy the cell and paste it into G11, G12, and G13. Or two hundred cells if your entry area is 200 cells deep. But these four for now.

Then go to the LAST one, G13, and edit it to remove that very last comma so it ends:

))" instead of )),"

because that will be the end of your string in the CONCAT() function and you can't have it end with a comma!

Good deal. Now go to some other cell, perhaps G15 and enter:

=CONCAT(G10:G13)

and after pressing Enter, copy the cell to the clipboard then go to yet another cell, perhaps G16, and Paste|Special|Values to finally create the text string you need for your SUM() function.

Press F2 then to edit the cell contents and copy the entire contents to the clipboard. Doing it from INSIDE the cell gets the literal text and nothing else. Copying the cell itself would be useless!

Go to the cell you want your calorie sum in and type "=SUM(", then paste your text string, then press Enter and let Excel add your closing ")" for you. It would look like this:

=SUM(INDIRECT(SUBSTITUTE(K10," ",""),INDIRECT(SUBSTITUTE(K11," ",""),INDIRECT(SUBSTITUTE(K12," ",""),INDIRECT(SUBSTITUTE(K13," ",""))))))

Done.

All the spaces the users enter in the food names are removed so they match the NR's. They see their food name entries in the cells, no matter what. Yet your summing cell gets the numbers it needs to do an actual summation.

Now, users will still break it, but those breakings (oh, not spelling something right, typos, a new food name, wrong names, you get the idea) would ALL break the lookup table approach too. They will all make the summing cell show an error so someone (you...) would know to look for the error/s.

By the way, if you are using several columns, not the one I used, just do the string for the first column, then edit the first cell to set up the next column, and so on. Each time, paste the extra text string material at the end of the previous pasting in your summing formula.

And if you reach the formula character lenth limit for Excel (Remember I was saying the entry range was limited to maybe 800 cells?) you can make a NR called perhaps "Sum1" and put the first batch into it, then another ("Sum2") for the second, and so on, until you account for your full entry range, and for you summing cell you'd use:

=Sum1 + Sum2 + Sum3

or what-have-you. The "layering" thing though... Excel's formula character length in a Named Range might be less than for in an actual cell, so it could get complicated. But that's the principle, and you'd just have to experiment.

But again, with simple, quick work, you have only the food names ever in the data entry range and all the calorie values out of sight. It would be magic to them.