Questions

Adding Values from several worksheets within 1 workbook - Excel 2010

+
0 Votes
Locked

Adding Values from several worksheets within 1 workbook - Excel 2010

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.
  • +
    0 Votes
    databaseben

    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.

    +
    0 Votes
    ecntrc

    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

    +
    0 Votes
    dogknees

    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

    +
    0 Votes
    hankster806

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

    +
    0 Votes
    dogknees

    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.

  • +
    0 Votes
    databaseben

    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.

    +
    0 Votes
    ecntrc

    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

    +
    0 Votes
    dogknees

    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

    +
    0 Votes
    hankster806

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

    +
    0 Votes
    dogknees

    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.