+ Reply to Thread
Results 1 to 5 of 5

Combining INDEX & INDIRECT Functions

  1. #1
    Registered User
    Join Date
    04-23-2019
    Location
    Glasgow, Uk
    MS-Off Ver
    Office 2016
    Posts
    3

    Combining INDEX & INDIRECT Functions

    Hi all

    I've built a nice looking "Front End" worksheet that should hopefully read from weekly reports downloaded to a shared folder.

    As there's a report for each week I wanted to have a drop down list which let you select which week you wanted to look at, which would then be used as part of an INDIRECT function to show the relevant data. I've managed to do this, but it requires all the workbooks you're referring to to be open, which is cumbersome.

    Currently

    Cell B1 contains the Drop Down Menu with Week Number

    =INDIRECT("'[BOL Week " & $B$1 & ".xls]Sheet 1'!A" &ROW(),TRUE) - this works to refer to the correct cells in the open Workbook.

    =INDEX('C:\Users\FlashGordy\Shared Folder\[BOL WEEK 16.xls]Sheet 1'!A:A,ROW(),1) - this works to refer to the correct cells in a closed workbook but doesn't use the drop down week number to update the page for different weeks.

    I thought

    =INDEX(Indirect("'C:\Users\FlashGordy\Shared Folder\[BOL WEEK " & $B$1 ".xls]Sheet 1'!A:A,ROW(),1)"))

    Would work but excel says it's not a valid formula.

    I'm pretty stuck - any help would be appreciated.

    Thanks

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Combining INDEX & INDIRECT Functions

    I'm afraid you cannot use INDIRECT with closed workbooks, as you have discovered.

    edit: if you "google" (other SE are available) INDIRECT with Closed Workbook you will find various discussions on supposed workarounds.

  3. #3
    Registered User
    Join Date
    04-23-2019
    Location
    Glasgow, Uk
    MS-Off Ver
    Office 2016
    Posts
    3

    Re: Combining INDEX & INDIRECT Functions

    Thanks Xlent

    Is there no way to create an INDEX string in the current workbook with a variable file destination based upon a cell value in the current workbook?

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Combining INDEX & INDIRECT Functions

    No, I'm afraid not -- INDIRECT is used to effectively cast the string to a range -- and to incorporate the variant filename you need a dynamic string in first instance.

    As I mentioned, there are lots of alternatives you could consider - which is most appropriate for you will depend on many factors, e.g.:

    - number of files you need to accommodate
    - feasibility of using VBA
    - feasibility of using 3rd party utilities etc...

  5. #5
    Registered User
    Join Date
    04-23-2019
    Location
    Glasgow, Uk
    MS-Off Ver
    Office 2016
    Posts
    3

    Re: Combining INDEX & INDIRECT Functions

    Thanks

    I've found a work around but it's not great.

    I've created 52 tabs named 'Week n', one for each weekly file and they reference all the relevant cells on the weekly report files stored on the network folder. These populate even if the files are closed, but I have access to the network folder.

    I can then use INDIRECT with the drop down menu to lookup the relevant week's information on the sheet and display on the front page.

    Other solutions would be welcomed.

    Ideally would need to work with an infinite number of files - at least 52 a year.
    VBA - I have used it, but not for a while. I'm self taught so would need to go back and relearn a lot.
    3rd Party Utilities are probably out - unless they're free.

+ 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. combining 'indirect' function with 'index/match"
    By wongth7 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-20-2015, 09:38 AM
  2. [SOLVED] Combining IF, OFFSET and INDIRECT functions.
    By dowell89 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-30-2013, 05:50 AM
  3. [SOLVED] Combining OFFSET, Indirect and IF functions
    By dowell89 in forum Excel General
    Replies: 3
    Last Post: 10-25-2013, 06:20 AM
  4. Where do we combine INDEX and INDIRECT functions?
    By BIJALRADIA in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-01-2013, 05:48 AM
  5. Use INDEX and INDIRECT functions combined.
    By maancalo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-11-2012, 02:43 PM
  6. Combining INDEX functions
    By wilsoa in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-26-2012, 03:07 PM
  7. Using Index, Indirect and Rand functions.
    By bolger in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-05-2007, 06:09 PM

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