+ Reply to Thread
Results 1 to 5 of 5

Avoid volatile formula

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    795

    Avoid volatile formula

    I am trying to read data into a front sheet from slave sheets depending on the User's selection. I want to avoid the use of =INDIRECT which, as I understand it, is volatile and therefore processor-hungry. I would also like to avoid using VBA if possible

    I have attached an example. Any help is much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,593

    Re: Avoid volatile formula

    Maybe in B5 coiped across and down

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


    But if this is a trivial example and your production workbook has dozens of sheets then that would be an inelegant solution.
    INDIRECT is processor hungry but it depends how much data you have. If this example data set is truly representative then I can't see it being a problem.

    A neater solution would be to change your data if its spread out across many sheets. Data is always best kept on a single sheet.
    In his case I'd have a new Data layout with columns for

    Current Tab Name - values would be A,B,C or their equivalent
    Whatever the current A:G values represent - values A,B,C,D...etc
    Whatever the current B4:I4 values represnet - values 1,2,3...8
    Value - i.e. the values currently at the intersections of the above
    Last edited by Richard Buttrey; 02-27-2020 at 01:38 PM.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,249

    Re: Avoid volatile formula

    BRISBANEBOB, only indirect allow to use dynamic sheet name. Your example is not so good or the solution is simple
    Please Login or Register  to view this content.
    OR
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by BMV; 02-27-2020 at 01:40 PM.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,794

    Re: Avoid volatile formula

    Are you only likely to have those 3 subsidiary sheets, or might there be more?

    You could do it with a multiple IF formula, i.e. IF($B$2="A", get the data from sheet A, IF($B$2="B", get the data from sheet B, get the data from sheet C))

    As the tables all follow the same format, you could simplify your INDEX formula by using ROWS() and COLUMNS() functions instead of MATCH.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    795

    Re: Avoid volatile formula

    There will only be 4 or 5 sheets but some will have 500 rows.

+ 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. How to avoid indirect volatile function
    By jaryszek in forum Excel General
    Replies: 19
    Last Post: 03-11-2019, 08:47 AM
  2. [SOLVED] Volatile Formula
    By AL1980 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-17-2017, 02:12 AM
  3. [SOLVED] Volatile Formula Query Help
    By BDBJ1 in forum Excel General
    Replies: 2
    Last Post: 01-27-2016, 11:26 AM
  4. Need to make a Formula non volatile
    By Modify_inc in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-02-2015, 02:31 PM
  5. [SOLVED] Non-volatile version of this formula?
    By Butcher1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-18-2014, 01:40 PM
  6. Formula without volatile formulation
    By BRISBANEBOB in forum Excel General
    Replies: 5
    Last Post: 05-12-2010, 01:28 AM
  7. [SOLVED] How to avoid volatile hyperlinks?
    By count in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2006, 12:06 PM

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