Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MACRO - Change the value of a cell with a MACRO (001)

    Hi, this is the first dilemma I've posted here, thanks for checking it out.

    Currently I have to constantly flick back and forth from my 'Input Sheet' (where the selections are made) to my analysis sheets, that is, 'Fin Summary' and 'Breakdown' (where the results are displayed).

    What I'd like to achieve is to make a selection from a drop down list displayed in each of the analysis sheets (preferably on the toolbar, much like 'Font Size') and have a macro change the relevant cell in the 'Input Sheet'. There are two separate choices, one for 'Period' (1 through 12 inclusive), which is cell C2 in the 'Input Sheet' and 'Load' (Actual or at 35.85% of Labour), which is cell C5 in the 'Input Sheet', thus I think I need two macros.

    Points of Note:
    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO - Change the value of a cell with a MACRO (001)

    Welcome to Woody's Lounge!

    I'm afraid it's impossible to provide help based on the workbook you attached. It doesn't contain any formulas, so we have no way of knowing what the result of changing any of the values will have. Could you attach a more relevant workbook and explain more clearly what you want to accomplish?

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MACRO - Change the value of a cell with a MACRO (001)

    G'day Hans, thanks so much for your time. Hopefully I can clarify things a bit.

    Let's just focus on getting one macro to work and if I can achieve that, copying it and using it to perform the same action should be relatively straight forward.

    I've put some very rudimentry raw data in another sheet in the attached file and have entered some formulas in the 'Input Sheet' and 'Fin Summary' sheets to make use of the cell C2 in the 'Input Sheet'. This is the cell I want the macro to update with values 1 through 12 inclusive as selected by the user at any point in time.

    Then, once we have that working, it would be really cool if it could be smartened up so that the macro is on the tool bar so I'm able to select the 'Period' I want and see the results change right before my eyes.

    I hope I'm not asking too much from Excel.

    Cheers,

    Al

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: MACRO - Change the value of a cell with a MACRO (001)

    Don't be scared off by the questions, but to expand on Hans comments: only the source for the Month-to-Date analysis is clear - the remaining detail numbers, in the Quarter 1 Analysis, Year-to-Date, and Forecast sections, do not tie to anything. Also, Loungers can intuit certain sum formulas in the Breakdown worksheet, and that the formula in Cell C20 of the Breakdown worksheet will be =C19*'Input Sheet'!C6, copied to the other numeric cells in that row, but the source of the rest of the data is not apparent. Further, there is nothing to indicate what value Load takes when it is "Actual", as opposed to 35.85% of Labour, and the worksheets do not indicate where that value should be used. So it would be much more useful if you provided a sheet with formulas and inter-sheet formula links. I have engineered formulas from my best guess of the relationships in this version of the attachment - complete what is missing and then help us understand your objective better. (I didn't agree with your formatting in L20:L21.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    New Lounger
    Join Date
    Nov 2006
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MACRO - Change the value of a cell with a MACRO (001)

    Hey John,

    Thanks for you time. Hopefully my last post will suffice. I couldn't attached my file spreadsheet because it was too large with all the pivot tables etc used to update the information in the analysis sheets after changing the value in cell C2 in the 'Input Sheet' sheet.

    If you need more from me after reviewing my latest attachment just let me know.

    Cheers,

    Al

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MACRO - Change the value of a cell with a MACRO (001)

    You probably don't need macros. In the attached workbook, I have placed a combo box (dropdown list) from the Forms toolbar on the Fin Summary sheet. Right click it and select Format Control to see its properties. The combo box takes its input from the list of periods, and it is linked to cell C2 on the Input Sheet. Selecting a period in the dropdown list will change the value of this cell, and hence the values on Fin Summary.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: MACRO - Change the value of a cell with a MACRO (001)

    ... or (once again following Hans) possibly Data Validation.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Nov 2006
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MACRO - Change the value of a cell with a MACRO (001)

    The combo box works fantastically as I can copy it into numerous sheets within the file and when i change in one sheet it changes it in all the other ones too.

    This is just brilliant. Thanks guys.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •