Question

Locked

Adding Values from several worksheets within 1 workbook - Excel 2010

By ecntrc ·
I have a user that is responsible for ordering uniforms for our sales employees. She has a workbook in Excel 2010 with about 60 worksheets, she would like to create a formula that will tell her how many Smalls, Medium, Large, XL ect to order within the WHOLE workbook. We have tried " =countif('worksheetname'!cell range) we've multiplied that formula with each store's information but of course were doing something wrong. Im no expert at Excel, I know the basics but this is a little out of my league as I am good with all other Office products except Excel which is why I am asking for some assistance. I have googled but couldn't come up with anything

Could anyone help? Please let me know if you need further information. I appreciate your help, suggestions, comments ect.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

might be time to use msaccess as a report generator

by databaseben In reply to Adding Values from severa ...

sometimes, spreadsheets are created without some planning and after many years its all a mish mash of data, like mash potatoes.

what i would suggest is instead of creating one more worksheet, you guys should use the power of MSAccess database.

msaccess can link to those active worksheets, manipulate the data and produce information that can be used.

Collapse -

Reponse To Answer

by ecntrc In reply to might be time to use msac ...

That sounds like a good idea, I know the basics and some advanced skills of Access so I will try that out. But if anyone else has any other suggestions please let me know.

Thanks Ben

Collapse -

Try This

by dogknees In reply to Adding Values from severa ...

The syntax for referring to a range on multiple sheets is as follows.

=SUM(Sheet1:Sheet10!A4:G12)

This will add up the range A4:G12 on all sheets from Sheet1 to Sheet10. Substitute the names of the first and last sheet in your workbook.

Regards

Collapse -

Reponse To Answer

by hankster806 In reply to Try This

Hi dogknees
How do I use your formula for transfer of single cell data from one worksheet to another? hankster

Collapse -

Reply

by dogknees In reply to Adding Values from severa ...

The syntax to refer to a single cell is as follows.

='Sheet Name'!A1

Assuming the sheet is called "Sheet Name" (note the space in the name) and you want the value in cell A1.

There is a far easier way to do this.

Put your cursor in the cell where you want the answer,
Hit the = key,
Click the sheet name at the bottom of the screen that has the value you want to copy,
Click the cell with the value you want and press the Enter key.

You can do this for any cell reference, I rarely type addresses, this is far quicker and more reliable.

Back to Software Forum
6 total posts (Page 1 of 1)  

Software Forums