+ Reply to Thread
Results 1 to 9 of 9

Alternative to Indirect variable worksheets

  1. #1
    Registered User
    Join Date
    10-25-2017
    Location
    London, UK
    MS-Off Ver
    MS 2010
    Posts
    14

    Alternative to Indirect variable worksheets

    Hi

    I have tried googling and I havent been able to find anything useful so hoping someone can help. I have attached a subset of the data but to give you an idea the actual file has approx. 46 categories across columns and 280 tiers along rows. In my subset, I have shown 3 columns 'cat 1, 2, 3' and 6 tiers.

    I receive the data monthly in the same format and ideally what I want to create is a summary tab that uses sumif to sum all the data for relevant categories only to create a rolling monthly view by automatically knowing the correct monthly worksheet. Exactly like the attached but not using indirect because with the amount of data, it takes too long to calculate.

    Alternatives please, vba welcome

    cheers,
    Dipi
    Attached Files Attached Files

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Alternative to Indirect variable worksheets

    You can use offset function with Indirect. Offset will automatic consider the range and right now you are using whole range like A:A

    Change as below.

    E12
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See if it work

    You will just need to change highlighted part above A1 Cat1, B1 Cat 2 & C1 Cat3
    Last edited by shukla.ankur281190; 11-01-2017 at 07:42 AM.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Registered User
    Join Date
    10-25-2017
    Location
    London, UK
    MS-Off Ver
    MS 2010
    Posts
    14

    Re: Alternative to Indirect variable worksheets

    Hi thank you for that, it works but it still takes long to calculate. Is there any other options without using volatile functions?

  4. #4
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Alternative to Indirect variable worksheets

    ok how much rows maximum can be in your sheet? I can almost more short this formula for ignoring COUNTA A:A range. But I first let us know how much size is your workbook.

    May be VBA would be better solution

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Alternative to Indirect variable worksheets

    If you want the "flexibility" of dynamically assigning sheet names then INDIRECT is the only option using formulae.

    How many months do have in your report: is it always 3? Is report format exactly as your sample? Need these details if VBA is an option

  6. #6
    Registered User
    Join Date
    10-25-2017
    Location
    London, UK
    MS-Off Ver
    MS 2010
    Posts
    14

    Re: Alternative to Indirect variable worksheets

    The number of tiers currently on the summary page is 280 and in the individual monthly worksheets the current maximum number of tier transactions is 1300. The total number of tiers shouldn't exceed more than 400 but the tier transactions can grow to about 5000 but that's just a guess.

    Do you know a VBA code that can do this? i'm not bad at modifying code but I am not confident to write it from scratch.

  7. #7
    Registered User
    Join Date
    10-25-2017
    Location
    London, UK
    MS-Off Ver
    MS 2010
    Posts
    14

    Re: Alternative to Indirect variable worksheets

    It will always be maximum 24 months, 1 previous financial year and 1 current financial year.

  8. #8
    Registered User
    Join Date
    10-25-2017
    Location
    London, UK
    MS-Off Ver
    MS 2010
    Posts
    14

    Re: Alternative to Indirect variable worksheets

    Attached exact format of file, I have removed the data though.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Alternative to Indirect variable worksheets

    How do you want the report organised?

    For example, for category, we could allocate 24 columns (one for each year) and then "fill in the blanks": so as a new year is added, we calculate for that year only.

    if the number of categories (and tiers) is dynamic, we need a method of knowing the maximum number of categories (and tiers) at any point in time: I assume there may be years where a category and or tier may not be present, so the report itself will to be "dynamic".

    You need to mock up a file showing the exact layout you require.

    And I suggest you move the post to the VBA/Macro forum.
    Last edited by JohnTopley; 11-01-2017 at 10:12 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. Alternative to Indirect when linking to worksheets which change names
    By Nik21 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-13-2015, 07:13 AM
  2. [SOLVED] INDIRECT or alternative?
    By ungers in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-01-2014, 08:33 AM
  3. Indirect Alternative
    By par0016 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-07-2013, 02:45 AM
  4. Alternative to Indirect()
    By erwina in forum Excel General
    Replies: 3
    Last Post: 09-04-2010, 08:06 PM
  5. Alternative for INDIRECT
    By Hein in forum Excel General
    Replies: 11
    Last Post: 01-27-2009, 05:42 AM
  6. Alternative To INDIRECT?
    By SamuelT in forum Excel General
    Replies: 3
    Last Post: 11-20-2008, 03:53 PM
  7. [SOLVED] Alternative to Indirect
    By [email protected] in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-20-2006, 07:35 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