update an overview sheet using a command button

**johnpaulhenning** · 11-25-2008, 10:41 AM

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?

**royUK** · 11-25-2008, 10:47 AM

Take a look at the summary sheet example here

http://www.excel-it.com/workbook_downloads.htm

**stanleydgromjr** · 11-25-2008, 10:52 AM

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

**johnpaulhenning** · 11-25-2008, 11:03 AM

Originally Posted by stanleydgromjr

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.

**royUK** · 11-25-2008, 11:15 AM

An example would help, but I'm sure that you can do this with formulas.

**johnpaulhenning** · 11-25-2008, 11:20 AM

Am I able to attach screen dump?

**johnpaulhenning** · 11-25-2008, 11:41 AM

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

**royUK** · 11-25-2008, 12:25 PM

You can attach a workbook-

Post Reply>- scroll down>- Manage Attachments

**stanleydgromjr** · 11-25-2008, 03:52 PM

johnpaulhenning,

Please post your workbook - see "Manage Attachments".

Have a great day,
Stan

**johnpaulhenning** · 11-26-2008, 05:18 AM

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

**stanleydgromjr** · 11-26-2008, 04:25 PM

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