+ Reply to Thread
Results 1 to 7 of 7

Adapt formula for different array size each month

  1. #1
    Registered User
    Join Date
    02-01-2017
    Location
    Eindhoven, Netherlands
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    4

    Adapt formula for different array size each month

    I am interested, how to make easily adoptable formula for changing array each month. I had difficulties knowing how to call this problem, also maybe my terminology is not correct, hopefully the explanation is clear.

    I am using LARGE and INDEX formula to pick out some items and their prices each month. However, every month the number of items changes and that can require me to change the formula to adapt the array, for instance from $E$2:$E$78 (77 products) to $E$2:$E$80 (79 products). Of course I can do this manually by changing the number from 78 to 80, but is there a way, to maybe just refer to another cell, that would state how many items there is in the table or that it would tell the array? I don't want the user to be changing the area, just somewhere aside changing the array.

    Thank you in advance!

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Adapt formula for different array size each month

    Welcome to the forum.

    This is a perfect example of when you need a dynamic named range. Go to the Formulas tab and click the 'Define Name' button. Enter a name which is memorable enough for you to know what it means - something like NR_NumItems.
    (It's good practice to preface named ranges with NR or something, so you always know that's what your dealing with - it also stops you having problems with names such as CAB12, which is an invalid name since CAB12 is actually a cell reference, whilst NR_CAB12 is fine)

    Now enter this in the 'Refers to' box:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Click OK.

    Now wherever you want to use the range which changes, use 'NR_NumItems' in your formula instead - it'll automatically change as you add/remove items. The only thing you'll need to watch for is that you shouldn't have any blanks in the column, or the CountA part will come up with the wrong figure, so the range will be incorrect.

    I hope that helps.
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Registered User
    Join Date
    02-01-2017
    Location
    Eindhoven, Netherlands
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    4

    Re: Adapt formula for different array size each month

    Great, that worked! Thank you a lot!

    Additional question, I have adapted my formula and it works fine with that sheet. Now how can I replicate it the easiest way (again, without having to change the formula) so I can just copy these formulas and paste them into another sheet within the same excel file? When I do it now, I obviously get reference to the sheet I did it in.

    The case is that every month, I want a list of Top 5 items and the value related to this item. So I have the table in May, and now I just want to copy the table to June and I expect to receive the Top 5 items and the value. The table I have sorted out, now it's only about the replication of this to different sheets within the file.

    Let me know if I should explain better or more accurately.

    Thank you in advance!

  4. #4
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Adapt formula for different array size each month

    When you create a name, you can choose to have it apply to the whole workbook (the default) or just to the sheet you're on - that's the 'scope' of the Name (it's a drop-down option under where you enter the Name's name. The Name you've created will be a workbook Name, since that's the default. The bad news is that you can't change an already-existing Name's scope - you have to re-create the Name and select the scope when you define it.
    The good news is that if you have a Name with a sheet-only scope, if you copy that sheet, the Name gets copied with it, so any formulae using it will automatically then use the Name on that sheet, not the previous one.

    What I suggest you do is create a sheet for one month, which you can use as the basis for the rest. Re-do your name using the instructions above, but with the additional step of changing the Scope to 'Sheet1' or whatever your active sheet is called. Once you've got everything working as you want it, copy the sheet to use for the next month.

    Sorry about the extra work required. I hope that explains things and helps.

  5. #5
    Registered User
    Join Date
    02-01-2017
    Location
    Eindhoven, Netherlands
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    4

    Re: Adapt formula for different array size each month

    Great, it worked, thank you!

  6. #6
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Adapt formula for different array size each month

    Graag gedaan / you're welcome.

  7. #7
    Registered User
    Join Date
    02-01-2017
    Location
    Eindhoven, Netherlands
    MS-Off Ver
    MS Office Professional Plus 2013
    Posts
    4

    Re: Adapt formula for different array size each month

    Haha, dank je wel!

+ 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. Replies: 31
    Last Post: 11-09-2016, 05:00 AM
  2. [SOLVED] Making Size of Named Ranges Adapt
    By AliGW in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-24-2016, 07:54 PM
  3. [SOLVED] Adapt this array formula to sort if possible
    By sipa in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 11-03-2015, 06:50 PM
  4. Replies: 6
    Last Post: 07-13-2015, 12:35 PM
  5. [SOLVED] Excel 2003 Help with sumproduct formula to sort by month and size
    By cajodonn in forum Excel General
    Replies: 3
    Last Post: 06-25-2012, 09:50 AM
  6. Array formula help, counting size of a dataset!
    By ad9051 in forum Excel General
    Replies: 4
    Last Post: 12-09-2010, 10:06 AM
  7. spreadsheet to adapt to window size
    By wilwhiting in forum Excel General
    Replies: 5
    Last Post: 08-05-2008, 08:32 AM

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