+ Reply to Thread
Results 1 to 11 of 11

update an overview sheet using a command button

  1. #1
    Registered User
    Join Date
    11-25-2008
    Location
    Portsmouth,UK
    Posts
    6

    update an overview sheet using a command button

    Hi,
    New here so sorry if this is posted in the wrong area.

    I have a work book that consists of 15 sheets. Sheets 1-14 are for different stores yearly data and sheet 15 will be an overview of the each month but with all the stores on it.

    column B-M have the heading Jan-Dec and will have data in the rows below.

    What I want excel to do is by using a command button on sheet 15(Overview)
    update the overview sheet with the data that is in the column, corresponding month that is stated in cell A1 on sheet15 from each sheet of the work book.

    can anyone help?
    Last edited by VBA Noob; 11-26-2008 at 04:26 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    Take a look at the summary sheet example here

    http://www.excel-it.com/workbook_downloads.htm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    re: update an overview sheet using a command button

    johnpaulhenning,

    If all the sheets, sheet1 thru sheet14, are in that order, and are setup exactly the same, you could in sheet15, just use formulae.

    For example sheet15:
    = SUM(Sheet1:Sheet14!B5)
    will sum all the B5's

    or, just for a single value:
    =Sheet1!B5


    Have a great day,
    Stan
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  4. #4
    Registered User
    Join Date
    11-25-2008
    Location
    Portsmouth,UK
    Posts
    6
    Quote Originally Posted by stanleydgromjr View Post
    johnpaulhenning,

    If all the sheets, sheet1 thru sheet14, are in that order, and are setup exactly the same, you could in sheet15, just use formulae.

    For example sheet15:
    = SUM(Sheet1:Sheet14!B5)
    will sum all the B5's

    or, just for a single value:
    =Sheet1!B5


    Have a great day,
    Stan

    Stan,
    This is very close to what I want, however, what i need it to do is not total the sheets but in row4 user the sheet number has the column header prefixed in already will not change. the with the corisponding sheet display the data that is in that column like stated above. then when i press the command button it will link the month that will be stated in cell A1 on sheet 15, with the corresponding months data from each sheet.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    An example would help, but I'm sure that you can do this with formulas.

  6. #6
    Registered User
    Join Date
    11-25-2008
    Location
    Portsmouth,UK
    Posts
    6
    Am I able to attach screen dump?

  7. #7
    Registered User
    Join Date
    11-25-2008
    Location
    Portsmouth,UK
    Posts
    6
    My overview sheet looks like this but will go up to sheet 14,

    Month
    Have a command button here

    Sheet1 Sheet2 Sheet3 Sheet4 Sheet5
    Profit
    Sales
    GC's
    DT Growth
    Burgo Variance %
    Burgo Variance £
    P&L Reconciliation
    Base-to-Actual %
    Base-to-Actual £
    Labour vs. Plan
    Weekly Work Accuracy/ Completion

    Does this help in any way, then whatever is in the same month cell the data in that column will be displayed under the relevant column

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    You can attach a workbook-

    Post Reply>- scroll down>- Manage Attachments

  9. #9
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    re: update an overview sheet using a command button

    johnpaulhenning,

    Please post your workbook - see "Manage Attachments".


    Have a great day,
    Stan

  10. #10
    Registered User
    Join Date
    11-25-2008
    Location
    Portsmouth,UK
    Posts
    6
    Hi,
    I have uploaded the attachment, Hope this makes it a little clearer to what I'm trying to do.

    Thank you for your time
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    re: update an overview sheet using a command button

    johnpaulhenning,

    Here you go.

    See the attached workbook "Data Validation to update an overview sheet using a command button - johnpaulhenning.xls" with your command button coded.

    Enter some data on the sheets, and click on the command button "Update Overview".


    Have a great day,
    Stan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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