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.

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.

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.

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.