+ Reply to Thread
Results 1 to 2 of 2

Dynamic Named Range: Trouble Omitting Multiple Row Heading

  1. #1
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Dynamic Named Range: Trouble Omitting Multiple Row Heading

    I've created a dynamic named range called "Ingredients" using the following formula:

    =OFFSET('Ingredient Cost Sheet'!$A$1,1,0,COUNTA('Ingredient Cost Sheet'!$A:$A)-1,1)

    I then used that dynamic named range to setup a drop down menu which worked beautifully when I had a heading that used one row, but I now have a heading that encompasses 4 rows, and I can't figure out how to make the named range omit the first 4 rows of the column. I thought the following formula change would do it, but no go:

    =OFFSET('Ingredient Cost Sheet'!$A$1,1,0,COUNTA('Ingredient Cost Sheet'!$A:$A)-4,1)

    Here's a quick screen shot of my sheet:
    Dynamic Range.jpg

    Thanks a bunch.
    (Using Excel 2003 if that makes a difference)

  2. #2
    Forum Contributor Big.Moe's Avatar
    Join Date
    03-15-2012
    Location
    Corea, Maine, USA
    MS-Off Ver
    Excel 2010
    Posts
    244

    Lightbulb Re: Dynamic Named Range: Trouble Omitting Multiple Row Heading

    Well, I kept experimenting and found my own solution. I'm not sure why this works, so if anybody could enlighten me, I'd be much appreciative.

    Here's the formula that did end up working:
    =OFFSET('Ingredient Cost Sheet'!$A$4,1,0,COUNTA('Ingredient Cost Sheet'!$A:$A)-3,1)

    I understand why changing the first number to 4 got me down to the row I wanted, but without changing the second number to -3, I ended-up with additional blanks on my drop down menu.
    Last edited by Big.Moe; 02-09-2017 at 01:27 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. multiple sort of named dynamic range in a macro
    By arb99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-22-2015, 01:45 AM
  2. Dynamic Named Range based on Pivot Table for MULTIPLE COLUMNS
    By Pho6 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 08-29-2014, 02:04 PM
  3. Dependent, Dynamic Named Range with Multiple Criteria
    By JustinCredibLee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-11-2014, 12:46 PM
  4. [SOLVED] Multiple condtion For loop, dynamic named range
    By emburl in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-04-2012, 09:59 PM
  5. Replies: 1
    Last Post: 03-08-2012, 09:42 AM
  6. [SOLVED] Trouble with dynamic named range
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-29-2006, 06:10 PM
  7. Dynamic named range across multiple sheets
    By babycody in forum Excel General
    Replies: 3
    Last Post: 07-24-2005, 02:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1