Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
define one cell location throughout workbook?
Hi, first time poster. Hope someone can help.
I am trying to create a Daily Sales Workbook with one sheet acting as the YTD report. I would like to create a formula on the YTD sheet that pulls the value of a constant cell location throughout the entire workbook. for example all of my C33 cells, on every monthly sheet, represent total blue widgets sold. on my YTD sheet I would like to SUM all the C33 cells and populate that value. I can create a formula manually by adding Sheet1!C33+Sheet2!C33 etc, but I thought there must be an easier way. I found the GLOBAL NAMING instructions which will define a cell to be called by any other sheet in the workbook, but I want to do the exact opposite. Or maybe define the cell on the YTD sheet and then push the infomation to the GLOBALLY defined cell..... I am sure the answer is right there but I am not seeing it. also I am wondering if the formula remove the requirement of creating all the sheets in the book ahead of time? Thanks in advance for a probably silly question. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
define one cell location throughout workbook?
=SUM(Sheet1:Sheet13!C33)
I prefer to insert a couple of dummy sheets at either end of my existing sheets. Name them Start and End Then on your YTD sheet enter =SUM(Start:End!C33) Will sum all C33's When inserting a new sheet make sure you insert between Start and End sheets. Gord Dibben MS Excel MVP On 1 Sep 2006 15:28:00 -0700, "New_to_accounting" wrote: Hi, first time poster. Hope someone can help. I am trying to create a Daily Sales Workbook with one sheet acting as the YTD report. I would like to create a formula on the YTD sheet that pulls the value of a constant cell location throughout the entire workbook. for example all of my C33 cells, on every monthly sheet, represent total blue widgets sold. on my YTD sheet I would like to SUM all the C33 cells and populate that value. I can create a formula manually by adding Sheet1!C33+Sheet2!C33 etc, but I thought there must be an easier way. I found the GLOBAL NAMING instructions which will define a cell to be called by any other sheet in the workbook, but I want to do the exact opposite. Or maybe define the cell on the YTD sheet and then push the infomation to the GLOBALLY defined cell..... I am sure the answer is right there but I am not seeing it. also I am wondering if the formula remove the requirement of creating all the sheets in the book ahead of time? Thanks in advance for a probably silly question. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
define one cell location throughout workbook?
Try something like this, instead:
Insert a blank sheet before the first daily sheet. Name that sheet StartSheet. .. Insert a blank sheet after the last daily sheet. Name that EndSheet. On the YTD sheet, build this formula: =SUM(StartSheet:EndSheet!C33 That formula will sum cell C33 on every sheet between StartSheet and EndSheet, inclusive. If you want to exclude a sheet, just move it so it's not between those 2 sheets. Any sheet you put between StartSheet and EndSheet will be included in the total. Also, you can hide StartSheet and EndSheet and the formula will still work Is that something you can work with? *********** Regards, Ron XL2002, WinXP "New_to_accounting" wrote: Hi, first time poster. Hope someone can help. I am trying to create a Daily Sales Workbook with one sheet acting as the YTD report. I would like to create a formula on the YTD sheet that pulls the value of a constant cell location throughout the entire workbook. for example all of my C33 cells, on every monthly sheet, represent total blue widgets sold. on my YTD sheet I would like to SUM all the C33 cells and populate that value. I can create a formula manually by adding Sheet1!C33+Sheet2!C33 etc, but I thought there must be an easier way. I found the GLOBAL NAMING instructions which will define a cell to be called by any other sheet in the workbook, but I want to do the exact opposite. Or maybe define the cell on the YTD sheet and then push the infomation to the GLOBALLY defined cell..... I am sure the answer is right there but I am not seeing it. also I am wondering if the formula remove the requirement of creating all the sheets in the book ahead of time? Thanks in advance for a probably silly question. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
define one cell location throughout workbook?
Thank you so much for your quick response.
I knew there would be an easier solution. Have a nice Holiday! Gord Dibben wrote: =SUM(Sheet1:Sheet13!C33) I prefer to insert a couple of dummy sheets at either end of my existing sheets. Name them Start and End Then on your YTD sheet enter =SUM(Start:End!C33) Will sum all C33's When inserting a new sheet make sure you insert between Start and End sheets. Gord Dibben MS Excel MVP On 1 Sep 2006 15:28:00 -0700, "New_to_accounting" wrote: Hi, first time poster. Hope someone can help. I am trying to create a Daily Sales Workbook with one sheet acting as the YTD report. I would like to create a formula on the YTD sheet that pulls the value of a constant cell location throughout the entire workbook. for example all of my C33 cells, on every monthly sheet, represent total blue widgets sold. on my YTD sheet I would like to SUM all the C33 cells and populate that value. I can create a formula manually by adding Sheet1!C33+Sheet2!C33 etc, but I thought there must be an easier way. I found the GLOBAL NAMING instructions which will define a cell to be called by any other sheet in the workbook, but I want to do the exact opposite. Or maybe define the cell on the YTD sheet and then push the infomation to the GLOBALLY defined cell..... I am sure the answer is right there but I am not seeing it. also I am wondering if the formula remove the requirement of creating all the sheets in the book ahead of time? Thanks in advance for a probably silly question. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell from one workbook to another | Excel Worksheet Functions | |||
insert date | Excel Worksheet Functions | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Custom functions calculating time arguments Help Desperate | Excel Worksheet Functions | |||
copying cell names | Excel Discussion (Misc queries) |