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

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

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

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