• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Dynamic Named Ranges

You are here: Home / Excel Formulas / Excel Dynamic Named Ranges
Excel Dynamic Named Ranges
January 24, 2013 by Mynda Treacy

How much time do you spend editing the ranges referenced in your formulas because you’ve added new data to your source?

This is what I’m talking about; let’s say you have some data in columns A:E:

dynamic named range

And in columns G and H you have a summary of the data, in this example, sales by Salesperson:

dynamic named range

See the formula in the formula bar is fixed on cells B2:B19 and E2:E19. When I add new data for September to my data in columns A:E I have to manually edit my formulas in column H to include the new rows.

Let me just say, if you do this type of thing more than once in any workbook then you are wasting valuable time, and you should be setting up your formulas with dynamic ranges.

dynamic named range

Dynamic ranges automatically detect new data and include it in your formulas.


Forget VBA, this is hands down one of the best ways to save time when working with Excel.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook and follow along. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Dynamic References

There are a few different ways to set up ranges so that they dynamically update.

1. Whole Column/Row References

For example taking the data above you could replace the formula in cell H2 with:

=SUMIF($B:$B,G2,$E:$E)

While this will work, it is the lazy way to create dynamic references and it’s not recommended.

Why? Because you’re asking Excel to do a load of work checking cells that are empty, and in Excel 2007 onward there are 1M+ cells in every column. The bottom line is whole column/row references = a slow workbook (usually).

Tip: If your workbooks are slow to calculate and save, and you have whole column or row references then this is likely to be the culprit.

2. Excel Tables

Dynamic references are just one of the benefits to housing your data in Excel Tables. Find out how to set them up and the other benefits of Excel Tables here.

Tip: If formulas are still a little daunting for you then Excel Tables will give you loads of benefits without the need for complex formulas.

3. Dynamic Named Range Using OFFSET

Now we’re getting a bit more sophisticated.

We can replace our formula in the example above with this:

=SUMIF(salesperson,J2,OrderAmt)

Where ‘salesperson’ and ‘OrderAmt’ are dynamic named ranges, as you can see in the Name Manager below:

dynamic named range

How to set up a dynamic named range

To set up any Named range go to the Formulas tab of the ribbon and click ‘Define Name’:

dynamic named range

We can make it dynamic by entering a formula in the ‘Refers to:’ field like this:

dynamic named range

Hold up, let’s take a closer look at that formula in the ‘Refers to:’ field:

=Sheet1!$B$2:OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B$2:$B$1000),,1)

The first part in orange is nothing special, but the second part in blue and pink uses the OFFSET and COUNTA functions to return a reference to the last cell in the range B2:B1000 that contains data.

Here’s a quick reminder of the syntax for the OFFSET function:

OFFSET(reference,rows,cols,[height],[width])

There’s an in-depth tutorial on the OFFSET function here, but I’ll quickly translate what this formula is doing in English.

=Reference the range that starts in cell B2:, and use the OFFSET and COUNTA functions to find the last cell in the range that contains data in cells B2:B1000. In the OFFSET function start in cell B1, and count all cells in the range B2:B1000 that contain data, (which = 18 i.e. rows 2 to 19), and return a reference to a cell that is 1 cell high.

That is, if I start in cell B1 and count down 18 cells I end up in cell, B19 therefore my formula evaluates like this:

=Sheet1$B$2:$B$19

When new data is added after cell B19 the formula will dynamically update to return the correct range, up to a maximum of row 1000 (because I’m only counting data in the range B2:B1000).

Notes:

  1. You can’t have any blanks in B2:B1000 because the COUNTA function will not count those cells and this would result in an incorrect range.
  2. If you’re counting numbers you can replace the COUNTA function with the COUNT function like I’ve done for the OrderAmt named range (see the Name Manager image above).
  3. If you think your data will exceed 1000 rows simply increase the row number to adequately accommodate it.

The Problem with OFFSET

Now, while a dynamic named range using OFFSET is good, there is one main problem with it:

OFFSET is a volatile function, which means every time you edit any cell it recalculates. The impact of this can render large workbooks slow, very slow.

dynamic named range

So slow that you avoid saving your work because it’ll mean you’re left twiddling your thumbs. You end up not saving your work because you don’t want to develop unsightly callouses, then your PC crashes…..and, well, you know the rest.

This is the reason why the next option is the best.

4. Dynamic Named Range using INDEX

The INDEX function is not only non-volatile, it’s faster than the OFFSET function.

We can set up a dynamic named range for the Salesperson column using INDEX like this:

=Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$1000,COUNTA(Sheet1!$B$2:$B$1000))

And for the Order Amount column like this:

=Sheet1!$E$2:INDEX(Sheet1!$E$2:$E$1000,COUNT(Sheet1!$E$2:$E$1000))

The syntax for the INDEX function in its reference form is:

INDEX(reference, row_num, [column_num], [area_num])

Note: the last two arguments, [column_num] and [area_num] are optional as we can tell from the square brackets. We don’t need them in this formula.

I’ll translate the Order Amount formula into English:

=Reference the range that starts in cell E2:, then INDEX the range E2:E1000, and return the row_num of the last cell containing data by counting all cells in the range B2:B1000 that contain numbers, (which = 18 i.e. rows 2 to 19), Index therefore returns a reference to E19.

My formula evaluates like this:

=Sheet1$E$2:$E$19

This is just one of the tricks in the INDEX hat. When teamed up with MATCH it can replace VLOOKUP, in fact INDEX & MATCH are faster than VLOOKUP too.

Daniel Ferry, ex MVP, owner of the Excel Hero blog and highly acclaimed Excel Hero Academy says:

“INDEX is the single most important function in the roster of Microsoft Excel functions”.

Click here for a comprehensive tutorial on the INDEX function.

Excel Dynamic Named Ranges

More Named Ranges Posts

Relative Named Ranges

Relative Named Ranges in Excel allow you to reuse named ranges with different results dependent on the cell you use them in.
Excel Named Ranges

Excel Named Range Shortcut

This Excel Named Range shortcut using the exclamation mark will enable you to create a global name with local scope.
Writing Excel Formulas Efficiently

Writing Excel Formulas Efficiently

Formulas should and can be quick to write, interpret and update. That's why writing Excel formulas efficiently should be one of the first skills you master.
excel named ranges explained

Excel Named Ranges Explained

Excel named ranges can be a huge time saver. They're easy to use and even easier to read in your formulas.

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Excel FormulasTag: named ranges
Previous Post:Excel CONVERT FunctionExcel CONVERT Function
Next Post:Excel Same Same but DifferentExcel Same Same but Different

Reader Interactions

Comments

  1. Peter Sumner

    April 29, 2022 at 3:55 pm

    I have used the OFFSET function before but it is particularly useful if you have dropdown validation lists that may require updating. You simply refer to the Named Range when setting up your validation. The dropdowns dynamically update without having to ammend your validation settings.

    Reply
  2. Ryan Merullo

    April 11, 2019 at 1:09 am

    Neither the offset nor index approach works with the SUMIFS function if the ranges contain blank cells in different rows. You can use either approach to perform a single operation on a single expanding range. SUMIFS is an extremely handy function. Thus far, I am using the primitive method of guesstimating liberally beyond my data. I have data, including many blank cells, in range A4:L1350. I am able to use SUMIFS, not with any of the OFFSET or INDEX formulas offered, but simply with the following:
    =SUMIFS($G$4:$G$10000,$D$4:$D$10000,”*Item”,$C$4:$C$10000,”Person*”)

    Reply
  3. Sophie

    November 13, 2018 at 11:06 pm

    Thanks Mynda, I really like your step by steps !
    I’m trying to set up a dynamic reference for a table with data re male and female. My goal is to build a chart with 1 serie with data re male and the other re female. Currently, each time I refresh the data, I sort the table by male/female then edit manually the graph series. Is there a way I could avoid that manual sort and let excel plot the “male” and female” series ?

    Reply
    • Mynda Treacy

      November 14, 2018 at 8:53 am

      Hi Sohpie,

      I’d use a Pivot Table to summarise the data and automatically sort it. That way when you add new data it will automatically re-sort it. Make sure the PivotTable source data is formatted in an Excel Table so that when you add new data the PivotTable range automatically picks it up upon refresh.

      If you get stuck please post your question and Excel sample file on our forum where we can help you further.

      Mynda

      Reply
  4. Ram

    January 25, 2018 at 9:12 pm

    Excellent article. A video could have been much better unless it is separately available

    Reply
  5. Terence

    August 26, 2017 at 1:02 am

    Great post, love the multiple options all explained thoroughly.
    Just out of curiosity, on the offset example is there a reason for doing cell:offset cell rather than using the height functionality of the offset?

    Reply
    • Mynda Treacy

      August 26, 2017 at 6:24 pm

      Hi Terence,

      Thanks for your kind words.

      There’s no particular reason for creating the range using cell:offset vs using the height parameter inside of OFFSET. I think I was just being consistent with the approach used to write the INDEX function dynamic named range, and so I wrote the OFFSET formula that way to make it easier to compare the two functions.

      Mynda

      Reply
  6. George Prattos

    April 2, 2017 at 6:02 pm

    Hi Mynda,

    After studying your website and trying things out in the process of getting to terms with dynamic ranges, I seem to have created dynamic range names without using any functions at all, i.e. without Offset, Index or Indirect. This came as a surprise…

    Starting with a copy of your raw data (columns A:E) I did the following:

    1. Made a table of the raw data, including headings (Table1).

    2. In column B: selected all Salesperson data excluding the heading and created a range name SalesPax with it.

    3. In column E: selected all Order Amount data excluding the heading and created range name OrderAmt with it.
    Both these are dynamic range names by virtue of being table data.

    4. In Total Sales cell H2 used the formula =SUMIF(Salespax,G2,OrderAmt) which the system accepted and converted to =SUMIF(Table1[Salesperson],G2,Table1[Order Amount]).

    5. Copied H2 to cells H3 through H8.

    I’ve tested this by adding and deleting both Salespersons and Order Amounts to/from the original data table and all Total Sales in column H update correctly.
    I welcome feedback on this approach. Thank you.

    Regards,
    George

    Reply
    • Mynda Treacy

      April 2, 2017 at 7:00 pm

      Hi George,

      Yep, that’s one of the features of Excel Tables. In fact you don’t even need to create named ranges. You can just refer to the table and columns using their Structured References. All explained here.

      Note: sometimes formatting your data in an Excel table isn’t ideal. For example, they aren’t suitable for data over 500k rows.

      Mynda

      Reply
  7. Bill Tastle

    March 17, 2017 at 12:57 am

    There is (surprisingly!) another way to create this range using INDIRECT.
    =INDIRECT(“B2:B”&COUNTA(B:B)-1)
    While this bypasses the OFFSET function, it does require one to grasp the use of INDIRECT. In short, INDIRECT converts any text address as an actual address. Thus, it cannot be used by itself for it generates a #VALUE! error, the same one as the OFFSET function when used by itself.

    Reply
    • Mynda Treacy

      March 17, 2017 at 9:40 am

      Hi Bill,

      That’s one of the great things about Excel. I would avoid using INDIRECT where ever possible though because it’s a volatile function and can cause performance issues in medium/large workbooks.

      Mynda

      Reply
  8. Rajesh Sinha

    March 1, 2017 at 11:22 pm

    Useful information,, cud u Plzzz post some samples, to Optimize data in Excel, for example PIPS data.

    Reply
    • Mynda Treacy

      March 2, 2017 at 10:39 am

      Hi Rajesh,

      Sorry, I’m not familiar with PIPS data.

      Mynda

      Reply
  9. Phil

    December 12, 2016 at 2:26 pm

    Thank you, this was very helpful

    Reply
  10. Tim Hale

    August 23, 2016 at 6:42 am

    I really like your explanation of the dynamic range name. Thank you for sharing.
    Tim Hale

    Reply
    • Mynda Treacy

      August 23, 2016 at 9:03 am

      Cheers, Tim. Glad I could help.

      Reply
  11. Luc Van Uffelen

    July 8, 2016 at 6:09 pm

    I successfully applied the INDEX method in my files, but the named ranges disappear in the Name Box. What can be done to avoid this consequence of the INDEX method?

    Reply
    • Mynda Treacy

      July 8, 2016 at 6:26 pm

      Hi Luc,

      Dynamic named ranges never appear in the name box. It’s just how it is I’m afraid.

      Mynda

      Reply
  12. Ed

    December 17, 2015 at 8:09 am

    I’m missing something here. I don’t see the advantage of using a dynamic named range rather than an Excel table. It seems to me the whole point of tables is that they are dynamic and “automatically” generate named ranges without the user having to identify ranges or use complex formula.

    Reply
    • Mynda Treacy

      December 17, 2015 at 12:46 pm

      Hi Ed,

      I too like using Excel Table’s dynamic structured references but Tables aren’t always an option so it’s good to know how to create a dynamic named range with formulas too.

      For example, Tables can become very slow when they are a lot of formulas and are not always suitable for big data. They’re also not compatible with Excel 2003, although these days no one should be using Excel 2003 anymore 🙂

      Kind regards,

      Mynda

      Mynda

      Reply
  13. Arthur Arkin

    December 14, 2015 at 1:49 pm

    I am trying to replicate what you have shown in excel_manual_chart_table, but there is no way I can do it. You seem to assume that one would intuitively know how to do all the things you show but the instructions are not detailed enough. For example, when I create the tow pivots tables, where do I put them? I notice that the chart for one table is sorted differently, but there was no instruction to sort anything. Sorry, I’m a beginner with pivot tables and charts, and I need step-by-step instructions, with nothing left to the imagination. Yet, this particular operation is very important to me. Hope you can help.

    Arthur

    Reply
    • Mynda Treacy

      December 14, 2015 at 1:59 pm

      Hi Arthur,

      Great to see you’re having a go at replicating this technique.

      If you’ve downloaded the file you’ll see where the PivotTables are in my file, however the location of them can be anywhere in the file. It makes no difference.

      You don’t have to sort the PivotTable data, I just did because it’s useful to the chart reader. Sorting is easy, select a value (number) cell in the column you want to sort > right-click > sort > choose your sort order. You only need to sort the first PivotTable since the values from the second one are brought in using VLOOKUP and will therefore be sorted based on the first PivotTable.

      Kind regards,

      Mynda

      Reply
  14. david Ostreicher

    November 26, 2015 at 8:17 am

    Hi Mynda.,
    These are great tools for dynamically naming ranges, and I love the way Tables are automatically dynamic.

    What I’m trying to do though, is to use the offset or index function (based on a range in a separate sheet, and couple that with the table’s built in function, to make it automatically resize itself with the same amount of rows as my other range. That way, I imagine, the same way when you add data to the cell directly beneath a table, the table automatically resizes and copies down all the formulas of the other columns in the table, in this case, as I add a row to my “other” range on the other sheet, my Table will automatically add a line and copy down ALL formulas… even that for the first data point.

    Sorry… I hope I was clear enough.

    Thank you very much for all your help.

    Reply
    • Mynda Treacy

      November 26, 2015 at 12:13 pm

      Hi David,

      Great to hear you’ll be making use of dynamic ranges.

      I’m struggling to picture the dependencies in your workbook. Are you able to send me a sample file with your question so I can better understand, and also give you a tailored answer? You can send it via the Help Desk.

      Kind regards,

      Mynda

      Reply
  15. Joyce

    May 20, 2015 at 2:50 am

    Now when I think of all the old models where I used OFFSET in my dynamic named ranges I will dream about replacing it with INDEX based formulas as you outlined. Thank you for the tip!

    Reply
    • Mynda Treacy

      May 20, 2015 at 7:55 am

      🙂 great to hear, Joyce.

      Reply
  16. Leah

    August 1, 2014 at 8:13 am

    Hi Mynda ~
    For anyone who routinely updates named ranges this is amazing! Of course, now that I’m trying to apply it, I’m stumbling. My first test file uses a named range in a vlookup. The current named range encompasses 2 columns (the first one is the lookup value [employee id], the second is the return value [employee name]) with data starting in cell B4 (header included). I attempted to use this formula for the named range but it errors out –=Employee!$B$4:INDEX(=Employee!$B$4:$C$4500,COUNT(=Employee!$b$4:$c$4500)). Either this won’t work across multiple columns or I’ve messed up the syntax somewhere. Would you mind pointing me back in the correct direction? Thanks!

    Reply
    • Mynda Treacy

      August 1, 2014 at 9:13 am

      Hi Leah,

      It’s tricky to say for sure but, your formula contains = signs where they’re not required so let’s start by eliminating those so your formula looks like this:

      =Employee!$B$4:INDEX(Employee!$B$4:$C$4500,COUNT(Employee!$b$4:$c$4500))

      Also, your INDEX formula is referencing B4:C4500 which is 2 columns, but you haven’t got a column_num argument in your INDEX formula. You either need to give it a column_num argument or make your range only 1 column wide.

      Your COUNT function is also couting 2 columns: B4:C4500 you need to choose just the column you want to count, and if that column contains text as opposed to numbers then you need to use COUNTA instead of COUNT. COUNT only counts numbers.

      If that doesn’t work then I’ll need you to send me the file via our Help Desk so I can see all of the contributing factors.

      Kind regards,

      Mynda

      Reply
      • Leah

        August 2, 2014 at 1:01 am

        Thanks Mynda!
        I modified the formula to this >> =Employee!$B$4:INDEX(Employee!$B$4:$C$4500,COUNT(Employee!$B$4:$B$4500),2) << and it worked! Absolutely fantastic. I can't wait to share it with my team.

        Reply
        • Mynda Treacy

          August 2, 2014 at 7:23 am

          That’s great, Leah. You should give yourself a pat on the back for mastering dynamic ranges using INDEX. Well done.

          Mynda

          Reply
  17. Kenneth J. Nessing

    June 10, 2014 at 5:51 am

    Mynda: Another home run, mate! The Dynamic Range info is priceless (actually, I’m sure we could agree on a price!). 😉

    Ken

    Reply
    • Mynda Treacy

      June 10, 2014 at 12:51 pm

      Cheers, Ken. Just reaching for my calculator now 😉

      Reply
  18. Rebekah

    April 29, 2014 at 1:47 pm

    I hv a question in setting range using table.

    I hv a report which has 5 sections. Each section starts with a function, eg cell A10 = Admin. Each of the function has the same set chart of accounts.

    Everymonth, I generate a new report, copy and paste the contents to a file which has formulae populating the data to a summary sheet in the same file.

    The problem I hv is that when a new account is added to the chart of accounts, the range changes. I try to range each function using the table, unfortunately, when I copy and paste the new data, the table range seems hv removed.

    Is there any other way to over come this problem?

    thanks for your help.

    rgds

    Reply
    • Catalin Bombea

      April 29, 2014 at 3:54 pm

      Hi Rebekah,
      Looks like you are pasting data over the entire table, including headers, this operation will delete the table. You should not do that, for example, if the first row contain headers, paste data under the headers row, starting from A2. This way, if you have extra rows, the table will autoexpand to include new rows, and even new columns (the new columns headers will be named automatically to Column1, Column2)
      Of course, this assumes that the data structure is the same for the new data, and new columns are added to the right side of the table, not between initial data structure.
      If the headers row structure is not the same, to preserve the table you can paste new headers to headers row, after you paste the data.
      Catalin

      Reply
  19. Maxime Manuel

    October 11, 2013 at 11:53 am

    Whoever from Microsoft Corp that invemted INDEX deserves a place in paradise. This function is gift from heaven.

    Reply
    • Mynda Treacy

      October 11, 2013 at 11:55 am

      🙂 Indeed, Maxime.

      Reply
      • Maxime Manuel

        October 11, 2013 at 7:34 pm

        Minda, I never learne Excel but my level is very advanced. I am the Excel Guru at work, no joke. I can do most of the dashboards that I see online, I create my own, I helped all the departments in my organization using Excel, etc. I finally want to have a certification in advanced Excel with you guys. Can you send me the curriculum of your courses?
        Another thing, what is the career of an Excel Guru? What should be his position in an organisation? What path should he follow? Please let me know. Thanks! 🙂

        Reply
        • Mynda Treacy

          October 11, 2013 at 8:58 pm

          Hi Maxime,

          It’s great to hear you are helping people get more out of Excel.

          You can see our course syllabuses from the Pricing menu, then select the course you’re interested in. Each page with have a link to the syllabus.

          Excel Guru’s come in all shapes and sizes and there is no definitive path. For example; you are already a Guru in your workplace. Helping out in Excel forums is a great way to increase your Excel knowledge. Typically the questions you find in forums are unique and challenging.

          All the best with your journey to Excel heights.

          Kind regards,

          Mynda.

          Reply
          • Maxime Manuel

            October 13, 2013 at 2:13 am

            Thank you!

  20. Vinay J

    July 9, 2013 at 9:24 pm

    Hi Mynda, Really love your site. It has taught me so much already – especially the beautiful elegant way you have for explaining any topic. Such a contrast from the inbuilt Help with Excel.

    Anyhow, I was wondering if you can help me out with a couple of queries on Dynamic Ranges and their application. Briefly, I have three worksheets (M,B,C) Each of them has an identical columnwise (15 columns) layout though the number of entries (rows) in each is different and is constantly growing. Hence I have made Dynamic Named Ranges (M,B,C) all with Workbook scope. Currently the number of rows of data (excluding Headers) for M is 25, for B is 60 and for C is 10.

    Now I want to consolidate the three sheets such that all the data from M (25 rows across 15 columns) and B (60 rows across 15 columns) and C (10 rows across 15 columns) should be stacked one of top of the other i.e. excluding headers, row 1-25 should be M, row 26-86 should be B and row 87-97 should be C in a Master Sheet. Note-it should not sum the data but each individual row should be reflected in Master sheet such that Master sheet should have 97 rows and 15 columns of data. And thereafter as more and more rows of data are added to the 3 sheets, the Master sheet will incorporate all the entries (both old and new)

    Is it even possible for Named Ranges to be displayed this way or can they only be displayed if some function is applied to them? Of course if it is not possible I would love to understand whether the issue is with the ranges being non-contiguous, being dynamic or their positioning on multiple worksheets or anything else. Intuitively I think it should be doable but I am unable to implement it.

    VBA solution I have found is to create a temporary range which is a union of the three ranges but I would prefer to do it without VBA as multiple users will handle the document and are not as proficient/ comfortable.

    Alternatively, a solution is to use multiple range/sheet inputs in Pivot tables – however, I am not able to get it to work as the page fields do not
    reflect all the column headings the way it reflects when using a single input/sheet range. So solutions from this angle would also be welcome.

    Thanks so much!

    Keep up the AWESOMENESS!!

    Reply
    • Mynda Treacy

      July 9, 2013 at 9:40 pm

      Hi Vinay,

      Thanks for your kind words.

      In regards to your question; I don’t know of a way to create a 3D dynamic named range. I’ve never seen it done and I can’t think of a way you could do it without VBA.

      I would make all of your worksheets uniform and use PivotTables.

      You haven’t said what you need this dynamic range for. I wonder if this tutorial on 3D SUMIF might be relevant.

      Kind regards,

      Mynda.

      Reply
      • Vinay J

        July 10, 2013 at 12:45 am

        No Mynda, 3D Sumif isn’t relevant for this instance, although it seems like an interesting thing to learn.

        I am not sure I explained myself correctly. What I am looking for is a way to consolidate multiple ranges together without summing them or any function. Just display the raw consolidated data one below another.

        All worksheets are already uniform in layout – unfortunately I am not able to use the Pivot tables with multiple ranges as inputs as the page fields do not reflect all the column headings the way it reflects when using a single input/sheet range. Perhaps you have some suggestions/ tips for using inputs from multiple ranges/ worksheets?

        Reply
        • Mynda Treacy

          July 10, 2013 at 10:36 pm

          Hi Vinay,

          Yes, I know what you mean with consolidating PivotTables. Unfortnately the only other options are Copy and Paste all the data into one master worksheet, or find some VBA to do the copying and pasting for you.

          Kind regards,

          Mynda.

          Reply
  21. Theron P. Yates

    February 5, 2013 at 1:10 pm

    dear all. this code copy cell step by step. do you know any other way to write the code easier?

    Reply
    • Carlo Estopia

      February 5, 2013 at 11:28 pm

      Hi Theron,

      I really don’t think there can be any other, easier way of doing this code.
      However, you may go to our HELP DESK and I’ll give you a file that could automate
      some. Just specify what part of this topic explained do you want it to be
      automated.

      Cheers.

      CarloE

      Reply
      • Kevin

        February 7, 2013 at 12:19 am

        a quick tip I picked up from another site for data validation

        Lets say we are working with Range A1:A10 and A1 has the header to our list

        Select A1:A10 and insert table (check the box “mt table has headers”)

        (A1:A10 is now a table)

        Select A2:A10 and name the range

        Insert Data Validation in whatever cell you need it and input the range you named in previous step

        thats it 🙂 you now have a dynamic range

        If you add data to cell A11 it should appear in your drop down list

        Reply
        • Carlo Estopia

          February 7, 2013 at 11:40 pm

          Hi Kevin,

          Thanks for the tips.

          Sincerely,

          CarloE

          Reply
  22. Keith Thompson

    January 25, 2013 at 10:46 am

    Hi Mynda,

    I have found the Dynamic Named Ranges using the INDEX function extremely useful, but have come across one small issue that you might be able to help me with. I have an excel based time-sheet for staff where they choose the site worked from a drop down list using Data Validation. As the number of sites is always changing I have named the site list using a dynamic named range using the INDEX function, but now find when you click on the drop down list you see the blank cells and have to scroll up each time to see the site list, is there a way around this?

    Regards,

    Keith

    Reply
    • Carlo Estopia

      January 25, 2013 at 9:35 pm

      Hi Keith,

      It’s good to hear that you have found it useful. I tried to simulate and walked through the file and I had a hard time trying to get
      the same problem you have.

      Anyway, It would be better for you to send your file through HELP DESK so we can have a better look at it.

      Sincerely,

      CarloE

      Reply
    • Mynda Treacy

      January 25, 2013 at 9:58 pm

      Hi Keith,

      First you need to create a list that doesn’t have blanks. Let’s say your list containing blanks is in cells A2:A9. In cell C2 enter this formula:

      =IFERROR(INDEX($A$2:$A$9,SMALL(IF(ISBLANK($A$2:$A$9),"",ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1),ROW(A1))),"")

      Enter with CTRL+SHIFT+ENTER as this is an array formula, and copy down as far as you need.

      Then set up your dynamic named range referring to cells C2:??, let’s say C2:C1000 using this OFFSET formula:

      =OFFSET(Sheet1!$C$2,0,0,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")),1)

      Or using INDEX:

      =Sheet1!$C$2:INDEX(Sheet1!$C$2:$C$1000,SUMPRODUCT(--(Sheet1!$C$2:$C$1000<>"")))

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x