Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 49
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
What's command for find sheets exist in workbooks? Jennifer Excel Worksheet Functions 3 December 14th 06 03:16 PM
add new sheets in a workbook with new sheets being a variable [email protected] Excel Discussion (Misc queries) 1 April 11th 06 08:38 PM
How to have a variable cell reference across sheets? Shane Gibson Excel Discussion (Misc queries) 3 March 8th 06 11:10 PM
Command button and linking with other sheets in the workbooks Ken Vo Excel Discussion (Misc queries) 2 January 17th 06 10:30 PM
Need Help: 'sheets' function with a variable in a formula livin Excel Worksheet Functions 2 September 7th 05 10:27 PM


All times are GMT +1. The time now is 10:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"