+ Reply to Thread
Results 1 to 15 of 15

Offset to auto-update dashboard?

  1. #1
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Offset to auto-update dashboard?

    Unfortunately I can't share an example sheet, but picture this:

    Sheet 1 has a dashboard showing cost savings. I want cell B2 here to always show the current cost savings for this period.

    Sheet 2 is a data sheet which has dates (months) in row 1. Cell A2 has our first month's cost savings, and cell B2 has our second month's cost savings. Column C, so in this case C2, containts averages for the various items we're tracking.

    As more months roll in, I'm adding more columns between (in this case) column C on sheet 2. I've been manually updating these on the dashboard, but we're starting to track too many items to make this update efficient.

    I need help having cell B2 on Sheet 1 show the values in the new cells in the new column on sheet 2 as I insert them. I want B2 to always show the most current (-1 column from the averages column currently occupying C) value.

    Is this possible? I've read a little on OFFSET mixed with COUNTA, but I can't seem to make it work - I can add one column and it'll set up, but I can't get it to work when I continue to add columns.

    Thank you!
    Last edited by coryjacques; 04-19-2019 at 10:35 AM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Offset to auto-update dashboard?

    Without a sample file it's difficult to envisage, but here are a few thoughts/ideas on it.

    Do you have to have the average column as the next column after the most current month? What if you put it to the left of the month columns? That way you wouldn't need to amend the dashboard each time you add a new month. You could then have the dashboard look up the MAX month in row 1 on sheet2 and look up the values from there. Or have a month selector on the dashboard that would allow you to change which month's figures are looked up.

    BSB

  3. #3
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Re: Offset to auto-update dashboard?

    That's a great idea - I can definitely move the averages column to the left. I hadn't considered that. How would you recommend getting the dashboard to continuously update if I did that?

    Thank you

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Offset to auto-update dashboard?

    Quote Originally Posted by coryjacques View Post
    How would you recommend getting the dashboard to continuously update if I did that?
    This would all depend on what needs updating and where from... Which bit are we talking about here? The months that are looked up or the average itself?

    BSB

  5. #5
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Re: Offset to auto-update dashboard?

    Using the actual cells I'm working with as an example,

    Sheet 1: Dashboard
    Sheet 2: Finances

    Dashboard sheet: M3="Total Cash on Hand" ; M4 targets what's currently in Finances!B40

    Finances Sheet: B40 contains the most up-to-date calculation of cash on hand for this current period. Next period, I'll add the newest update to cell B41. The period after that it will be in B42, and so on, so I'm maintaining history.

    Right now what I've been doing is just modifying the calculation in Dashboard!M4 to reflect [=Finances!b40], updating it to [=Finances!B41], [=Finances!B42], etc., for each new period. If there's any way to automate this it would make life much easier since I have about 12 of these categories that I manually update. They're simple to update manually, but it would save a ton of time given the work needed to do on the Finances page alone prior to dashboard update.

    Does that make sense?

  6. #6
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Offset to auto-update dashboard?

    Perhaps you could benefit from using lookup formulas like VLOOKUP or INDEX/MATCH.

    Does your dashboard have the period it covers listed anywhere?

    If you had a cell with Apr-19 in it that you'd change to May-19 for next month then you can use that cell in a lookup formula. So you tag your figures for the next month into column B on the Finances sheet and the lookup formula will pull back the relevant one without you needing to change the formulas each time.

    If you like I'll put together a quick dummy dashboard as an example...

    BSB

  7. #7
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Re: Offset to auto-update dashboard?

    No thanks, I'm looking for a way to completely automate this (meaning I wouldn't need to update the date on the dashboard sheet) which doesn't appear possible. I'm going to rework this and see if there's another way.

  8. #8
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Offset to auto-update dashboard?

    You could have a cell with the formula =EOMONTH(TODAY(),-1)+1 that would always calculate as the first day of the current month. Format it as Mmm-YYYY so you don't see the day element then you can use that in a lookup. It will update automatically whenever the file is opened.

    Or if you need the report to be static even if opened in subsequent months you could use VBA to create it.

    There are many options open to you here.

    BSB

  9. #9
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Offset to auto-update dashboard?

    Maybe this is the sort of thing you're looking for.

    The list of months and figures on the left is the equivalent of your Finances sheet. The green cells would be on the dashboard sheet.

    If you add a figure to the table for May you'll see the green cells, both date and value, update automatically.

    BSB
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Re: Offset to auto-update dashboard?

    I ended up using =OFFSET(Finances!$B40,0,COUNTA(Finances!$C40:$ZZ40),1,1). This seems to do the trick

  11. #11
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Offset to auto-update dashboard?

    Be wary of OFFSET as it's one of the 'volatile functions' which means it recalculates at every change/recalculation and not just when it needs to.
    Excessive use of volatile functions can cause slow down issues on large workbooks.

    BSB

  12. #12
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Re: Offset to auto-update dashboard?

    Thank you - I'm using it a few times here, but none are nested and the other functions I'm using are all pretty basic.

  13. #13
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Offset to auto-update dashboard?

    The INDEX/COUNTA in my attachment does exactly the same (although needs adjusting to look at only the required range) as your Offset formula but without the volatility.

    BSB

  14. #14
    Registered User
    Join Date
    07-12-2016
    Location
    massachusetts
    MS-Off Ver
    excel 2010
    Posts
    44

    Re: Offset to auto-update dashboard?

    Thank you - let me mess around with it. I might be able to work yours in to some issues I'm having nesting offset. Much appreciated

  15. #15
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Offset to auto-update dashboard?

    No probs. Happy to help.

    BSB

+ 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 Dashboard: Auto update data every minute
    By Sharonjit in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 12-20-2018, 08:35 AM
  2. [SOLVED] how to unlock dashboard chart & update new data
    By raysrains in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-11-2015, 09:25 AM
  3. Dashboard update
    By gilesm in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-19-2013, 11:56 AM
  4. Macro to update Dashboard
    By Biancabbb in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-04-2013, 02:19 PM
  5. Update Diffrent worksheets from Dashboard
    By v2jtb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-03-2010, 04:18 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