+ Reply to Thread
Results 1 to 12 of 12

Alternative to the indirect function

  1. #1
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Alternative to the indirect function

    I have a workbook that is updated regularly through out the day. I want to build a quick dashboard that would allow an individual to access information off of that workbook. The dashboard has to be dynamic so that they can look at particular days as well as showing real time progress during the current day. The workbook that is being updated is stored on a share network drive so that multiple people can access it through out the day.

    I have tried to use the indirect function to link the dashboard to the other workbook and It works just fine as long as that work book is open on the computer also using the dashboard. As soon as you shut down the workbook with the data the dashboard can no longer display any information and has multiple #REF! errors.

    Is there a function that I can use instead of indirect to access information from a possible closed workbook on a different computer?

    I am including some sample books for reference.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Alternative to the indirect function

    This is a perennial problem. INDIRECT won't work. In the real world...

    How many closed workbooks do you need to reference?

    How many cells in each closed workbook do you need to reference?

    Are they contiguous?

    There is a workaround but it is ABSOLUTELY dependent on the SCALE of what you are trying to do. More info, please...
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Re: Alternative to the indirect function

    Quote Originally Posted by Glenn Kennedy View Post
    This is a perennial problem. INDIRECT won't work. In the real world...

    How many closed workbooks do you need to reference?

    How many cells in each closed workbook do you need to reference?

    Are they contiguous?

    There is a workaround but it is ABSOLUTELY dependent on the SCALE of what you are trying to do. More info, please...
    There is one workbook and it might be open on one computer but not always the computer with the dashboard. Think of it like your neighbor is working on it at his house and you want to see what is going on in yours.
    there will be one workbook per month with tabs for every day of the month. So today the dashboard would look at March 18th and tomorrow it would switch to March 19th and so on. The other issue is the the target cells on the tab we are looking at will not always be in the same location that they were yesterday based on the amount of work being done. Today it is on G30 and tomorrow it will be on G51 since that day might be more busy.

    I thought about trying to do a form of index match but is still have to be able to change the target tab based on the day.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Alternative to the indirect function

    ... G30 today, G51 tomorrow.... will the rest of the sheet (G31 downwards... and G52, downwards...) be EMPTY?

  5. #5
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Re: Alternative to the indirect function

    Quote Originally Posted by Glenn Kennedy View Post
    ... G30 today, G51 tomorrow.... will the rest of the sheet (G31 downwards... and G52, downwards...) be EMPTY?
    usually yes. unless some yahoo decides to make some notes or something further down. If I use a range of G3:G100 I would usually be ok. I used a lookup function to pull the last non empty cell in a column and get the number, but making it dynamic so it looks at the right tab depending on the day is problematic.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Alternative to the indirect function

    The approach that i ahd in mind won't be practical for 31 sheets... HOWEVER, a sensible workaround would be to use LOOKUP to return the last value from each day to a master sheet (2 columns... date & last value, and - of course - it could be hidden). Pulling THAT info over from a closed sheet is then easy. Would that work??

  7. #7
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Re: Alternative to the indirect function

    Quote Originally Posted by Glenn Kennedy View Post
    The approach that i ahd in mind won't be practical for 31 sheets... HOWEVER, a sensible workaround would be to use LOOKUP to return the last value from each day to a master sheet (2 columns... date & last value, and - of course - it could be hidden). Pulling THAT info over from a closed sheet is then easy. Would that work??
    Not really, the dashboard needs to be active in real time so they can display it on a tv screen to show hourly progress. I need it to be dynamic so no one (including me) is tethered to the thing all day having to update. Getting these people to do anything ( especially computer and spreadsheet related) is daunting.

    If it was just on that one computer it wouldn't be that difficult, but the big boss wants to be able to see "At a glance" what is going on no matter where he is. But, it just might not be able to be done.

  8. #8
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Re: Alternative to the indirect function

    This is what I might do, I will add the dashboard to the report itself and then create another as a stand alone workbook that references the first dashboard. I could set up index match of some kind with helper cells to make the satellite dashboard kind of dynamic.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Alternative to the indirect function

    ?? I don't understand your reply... the master sheet would be in your (closed) raw data sheet.

    Is your (closed) raw data sheeet being updated even when it is closed???!!!

  10. #10
    Forum Contributor
    Join Date
    04-04-2016
    Location
    dallas tx
    MS-Off Ver
    365 (15.0.5501.1000)
    Posts
    534

    Re: Alternative to the indirect function

    Quote Originally Posted by Glenn Kennedy View Post
    ?? I don't understand your reply... the master sheet would be in your (closed) raw data sheet.

    Is your (closed) raw data sheeet being updated even when it is closed???!!!
    No, sorry for the confusion. The workbook is open most of the time but only on one computer that is in the shipping office. The General Manager wanted to make a dashboard so he could quickly check the status of how many units had been picked for the day so far without having to open the master workbook. Originally the dashboard would not have been "connected" to the master workbook but serve as a window to the master to check progress. The issue with using indirect was that since the dashboard and the master workbook will not always be open on the same computer at the same time, the formulas do not work and only display a reference error.

    My thought was to put the "master dashboard" in the master workbook and have a copy of it on the GM's computer. I can link to the static cell on the master dashboard with index for the copy dashboard. Every time he will open it, it will display the updated information without indirect links.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Alternative to the indirect function

    Take a look. Download both - for now to the same place. Open Book 1 & change the date (only bewtwwen 1st and 4th... as that's all I did).
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Alternative to the indirect function

    imo, in this case, create a duplicate data from network drive might be the best solution.
    to do this, open both file. in dashboard file create a blank sheet then
    just use = and navigate to the other file, choose top left cell , press enter.
    after that, try to remove $ from the formula and copy across to make sure it's big engouh.

    Regards.

+ 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. VBA Alternative: INDIRECT created on Mac does not function the same in Windows
    By wee_wee in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 07-07-2020, 11:05 PM
  2. Alternative to INDIRECT function?
    By ACORAM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2019, 03:20 PM
  3. Replies: 4
    Last Post: 11-10-2017, 10:22 AM
  4. Alternative of INDIRECT function on CLOSED WORKBOOK - No VBA
    By dluhut in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-13-2017, 10:38 AM
  5. Alternative to Indirect()
    By erwina in forum Excel General
    Replies: 3
    Last Post: 09-04-2010, 08:06 PM
  6. Alternative for INDIRECT
    By Hein in forum Excel General
    Replies: 11
    Last Post: 01-27-2009, 05:42 AM
  7. Alternative To INDIRECT?
    By SamuelT in forum Excel General
    Replies: 3
    Last Post: 11-20-2008, 03:53 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