Am I Correct in Using a Named Range to Insert Multiple Cells?
I have a text label in cell A1 followed by numbers in B1, B2, B3 and B4. I have named this range of cells (RangeName). I want to be able to simply type the label name and have the label and its associated numbers inserted into cells in another sheet of the same workbook.
I thought this could be done by naming the range. However, if I enter +RangeName or =RangeName in a cell in another sheet, I get a value error (#VALUE!). If I enter either phrase on the sheet containing the range, I get a number that doesn't seem to relate to the numbers in the range.
Am I incorrect in trying to use a range name to accomplish what I want, which is to type the label name and have it and the numbers inserted into separate cells? If I cannot accomplish this with a named range, is there another way to do this?
I thought this could be done by naming the range. However, if I enter +RangeName or =RangeName in a cell in another sheet, I get a value error (#VALUE!). If I enter either phrase on the sheet containing the range, I get a number that doesn't seem to relate to the numbers in the range.
Am I incorrect in trying to use a range name to accomplish what I want, which is to type the label name and have it and the numbers inserted into separate cells? If I cannot accomplish this with a named range, is there another way to do this?
ASKER
Gowflow:
I should have explained further but was a bit short on time then.
I am trying to create a food log to track my intake of protein fats and carbs. In my file, I have a sheet that is my list of foods. Each row has a text cell that contains the food name, and the following cells contain total calories, number of grams of protein, carbs, fats, and the portion size. All of this information is contained in the named range that food. I need to be able to simply enter the food name in another sheet in the workbook and have the name entered followed by the nutrient data in separate cells.
Dave
I should have explained further but was a bit short on time then.
I am trying to create a food log to track my intake of protein fats and carbs. In my file, I have a sheet that is my list of foods. Each row has a text cell that contains the food name, and the following cells contain total calories, number of grams of protein, carbs, fats, and the portion size. All of this information is contained in the named range that food. I need to be able to simply enter the food name in another sheet in the workbook and have the name entered followed by the nutrient data in separate cells.
Dave
ok fine could you pls post your sample workbook so I can see how to fit this for you ? for sure you tell me like where you would like to have this set as example.
gowflow
gowflow
ASKER
Gowflow:
Thanks for taking a look. Attached is the Excel 2010 file. The first sheet is the food list. If you look at the Name Manager, you'll see I have named a few ranges as samples. I want to be able to go to another other sheet and enter, for example, "kale_steamed" and have that label entered into the current cell and for the protein, carbs, fats and total calories numbers to populate the adjacent cells (don't care about the sodium). I've just been cutting and pasting the information from the food list, but there has got to be a better way!
Dave
Thanks for taking a look. Attached is the Excel 2010 file. The first sheet is the food list. If you look at the Name Manager, you'll see I have named a few ranges as samples. I want to be able to go to another other sheet and enter, for example, "kale_steamed" and have that label entered into the current cell and for the protein, carbs, fats and total calories numbers to populate the adjacent cells (don't care about the sodium). I've just been cutting and pasting the information from the food list, but there has got to be a better way!
Dave
no file attached !
gowflow
gowflow
ASKER
Once more, with feeling. Sorry about that!
Food-Log-December-2014.xlsx
Food-Log-December-2014.xlsx
ok I see a file with lots of similar sheets and a table.
Could you please tell me what you want ? and how would you been doing it repetitively to see how we can build you something ?
gowflow
Could you please tell me what you want ? and how would you been doing it repetitively to see how we can build you something ?
gowflow
ASKER
As I mentioned, Sheet 1, labeled "Food List" is the master table of food items and their associated values. The following sheets are daily meal logs. Currently, when I eat, for example, "kale steamed," I have to go to the Food List Copy that line entry and paste it into another sheet (the actual food amount eaten may vary from what is listed, but let's not concern ourselves about that at this point). I have named the range containing the kale steamed information as "kale_steamed," hoping to be able to go to another sheet, enter the range name, and have the name and nutrient values entered into the sheet.
In essence, that is what I want to do, enter a name from the Food List sheet in another sheet and have the food name and its associated values entered into separate cells, like they are shown on the Food List.
In essence, that is what I want to do, enter a name from the Food List sheet in another sheet and have the food name and its associated values entered into separate cells, like they are shown on the Food List.
ok got your point
Let me comment.
I checked your named ranges that you created but they were referring to the wrong items like Banana Large was pointing to Banana Small etc.. some were totally off like pointing to an other item altogether.
We can do this a different way. Would you accept a VBA solution ?
If yes then I need to know if always your description is identical to what is in the Food list ie field Item and if always in your sheets it will go in Column B
Also need to know in table you have
Protein Carbs Fat Calories Serving Size and Sodium
whereas in the sheets you only have
Protein Carbs Fat Fluid Intake Calories
so we only list the item that exist in Table ? and the rest we skip ?
BTW I have fixed your named ranges but the solution I have in mind will not necessitate named ranges but will wait to see your answer.
gowflow
Let me comment.
I checked your named ranges that you created but they were referring to the wrong items like Banana Large was pointing to Banana Small etc.. some were totally off like pointing to an other item altogether.
We can do this a different way. Would you accept a VBA solution ?
If yes then I need to know if always your description is identical to what is in the Food list ie field Item and if always in your sheets it will go in Column B
Also need to know in table you have
Protein Carbs Fat Calories Serving Size and Sodium
whereas in the sheets you only have
Protein Carbs Fat Fluid Intake Calories
so we only list the item that exist in Table ? and the rest we skip ?
BTW I have fixed your named ranges but the solution I have in mind will not necessitate named ranges but will wait to see your answer.
gowflow
ASKER
Gowflow:
Using VBA is an excellent idea! I had a feeling the named ranges were not the way to go.
To answer your questions:
- yes, the description can be identical to the way it is shown in the Food List, and it will always go into Column B.
- the sodium was recorded just in case I need it in the future; that information does not have to carry over to the daily sheets.
- The fluid intake column is actually not used and could be eliminated, so the values could transfer straight across as they are shown on the Food List.
If the data from the Food List can be transferred in the order shown on the food list, that would be fine.
Thank you! I really appreciate your help with this. Tracking your food is really time consuming. I need to streamline the process so it doesn't take up so much time.
Dave
Using VBA is an excellent idea! I had a feeling the named ranges were not the way to go.
To answer your questions:
- yes, the description can be identical to the way it is shown in the Food List, and it will always go into Column B.
- the sodium was recorded just in case I need it in the future; that information does not have to carry over to the daily sheets.
- The fluid intake column is actually not used and could be eliminated, so the values could transfer straight across as they are shown on the Food List.
If the data from the Food List can be transferred in the order shown on the food list, that would be fine.
Thank you! I really appreciate your help with this. Tracking your food is really time consuming. I need to streamline the process so it doesn't take up so much time.
Dave
ok I read you and have no problem with all that.
I suggestion, What do you thing if we are to replace all the 'Fluid Intake' filed by 'Serving Size' ? Like I show you in the column Fluid Field (we rename it of course to show Serving Size) the data corresponding to Serving Size ? Would it be handy to you ?
gowflow
I suggestion, What do you thing if we are to replace all the 'Fluid Intake' filed by 'Serving Size' ? Like I show you in the column Fluid Field (we rename it of course to show Serving Size) the data corresponding to Serving Size ? Would it be handy to you ?
gowflow
I am working on something inline of my latest comments. Will revert.
gowflow
gowflow
ASKER
To show the listed serving size? Yes! Once we get the data to populate, my next step will be to compare the actual serving size against the listed and make adjustments to the numbers, but first things first. Thanks. Great idea! The column could even be called "Listed Serving Size" or something like that.
ok thought so before you even asked that somehow dwon the line you would want to see the actual servings impact on fat/carb/etc...
for sure as you said first things first. Happens so that I also followed diets but nothing compared to your crazy scrupulous detailed exact figures ...
Will revert
gowflow
for sure as you said first things first. Happens so that I also followed diets but nothing compared to your crazy scrupulous detailed exact figures ...
Will revert
gowflow
ASKER
I've never been this exact about eating or recording diet before either, but it is working like magic! Getting this sheet to work as I envision it will help make the routine less painful.
I have to admit I like the concept but given the multitude of worksheets, It look more challenging than I first though to be. I like challenges ... reason why it is taking time. I devoted almost good part of today on this. But will get there and hopefully to your taste.
Meantime ... to prepare for next step I invite you to review your table to split the serving size into 2 columns one would be the unit of measure ie oz, g, tbs, cup etc.. and the second would be value typically a number or decimal, also will have to work on a first phase to split your data for Col A to extract only the number part and once it is done we can be ready for next ultimate phase which would be the calculation conversion part.
So you have some work while I am developing ... :)
Cheers
gowflow
Meantime ... to prepare for next step I invite you to review your table to split the serving size into 2 columns one would be the unit of measure ie oz, g, tbs, cup etc.. and the second would be value typically a number or decimal, also will have to work on a first phase to split your data for Col A to extract only the number part and once it is done we can be ready for next ultimate phase which would be the calculation conversion part.
So you have some work while I am developing ... :)
Cheers
gowflow
ASKER
That makes sense. So I should go ahead and modify my copy while you are working? I can do that.
yes presume you will modify only the Table and then I can take your table sheet and adapt it here. Do not change your zillion worksheets will have to do this via macro.
gowflow
gowflow
ASKER
Ah, got it, thanks. I am doing actual work now, but will get to that this evening.
Dave
Dave
ok no problem anyway I in the research phase as not too obvious as I said given repetitive sheets and useless to start putting code and creating stuff in each and every sheet it would be too cumbersome and primitive job.
gowflow
gowflow
ASKER
Here is the revised sheet. I separated the serving size numbers from their unit type, sorted the list and cleaned up the sheet. I also removed the used daily sheets and have created a new base sheet. Have at it!
Just so you don't go crazy: if you plug values from the food list into the daily sheet, the numbers will not calculate to an exact 100%. The calories given on packaging usually do not match the number you get by multiplying protein and carb numbers by 4, fats by 9 and adding them up.
Food-Log-Base-File.xlsx
Just so you don't go crazy: if you plug values from the food list into the daily sheet, the numbers will not calculate to an exact 100%. The calories given on packaging usually do not match the number you get by multiplying protein and carb numbers by 4, fats by 9 and adding them up.
Food-Log-Base-File.xlsx
ok fine.
Let me understand the mechanism here. I will use this as an example:
almonds, sliced, unsalted, dry toasted TJ 7 6 15 180 30 g.
the portion that has the values is 30g. let's say you eat 75g. as you are really out of control ... or whatever so you would input in your daily sheet now in the old format
75 g in Col A ??? or what how you do it ? and also what would be the values in the Fat/Calories etc.. columns on the current sheets layout.
and in the new format also how you expect this to be.
pls give the example for both so I understand the mechanism.
My understanding is that you pick the item: almonds, sliced, unsalted, dry toasted TJ
and you input in Col A how much you ate and you want the values from the table pulled automatically for this item in their corresponding columns. Let not talk about conversion at this stage as it is premature and is subject of an other phase.
pls advise
gowflow
Let me understand the mechanism here. I will use this as an example:
almonds, sliced, unsalted, dry toasted TJ 7 6 15 180 30 g.
the portion that has the values is 30g. let's say you eat 75g. as you are really out of control ... or whatever so you would input in your daily sheet now in the old format
75 g in Col A ??? or what how you do it ? and also what would be the values in the Fat/Calories etc.. columns on the current sheets layout.
and in the new format also how you expect this to be.
pls give the example for both so I understand the mechanism.
My understanding is that you pick the item: almonds, sliced, unsalted, dry toasted TJ
and you input in Col A how much you ate and you want the values from the table pulled automatically for this item in their corresponding columns. Let not talk about conversion at this stage as it is premature and is subject of an other phase.
pls advise
gowflow
Also an other one I just stumbled on,
Catfish Filet
does not exist in your list !!!
How do we do then ?
gowflow
Catfish Filet
does not exist in your list !!!
How do we do then ?
gowflow
ASKER
The actual serving size on the Daily Base Sheet now has two columns, A and B, separating the number from the unit of measure. I figured that was needed for future calculations.
The fat, protein and carb numbers are all in grams. The columns following A and B for the actual serving size are now in the same order as they are in on the Food List, so the data can be brought straight across.
Catfish filet! I forgot I had that treat. Once the file is working and I start using it, more foods will be added as I get bored but understand how better to match foods to meet my target numbers.
The fat, protein and carb numbers are all in grams. The columns following A and B for the actual serving size are now in the same order as they are in on the Food List, so the data can be brought straight across.
Catfish filet! I forgot I had that treat. Once the file is working and I start using it, more foods will be added as I get bored but understand how better to match foods to meet my target numbers.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Super-fantastic! I will take a look at this as soon as I can. Thanks!
ok no problem let me know your feedback.
gowflow
gowflow
ASKER
Gowflow:
This really is fantastic! It is working quite beautifully. You're a wiz!
In my testing, the already filled in sheets are working fine. However, the base data sheet isn't producing the combobox. Looking at the editor, I am not seeing any code attached to that sheet. Was that planned?
I am not worried about cleaning up the existing data on the daily sheets; the December record is what it is. However, I am realizing that to make the logging process as quick as possible for me, it is best to have the base file ready with blank sheets for all 31 days. I will do that before we continue to the next step. Unfortunately, I need to get to bed soon so I can make it to the gym (the reason for all this food logging!). I will do that this weekend and then post the cleaned up file.
Thanks! This is a very exciting improvement!
Dave
This really is fantastic! It is working quite beautifully. You're a wiz!
In my testing, the already filled in sheets are working fine. However, the base data sheet isn't producing the combobox. Looking at the editor, I am not seeing any code attached to that sheet. Was that planned?
I am not worried about cleaning up the existing data on the daily sheets; the December record is what it is. However, I am realizing that to make the logging process as quick as possible for me, it is best to have the base file ready with blank sheets for all 31 days. I will do that before we continue to the next step. Unfortunately, I need to get to bed soon so I can make it to the gym (the reason for all this food logging!). I will do that this weekend and then post the cleaned up file.
Thanks! This is a very exciting improvement!
Dave
HOLD ON !!!!
Your jumping your horses too quickly. I am good, maybe but I am not a superstar !!!! 1 step at a time.
Your supposed to try the whole thing on the existing sheets and eventually clean them (that is separately) for sure this has not been implemented in the Base sheet as I have plans for you.
I will surprise you in telling you we will not have to create for 31 days or so all the sheets .. I will leave this for later. But if you have remarks or comments on what exist so far pls go ahead so we can fine tune it as it is the base here. If not then here is my suggestion for the next steps I will split the proposed ideas in questions that you will ask and will act upon if you want.
1) Next step or next question would be to get the Data in Col A for the existing sheets fixed. More precisely split between A and B.
2) After is to get you a button to generate sheets with the new format (based on the Base Sheet) and allow you to name them all thru a macro and automated.
3) Implement the conversion and calculations issues.
If you have other ideas they are welcome and if you have a different proposal pls go ahead.
gowflow
Your jumping your horses too quickly. I am good, maybe but I am not a superstar !!!! 1 step at a time.
Your supposed to try the whole thing on the existing sheets and eventually clean them (that is separately) for sure this has not been implemented in the Base sheet as I have plans for you.
I will surprise you in telling you we will not have to create for 31 days or so all the sheets .. I will leave this for later. But if you have remarks or comments on what exist so far pls go ahead so we can fine tune it as it is the base here. If not then here is my suggestion for the next steps I will split the proposed ideas in questions that you will ask and will act upon if you want.
1) Next step or next question would be to get the Data in Col A for the existing sheets fixed. More precisely split between A and B.
2) After is to get you a button to generate sheets with the new format (based on the Base Sheet) and allow you to name them all thru a macro and automated.
3) Implement the conversion and calculations issues.
If you have other ideas they are welcome and if you have a different proposal pls go ahead.
gowflow
ASKER
Goflow:
I didn't know that was what you had in mind. That is truly amazing! That is why I asked. I thought you might have plans for the base sheet. To answer your questions:
1. I can certainly do that this weekend.
2. You are blowing my mind with this! That would be completely awesome!
3. Yes. That would give me exactly what I need!
Thank you!
Dave
I didn't know that was what you had in mind. That is truly amazing! That is why I asked. I thought you might have plans for the base sheet. To answer your questions:
1. I can certainly do that this weekend.
2. You are blowing my mind with this! That would be completely awesome!
3. Yes. That would give me exactly what I need!
Thank you!
Dave
ok great !! very pleased to have you pleased ! as this is why I develop is to hv working solutions ! :)
take your time and let me know what else you need.
gowflow
take your time and let me know what else you need.
gowflow
Reading your 1) 2) 3) ...
Seems you did not get the full picture !
in 1) I did not mean you to work on this but rather you to ask a new question and I could just do that for you via code.
for sure all after we close this one.
gowflow
Seems you did not get the full picture !
in 1) I did not mean you to work on this but rather you to ask a new question and I could just do that for you via code.
for sure all after we close this one.
gowflow
ASKER
I get it now. I guess I'm just feeling like I should be doing SOMETHING; you're doing all the work! I'll test the file further this weekend, and get back to you with questions.
Have a great weekend!
Dave
Have a great weekend!
Dave
ok fine n hv a good weekend.
gowflow
gowflow
ASKER
Gowflow:
Testing the sheets, and everything is performing as your describe. It is awesome!
I also see that adding a new food item is just a matter of adding it to the Food List table.
I think this fully satisfies this question, beyond my expectations. Let's move on to the next steps as you describe.
Thank you!
Dave
Testing the sheets, and everything is performing as your describe. It is awesome!
I also see that adding a new food item is just a matter of adding it to the Food List table.
I think this fully satisfies this question, beyond my expectations. Let's move on to the next steps as you describe.
Thank you!
Dave
Very good. Pls go ahead and close this one as you feel appropriate and put a link in here to the new question as previous suggestion.
gowflow
gowflow
ASKER
OK. Closing this out and opening new question. Not sure how to add the link.
Dave
Dave
ASKER
A+! Solution was, as I suspected, not what I envisioned, but far more majestic. Great work!
ASKER
I have opened a related case to address adjusting values based on the difference between the actual serving size and the serving size listed on the master Food list. It is case ID 28595451.
Dave
Dave
Sorry cannot find the question as this ID point to this question. go to the new question and copy/paste the http address in here that is how you put a link to the new question.
gowflow
gowflow
Did you see my last comment ?
gowflow
gowflow
ASKER
Gowflow:
Good to hear from you! No, I did not see your previous post. Here is the new ID number: 28595451.
Dave
Good to hear from you! No, I did not see your previous post. Here is the new ID number: 28595451.
Dave
Why you need to do this ?
gowlfow