define one cell location throughout workbook?

  • Thread starter Thread starter New_to_accounting
  • Start date Start date
N

New_to_accounting

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.
 
=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
 
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
 
Thank you so much for your quick response.

I knew there would be an easier solution.

Have a nice Holiday!
 
Back
Top