+ Reply to Thread
Results 1 to 7 of 7

Is there a better way than "indirect"

  1. #1
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    167

    Is there a better way than "indirect"

    My file has 90+ tabs and the MasterList uses "indirect" several times per each row for 90+ rows, is there a better way to do this?

    Row Example
    B10
    Please Login or Register  to view this content.
    C10
    Please Login or Register  to view this content.
    D10
    Please Login or Register  to view this content.
    E10
    Please Login or Register  to view this content.
    F10
    Please Login or Register  to view this content.
    J10
    Please Login or Register  to view this content.
    -Travis
    Mac OS 12.1
    Excel 365 (v16.59)

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,676

    Re: Is there a better way than "indirect"

    Personally, I would have probably three sheets. The Master sheet would have the static information for ALL vehicles, much as now. Then ONE Transaction sheet for ALL vehicles. You'd need to add a key field column at the beginning of each record with the Vehicle ID number. Finally, the third sheet would be an Enquiry sheet. You could have a drop down list with a unique list of the vehicle IDs (from the Master list). You could populate the static information from the Master list using VLOOKUP or INDEX/MATCH. And, as you are using Office 365, you could collate the service and maintenance records from the Transaction list using FILTER and SORT, possibly UNIQUE.

    There would be no need for dozens, if not hundreds of (similarly structured) sheets which are, inevitably, difficult to maintain and gather data from.

    With all the transaction data in one sheet, you could sort and filter it, and you could use Pivot Tables and Charts to analyse the records.

    You could also add extra columns to flag specific types of transaction (which would be easier to analyse than free format descriptions. A little more work but useful if you need to see which vehicles have had specific actions.

    All food for thought.

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,103

    Re: Is there a better way than "indirect"

    One alternative is to use VBA to build each formula directly every time a new row is added, but I think the solution you have is better for the amount of data you have. VBA would make sense only if the calculation performance of what you have is too slow. Why are you looking for a different way?

    This code would go into the module for Sheet1 (Master List) [tested in your file]
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,103

    Re: Is there a better way than "indirect"

    By the way the truck numbers in column A do not require an apostrophe.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    36,676

    Re: Is there a better way than "indirect"

    Adding VBA introduces another, possibly, unnecessary learning curve. Users will need to enable macros for the code to work. Could be a whole world of pain for inexperienced users.

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,103

    Re: Is there a better way than "indirect"

    I agree, which is why I thought he already had the best solution. I would go to VBA only if the current solution is unusable for some reason.

  7. #7
    Forum Contributor
    Join Date
    10-11-2007
    Location
    St Louis MO
    MS-Off Ver
    365 (v16.53) for Mac
    Posts
    167

    Re: Is there a better way than "indirect"

    OK thanks, just asking.

+ 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. help with =SUMPRODUCT(COUNTIF(INDIRECT("'"&A25:A31&"'!L7:L30"),"<60")
    By pandakor in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-06-2020, 09:10 AM
  2. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  3. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  4. Replies: 2
    Last Post: 11-16-2015, 04:45 PM
  5. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  6. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  7. Replies: 4
    Last Post: 10-29-2009, 12:08 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