+ Reply to Thread
Results 1 to 9 of 9

Function to change formulas (choose different sheet through button)

  1. #1
    Registered User
    Join Date
    01-31-2015
    Location
    thailand
    MS-Off Ver
    win8, office2010
    Posts
    6

    Function to change formulas (choose different sheet through button)

    Hi there,

    I just discovered this Forum and so far I couldn't find any solution via the search. I have a problem that most likely should be easily solved but I am pretty new to Excel.

    Problem:

    I downloaded some household budget file and modified it to make it more detailed. Now the File looks like this:
    Sheet1: overview (gets all data from sheet 2)
    Sheet2: monthly expenses (manually input & gets data from sheet 3)
    Sheet3: daily expenses
    Sheet4&5, 6&7,... = next month

    The overview is currently showing the data for the first month, which works perfectly fine. However I would like to add some button or slider or anything similar that allows me to select another month (so instead of sheet 2, it should be sheet 4).

    Right now the overview sheet uses about 10 formulas like: =SUMIF(BudgetDetails[Category],[@Category],BudgetDetails[Projected Cost])
    I will rename Budgetdetails to "January" and the new function should simple be able to change the "budgetdetails" to "january" (and later February respectively, by a simple click or slide).

    Is there any easy solution in doing so? Button? Slider? Macro?
    Do you have any other ideas?

    I could upload the empty file if it's helpful (sorry for my English)

  2. #2
    Registered User
    Join Date
    01-31-2015
    Location
    thailand
    MS-Off Ver
    win8, office2010
    Posts
    6

    Re: Function to change formulas (choose different sheet through button)

    I've double checked because I wasn't used to the command used in the overview. Basically it's referred to a table and not even a sheet if I understand it correctly.

    So all I need is a Button/Slider/etc that allows me to change the name of the table for various fields at the same time from Jan_tab to Feb_tab etc

  3. #3
    Registered User
    Join Date
    01-31-2015
    Location
    thailand
    MS-Off Ver
    win8, office2010
    Posts
    6

    Re: Function to change formulas (choose different sheet through button)

    No ideas?

    As i said, I pretty much just need to change the name of a table with 1 click or swipe

  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
    44,064

    Re: Function to change formulas (choose different sheet through button)

    Yes - please upload a file.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    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

  5. #5
    Registered User
    Join Date
    01-31-2015
    Location
    thailand
    MS-Off Ver
    win8, office2010
    Posts
    6

    Re: Function to change formulas (choose different sheet through button)

    Sorry for the late response, I usually we be online on weekends

    The structure is original for Sheet 1,2,3. excelquestion.xlsx
    All I'd like is a quick option to change the input month (sheet2,4,6 etc) for the categories table and income in the overview page (sheet1)

    Any ideas? Else I will simply make one document for each month

  6. #6
    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
    52,939

    Re: Function to change formulas (choose different sheet through button)

    Here is a sample of what you need to do.

    I put January in G1 (you can put where you want, suggest make it a drop-down with all the month names)

    Then I changed the formula in E4 from this...
    =SUM(JanuaryTable[Projected Cost])
    to this...
    =SUM(INDIRECT($G$1&"Table[Projected Cost]"))
    Likewise, C4...
    =January!I1
    becomes...
    =INDIRECT($G$1&"!I1")
    By putting the month name into its own cell and referencing it with INDIRECT() you can now adjust the months.
    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

  7. #7
    Registered User
    Join Date
    01-31-2015
    Location
    thailand
    MS-Off Ver
    win8, office2010
    Posts
    6

    Re: Function to change formulas (choose different sheet through button)

    that worked great so far thanks, but I am having minor problems with this formula:

    =C7-SUM(JanuaryTable[Projected Cost])

    how can I use the G1 to replace the name of the Table?

    I thought: =C7-SUM(indirect(G1)&"Table")[Projected Cost]) should work, but it won't let me input the formula.

    Is there an option to replace the table name via the G1 field?

    Else I think I could work around by adding a "sum" field for the projected costs and use the exact formula as above, but I was wondering if there is a prettier way

    thanks

    EDIT
    got it... needs to be: =C7-SUM(INDIRECT($D$1&"Table[Projected Cost]"))
    Last edited by nakmuay; 02-14-2015 at 12:25 AM.

  8. #8
    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
    52,939

    Re: Function to change formulas (choose different sheet through button)

    Good job and thanks for the feedback

  9. #9
    Registered User
    Join Date
    01-31-2015
    Location
    thailand
    MS-Off Ver
    win8, office2010
    Posts
    6

    Re: Function to change formulas (choose different sheet through button)

    thanks a lot too, your post helped me to do a whole lot of modifications and improvements to my document and I got to understand some functions and tricks that will hopefully be helpful in future
    Much appreciated (:

+ 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. Two validations: first choose the sheet (1-5), then choose the option
    By bee88 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2014, 08:12 AM
  2. [SOLVED] UserForm change function of close (X) button
    By mkachwee in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-12-2012, 02:35 PM
  3. Replies: 2
    Last Post: 06-08-2012, 07:38 AM
  4. Button to change cell contents/formulas
    By abarham in forum Excel General
    Replies: 2
    Last Post: 03-05-2012, 11:46 AM
  5. [SOLVED] Change worksheet formulas from button on chart sheet
    By David in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-15-2005, 11:20 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1