Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM command using variable sheets
I have a series of sheets containing product data, each with the same
structure. In the first sheet (e.g. A_MONTH), I have monthly data. In the second sheet (A_YTD), I calculate the year to date figures from the data in the first sheet using a simple SUM command. I only need to look at one product at a time and (to save space) was going to create a single YTD sheet where the figures would be calculated for the product being viewed by the user (this would be selected by a dropdown box). However, I cannot work out how I can change the simple =SUM(A_MONTH!B10:B10), etc formulas to look at the appropriate MONTH sheet for the product that has been selected. I feel certaijn that I will need to use a different command, but cannot work out which. IanC |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM command using variable sheets
Where B1 is your sheet name (A_Month in your example) and assuming you meant
to sum B1:B10 (instead of B10:B10) then try: =SUM(INDIRECT("'"&B1&"'!B1:B10")) If you actually want just cell B10 then try: =INDIRECT("'"&B1&"'!B10") "IanC" wrote: I have a series of sheets containing product data, each with the same structure. In the first sheet (e.g. A_MONTH), I have monthly data. In the second sheet (A_YTD), I calculate the year to date figures from the data in the first sheet using a simple SUM command. I only need to look at one product at a time and (to save space) was going to create a single YTD sheet where the figures would be calculated for the product being viewed by the user (this would be selected by a dropdown box). However, I cannot work out how I can change the simple =SUM(A_MONTH!B10:B10), etc formulas to look at the appropriate MONTH sheet for the product that has been selected. I feel certaijn that I will need to use a different command, but cannot work out which. IanC |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM command using variable sheets
Thank you. Works perfectly.
"JMB" wrote: Where B1 is your sheet name (A_Month in your example) and assuming you meant to sum B1:B10 (instead of B10:B10) then try: =SUM(INDIRECT("'"&B1&"'!B1:B10")) If you actually want just cell B10 then try: =INDIRECT("'"&B1&"'!B10") "IanC" wrote: I have a series of sheets containing product data, each with the same structure. In the first sheet (e.g. A_MONTH), I have monthly data. In the second sheet (A_YTD), I calculate the year to date figures from the data in the first sheet using a simple SUM command. I only need to look at one product at a time and (to save space) was going to create a single YTD sheet where the figures would be calculated for the product being viewed by the user (this would be selected by a dropdown box). However, I cannot work out how I can change the simple =SUM(A_MONTH!B10:B10), etc formulas to look at the appropriate MONTH sheet for the product that has been selected. I feel certaijn that I will need to use a different command, but cannot work out which. IanC |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM command using variable sheets
You're welcome - thanks for the feedback.
"IanC" wrote: Thank you. Works perfectly. "JMB" wrote: Where B1 is your sheet name (A_Month in your example) and assuming you meant to sum B1:B10 (instead of B10:B10) then try: =SUM(INDIRECT("'"&B1&"'!B1:B10")) If you actually want just cell B10 then try: =INDIRECT("'"&B1&"'!B10") "IanC" wrote: I have a series of sheets containing product data, each with the same structure. In the first sheet (e.g. A_MONTH), I have monthly data. In the second sheet (A_YTD), I calculate the year to date figures from the data in the first sheet using a simple SUM command. I only need to look at one product at a time and (to save space) was going to create a single YTD sheet where the figures would be calculated for the product being viewed by the user (this would be selected by a dropdown box). However, I cannot work out how I can change the simple =SUM(A_MONTH!B10:B10), etc formulas to look at the appropriate MONTH sheet for the product that has been selected. I feel certaijn that I will need to use a different command, but cannot work out which. IanC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What's command for find sheets exist in workbooks? | Excel Worksheet Functions | |||
add new sheets in a workbook with new sheets being a variable | Excel Discussion (Misc queries) | |||
How to have a variable cell reference across sheets? | Excel Discussion (Misc queries) | |||
Command button and linking with other sheets in the workbooks | Excel Discussion (Misc queries) | |||
Need Help: 'sheets' function with a variable in a formula | Excel Worksheet Functions |