moving totals
 

Hi,
I have a file with multiple worksheets 1 work sheet for a summary and the
other worksheets are my quarterly. In my quarterly worksheets I am always
inserting columns to add P.O. orders to a customer's account, but out to the
end of each customer I keep a total running of their account.

My problem is on the summary page. I want to be able to show each
customer's current condition in the summary page, how do I get their totals
to always show up when their totals on their individual worksheets keep
moving to a different column?

for instance worksheet one is my summary page cell B7 would show the total
from worksheet 2 H5, but next week that total from worksheet 2 will be in
cell K5, is there a way to make my summary cell keep track of the correct
cell on worksheet 2?

Sorry if this is too long or confusing.

moving totals
 

There is a feature of Excel called a Named Range. You use it by:

Insert Name
and use the dialog the pops up

This name will adjust as you add rows above it. By refering to the Name
instead of the cell address, the summary sheet will always stay in synch.
--
Gary''s Student - gsnu200787


"thedeuces" wrote:

Hi,
I have a file with multiple worksheets 1 work sheet for a summary and the
other worksheets are my quarterly. In my quarterly worksheets I am always
inserting columns to add P.O. orders to a customer's account, but out to the
end of each customer I keep a total running of their account.

My problem is on the summary page. I want to be able to show each
customer's current condition in the summary page, how do I get their totals
to always show up when their totals on their individual worksheets keep
moving to a different column?

for instance worksheet one is my summary page cell B7 would show the total
from worksheet 2 H5, but next week that total from worksheet 2 will be in
cell K5, is there a way to make my summary cell keep track of the correct
cell on worksheet 2?

Sorry if this is too long or confusing.

moving totals
 

I have a file with multiple worksheets 1 work sheet for a summary and
the other worksheets are my quarterly. In my quarterly worksheets I
am always inserting columns to add P.O. orders to a customer's
account, but out to the end of each customer I keep a total running of
their account.

My problem is on the summary page. I want to be able to show each
customer's current condition in the summary page, how do I get their
totals to always show up when their totals on their individual
worksheets keep moving to a different column?

for instance worksheet one is my summary page cell B7 would show the
total from worksheet 2 H5, but next week that total from worksheet 2
will be in cell K5, is there a way to make my summary cell keep track
of the correct cell on worksheet 2?

Sounds like the total is the last non-empty cell of each row.

The formula
=LOOKUP(2,1/(Sheet2!1:1<""),Sheet2!1:1)
returns the last non-empty cell value in row 1 of Sheet2.

moving totals
 

Thanks for the response. I will give them a try.

"MyVeryOwnSelf" wrote:

I have a file with multiple worksheets 1 work sheet for a summary and
the other worksheets are my quarterly. In my quarterly worksheets I
am always inserting columns to add P.O. orders to a customer's
account, but out to the end of each customer I keep a total running of
their account.

My problem is on the summary page. I want to be able to show each
customer's current condition in the summary page, how do I get their
totals to always show up when their totals on their individual
worksheets keep moving to a different column?

for instance worksheet one is my summary page cell B7 would show the
total from worksheet 2 H5, but next week that total from worksheet 2
will be in cell K5, is there a way to make my summary cell keep track
of the correct cell on worksheet 2?

Sounds like the total is the last non-empty cell of each row.

The formula
=LOOKUP(2,1/(Sheet2!1:1<""),Sheet2!1:1)
returns the last non-empty cell value in row 1 of Sheet2.