+ Reply to Thread
Results 1 to 6 of 6

Formula that updates spreadsheet when I change the dates

  1. 06-22-2015, 04:00 PM #1
    chris004
    chris004 is offline
    Registered User
    Join Date
    06-22-2015
    Location
    Michigan
    MS-Off Ver
    2007
    Posts
    4

    Formula that updates spreadsheet when I change the dates

    I'm a newbie here so I will try my best to explain my question.

    I have a spreadsheet full of data that I do my monthly reports with.. On one sheet is the data by month, on the other is the dashboard of charts. Right now all of the charts reference the data from the other sheet and each month I have to go in the charts and select data and move it over to the current month. What i'm looking for is a formula that will allow me to go on the page with the charts and change the month, then all of the data changes to the current month.

    Not sure if this makes any sense or not

    let me know

    Thanks
    Reply With QuoteRegister To Reply
  2. 06-22-2015, 04:02 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Formula that updates spreadsheet when I change the dates

    Hi, welcome to the forum

    Makes perfect sense However, to be able to offer specific suggestions, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 06-22-2015, 04:08 PM #3
    chris004
    chris004 is offline
    Registered User
    Join Date
    06-22-2015
    Location
    Michigan
    MS-Off Ver
    2007
    Posts
    4

    Re: Formula that updates spreadsheet when I change the dates

    Thanks for the super quick response. Let me work on that.
    Reply With QuoteRegister To Reply
  4. 06-22-2015, 04:55 PM #4
    chris004
    chris004 is offline
    Registered User
    Join Date
    06-22-2015
    Location
    Michigan
    MS-Off Ver
    2007
    Posts
    4

    Re: Formula that updates spreadsheet when I change the dates

    Excuse the dash board.. I had to remove a few things because it was for work.. But as you can see I have a bunch of text boxes linking to another page in the document. As of now, every month I would have to go in and change the reference for each text box and each graph.. In a perfect work I want to go to the data page, and put all my raw data in there, change the month on the dashboard page and have the graphs and text boxes update
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-23-2015, 12:39 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,047

    Re: Formula that updates spreadsheet when I change the dates

    very impressive dashboard, great job!!

    On your data sheet (references), I would suggest that you modify the layout so it becomes a standard 2D (regular) table, with few blank rows.

    This is a data entry sheet, not a presentation sheet, so it does not need to look pretty...you only need the dates in 1 row, not for every category (thats kinda redundant)
    Keep the table ALL going the same way. For instance in rows 10:13, you have...
    C
    D
    E
    10
    		 Visits Unique Users
    11
    		 March
    20,544
    15,527
    12
    		 April
    21,593
    16,568
    13
    		 May
    22,979
    18,532

    change that so the info flows the same as the rest of the table.

    OK, to your question.

    I put a date in Z2, then used this...
    =INDIRECT("References!"&CHAR(66+Z3)&3)
    This is pretty much allowing "C" (the column number) to change based on the date. You will need to use that in each of your text boxes (sorry).

    I tried to use INDEX/MATCH, but could not get the row ID from the text box ...
    =index(References!$C$3:$J$105,match(?????
    Reply With QuoteRegister To Reply
  6. 06-23-2015, 09:38 AM #6
    chris004
    chris004 is offline
    Registered User
    Join Date
    06-22-2015
    Location
    Michigan
    MS-Off Ver
    2007
    Posts
    4

    Re: Formula that updates spreadsheet when I change the dates

    So it looks like we are able to set up the charts to automatically update.. But the text boxes will still be a manual thing ?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 format spreadsheet to change color after x dates in Excell 2007
    By Cholo9 in forum Excel General
    Replies: 11
    Last Post: 01-24-2013, 05:49 PM
  2. How to make copies of a spreadsheet, within a book, where the dates change automatically
    By jaherlihy in forum Excel General
    Replies: 4
    Last Post: 11-28-2012, 11:44 AM
  3. Copying updates from one spreadsheet into a shared spreadsheet?
    By Stu1961 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-02-2010, 11:22 AM
  4. spreadsheet updates
    By baz0912 in forum Excel General
    Replies: 3
    Last Post: 03-10-2009, 04:58 AM
  5. [SOLVED] dates change when copied form 1 spreadsheet to another
    By sheehanb in forum Excel General
    Replies: 1
    Last Post: 05-11-2006, 10:10 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1