Solved

Am I Correct in Using a Named Range to Insert Multiple Cells?

Posted on 2015-01-04
43
69 Views
Last Modified: 2015-01-14
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?
0
Comment
Question by:Dave Kong
  • 22
  • 21
43 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I will ask a question:

Why you need to do this ?
gowlfow
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
no file attached !
gowflow
0
 

Author Comment

by:Dave Kong
Comment Utility
Once more, with feeling.  Sorry about that!
Food-Log-December-2014.xlsx
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
I am working on something inline of my latest comments. Will revert.
gowflow
0
 

Author Comment

by:Dave Kong
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
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.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
That makes sense.  So I should go ahead and modify my copy while you are working?  I can do that.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
Ah, got it, thanks.  I am doing actual work now, but will get to that this evening.

Dave
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Also an other one I just stumbled on,
Catfish Filet
does not exist in your list !!!
How do we do then ?

gowflow
0
 

Author Comment

by:Dave Kong
Comment Utility
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.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok now you have changed the format between the new layout and the old one. I have changed the format of all your old sheets to correspond to the new format then you can benefit from the old data as well !!!!

I will upload the version I worked on and you can see how it works. Here are some indications:

0) Enable macros as soon as you load the file.

1) Each time you activate a sheet the macro automatically kicks off.

2) When your cursor hit a cell in Col C (which is description for the new format) you will see a combobox.

3) If the item  that is in the cell is found in the table then you will see it thru the combobox directly. If the item is not found like cattle fish or other twisted items like (in the table broccoli, fresh steamed but you typed broccoli steamed so obviously it did not find it then you will see the combobox turn blank) !!! don't worry just move to next row it will appear again the data will not be changed unless YOU CHANGE IT !

4) Now I suggest you go in Col C down or in the top rows till you find a blank row to test. Select an item from the combo and see what you get. Select an other one and see how it changes the data. You can at any time select all the data in the row and delete it Just highlight the data and press on the DEL button. You can also if you want type an item that is not in the list simply by pressing F2 when you are in Col C you will go in edit mode and then type in what ever you want same as before. For sure in this case you cannot expect to get figures.

My suggestion at this point.

Test it and if all ok then I guess this is it for this question we can then move to the next issue that will be to get your Col A divided in A and B. Then we can worry about the conversion issues.

Meantime,
You need to do some homework if you are interested to have your present data 'clean and updated' then You need to go thru each sheet and each item and when the item  is blank find it in the list and choose the proper one so it updates the figures correctly. This is a suggestion but guess you are pro in your work and for sure know what to do.


Let me know your comments.
gowflow
Food-Log-December-2014-V02.xlsm
0
 

Author Comment

by:Dave Kong
Comment Utility
Super-fantastic!  I will take a look at this as soon as I can.  Thanks!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok no problem let me know your feedback.
gowflow
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok fine n hv a good weekend.
gowflow
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 

Author Comment

by:Dave Kong
Comment Utility
OK.  Closing this out and opening new question.  Not sure how to add the link.

Dave
0
 

Author Closing Comment

by:Dave Kong
Comment Utility
A+!  Solution was, as I suspected, not what I envisioned, but far more majestic.  Great work!
0
 

Author Comment

by:Dave Kong
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Did you see my last comment ?
gowflow
0
 

Author Comment

by:Dave Kong
Comment Utility
Gowflow:

Good to hear from you!  No, I did not see your previous post.  Here is the new ID number:  28595451.

Dave
0
 

Author Comment

by:Dave Kong
Comment Utility
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
The viewer will learn how to edit the master slide. They will also learn how to combine multiple themes into one master slide to use them in their presentation.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now