Comments on: Excel named range - how to define and use names in Excel

Names in Excel are a paradoxical thing: being one of the most useful features, they are often considered pointless or nerdy. This tutorial will teach you different ways to create a named range in Excel to make your formulas much easier to write, read, and re-use. Continue reading →

Comments page 3. Total comments: 107

  1. Is there a way I could use named ranges in a cell comment?

  2. Hi, I am using a Named Range on a sheet. I have cell range unlocked and rest of sheet locked It runs fine with sheet uprotected, however if I protect the sheet the Named Ranges fails to work.
    Can you assist please?

  3. Hi, i need to know how we can get "true" ot "false" result using if function taking base from "named range".
    Eg- i have named range of colour as "color_list" having 5 color as red white, black, orange and green.
    Now i need need to get true or false result using if funtion taking information from "color_list".
    =if (and (A2="red",A3=colour_list)),"true","false"
    If cell A2 have text "red" and cell A3 have any color list from the named range "color_list" , then results "true" else " false".
    But i didnt get result using above formula.
    How can we get it.
    Please guide.

  4. Hi,

    I want to know can we save the background color for the cell in the name manager too rather than the constant value?

  5. Hi, how can i create dependent validation list if i have multiple table to select

  6. Hi,
    I want to start by thanking you for your articles. They are great!
    I am copying some worksheets from one workbook to another one as I need them as templates. There are about 300 names and after copying the worksheet, they show the source. Is it possible to edit them all at once, so that part can be replaced with a blank?
    Thank you!

  7. Hello,
    I would need to create a name [easy], calling another excel file [with multiple tab]. The address of the called/linked excel file, may change in location.
    as exemple, I'm looking for this; =namefilelinked+tab number+row number
    the second part of formula, '+tab number+row number' never changed. It is fixed.
    Is that possible and how?
    Thank you

  8. Hello,
    Is there anyone can help me with my query, I have around 100 sheets in my workbook; there are number of ranges from each workbook has a defined name.
    I am looking for a way to allow user to Pick any of the defined name ranges from a picklist and then on clicking can navigate to that specific workbook (range).

    Thanks,
    Ankit

  9. I am trying to make a packing list in Excel. The items to pack are boxes labeled things like Microphones, Grip, Clamps, DAW, Drapes, and Green Room. Each of those boxes contain a list of items to be packed. what I would like to do is make a master sheet that has every box and ever boxes' contents. then when we pack for a project, I could generate a customized list from the master list by simply inserting on a new sheet something like "=Microphone_Box", and getting the range names Microphone_Box" populated on my customized list.
    Am I making any sense and can it be done in Excel?

  10. Hi, Is there a way to make a cell refer to a named range? For example, the Covar function requires two arrays. Instead of typing in both named ranges, I would like to make the Covar function reference two cells that reference the named ranges. To take the covariance of ~500 stock's price data, I would have to manually enter in the named ranges for 250,000 cells. Is here an easier way to accomplish this?

  11. I have a workbook with twelve sheets, one for each month. I do several calculations(formulas), but the subsequent sheets only use the values from sheet 1. I've tried using "Jan:Dec!$d$4:$d$6" to include all of the sheets, but I get a #value! error indicating wrong data type.

  12. i have a problem in create a defined name using "Formulas>Create from selection>Top Row".If i try to create a defined names for more than 20 columns using Top rows,It created defined name for some columns and some columns were missing.How to resolve this issue

  13. I named a cell but made a spelling mistake. How can I correct the spelling in the name?
    Thanks,

    Paul

  14. Where can i find these named ranges when in Word to insert into a mail merge?

  15. Hello,
    would it be possible to include in a formula a text from a cell that refers to a given "name" that identify a column of values? I have the closing prices of a stock in column E that I named "Close", I had the cell N1 with the text "close", I would like to reference to cell N1 in the formula contained in the cell O8 (=min(Close)) as cell, so I would use, in O8 =min(N1). Changing the text in N1 would report the value calculated for the same function applied to different set of values (columns).
    Thanks for the support you can give me.

    1. Was there ever any answer to this? I’d love to know! I’m using a series of List Validations to develop a Budget vs Actual comparison tool. I’ve named ranges for Budget and Actual over similar periods and would like the names range referenced to change depending on the period and budget vs actual selection. This would be perfect!

      1. Just tried an update to my formulas using the =indirect() formula. Now I m able to change my vlookup to whatever table array the logic in my target cell returns. So instead of hardcoding the names range for the vlookup - I’ve pointed it to a cell and that cell results in the names range I’m looking for (=vlookup(a14,indirect(L17),b5,false) where a14 = row ref I’m searching for and l17 refers to the bud or actual PLs, b5 is column ref).

        Good luck!

        1. Dan - Thank you, thank you, thank you!!! You had the answer I have been searching for!!! I have been successfully able to use the INDIRECT formula within my VLOOKUP formula to be able to summarise my results by quarter (after first defining names for the data for each quarter).

  16. Can you use named ranges with the '=IF()" function? I have two columns of data and want to use a formula like this: =IF($A1=ListB, $A1, "No Poster") in column C where ListB is the named range B1:B100. Unfortunately the formula only works if data in A"X" and B"X" are the same, if the same data that is in A1 is in B3 for example I get the "No Poster" message in C1.

  17. Hi:
    Is it possible to have a named range available to any worksheet? What I mean is I want a named range available in named manager every time I open a new or an existing worksheet.

    Thanks you very much

    Mike.

    1. Hi Mike.

      Unfortunately, it is not possible to create a named range available in all the worksheets using the standard Excel features. However, it is possible to do using a special macro. I am really sorry, but we can’t help you with this. You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.

      Sorry for not being able to help you more.

  18. Hi there,

    I would like to define a Name that references the name's calories. However, when I list the name without an =, (so that it just says, Whole_Wheat_Bread) I would like to reference its value in another column titled Calories.

    i.e.

    Column A:
    Row 1: Food
    Row 2: Whole_Wheat_Bread
    Column B:
    Row 1: Calories
    Row 2: =A2

    [Assigned Value for Whole_Wheat_Bread is 78]

    However, when I sype =A2 it enters "Whole_Wheat_Bread" in the cell;
    I would like it to enter the Value associated with Whole_Wheat_Bread (which is 78)

    This is so when I enter food items for a daily diet, I can just enter the titled food item and let the caloric section autofill with the selected values so I do not have to type ='adjusted new food' in the calories column.

    Thank you!

    1. I believe it is the same answer as under post 11 (INDIRECT-formula).

  19. If I define with a name a range of cells and later insert a column or row through that defined block, does that new column or row become part of the defined range, or does the definition cease to apply, or will it split the defined thing into two parts, or just exclude the new column or row from the range and just run either side of it?

  20. Is it possible to use Concatenate or the ampersand to develop a known value name for use in a formula?
    I have several list selections in a spreadsheet, that when merged (concatenated) will give me one of several named values. I would like to avoid having a nasty IF statement just to accommodate the all the possible combinations.

  21. Hello

    I am looking to use VLOOKUP to return certain column data out of an array.
    Outside of a table this is what I have been using:
    =VLOOKUP($B2,Sheet2!($A$8:$H$48,Column(B8),FALSE) and then dragged the formula across and down the page. This is fine, as the lookup locks to B.

    However, when in a table with named ranges, I cannot drag the formula across as the initial reference keep changing as I drag across.
    =VLOOKUP([@sampleid],[Book1]Sheet1!$A$8:$H$48,COLUMN(B8),FALSE)

    The initial [@sampleid] needs to be locked, I assumed with $ but nowhere i put it allows the formula to function...

  22. I have a named range. Is it possible to reference, via a formula, individual cells in that range? My range is 3 columns by 1 row. I would like to be able to access each cell individually utilizing the range name.

  23. Need more clarity on.

    Apply names

    Ignore Relative/Absolute

    Use row and column names

    Regards
    Lalit.M

  24. I set a field with formula to link with the month of the date and set a list of name by "month" with formula. Is it possible to use the formula result to call the "names" result?
    ie 01 Jan 2018
    field A =left(A1,4,3),result=Jan
    Names set : Jan = B3
    End up, field A = B3
    I tried, but failed.
    Thanks

  25. Is there a way to create a name without the sheet reference? I want to have a name that references Sheet1!$S:$S if I use it in Sheet1 and Sheet2!$S:$S if I use it in Sheet2 but I can't figure out how to do that without using multiple names and I would need too many names for that.

    1. Hello, Garrett,

      Sure there is! You just need to name the range you want to use and then use this new name in references instead.
      Please have a closer look at this point of the article above for more details.

      If you mean that you don't want to use the "Sheet" word when naming your range, I'm afraid it's impossible to omit that part since Excel should understand what cells you refer to exactly.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)