Need Help Urgently Please!!!!!

  • Thread starter Thread starter lozwest
  • Start date Start date
L

lozwest

Can anyone help, I have looked through the Excel Help Book and I can not
find what I need, mainly because I don't understand half of it!!!!

I have two spreadsheets, totals from one (Allocation sheet)are being
brought forward to another (Profit and Loss). The problem is, when I
insert a line into my allocation sheet is distorts my Profit and Loss
and does not pick up from the original cells that it should, so I have
to re do them all again. It is a absolute pain and I am getting very
stressed to say the least.

I know there must be a way to lock the cell so that if I insert a row,
it will not alter the totals being picked up.

Waiting in total anticipation, stressed Excel User!!!!!:rolleyes:
 
This behavior seems strange particularly since that is exactly wha
Excel is used for.

Do you mean that in the P&L there is a reference to (for example)
Allocation@A22?

You add a new row at row 18 and the new refence doesn't now point t
Allocation@A23
 
I believe what he is saying is he has two separate workpooks
he is making changes to the allocation workbook with the profit and
loss work book closed

Then when he opens the Profit and loss work book the data has changed
because it is still looking for the old cell from before the new cell
was inserted

If both workbooks are open at the same time you will not have this
problem

Or better yet just have both sheets in the same book then the problem
is solved

Randall
 
Yep, that is exactly what I mean. I have a list of say Advetising Cost'
and I want the Total of that to be shown in the P&L, but if I add a
extra line, the Total gets moved.
 
Thank you for this, I will put the allocation sheet into my Summar
Accounts workbook and then hopefully this should solve the problem.

Thanks again.
 
...
...
If both workbooks are open at the same time you will not have this
problem

Or better yet just have both sheets in the same book then the problem
is solved
Click to expand...

It's solved in this instance. Another, more general solution would be to use
defined names in the referenced workbook, and refer to those defined names
rather than hardcoded range addresses in the other workbook.
 
You should also take heed of Harlan's advice re named ranges. Putting the
sheets in the same book is a good idea, but you could still make things easier
for yourself. If you name the Allocation Total cell say AllocTot, on any other
sheet you could simply type =AllocTot and that data would appear. Also makes it
real easy to see where data comes from and what it is.
 
Really 2 options.
You can ammend the formula
=sum(allocation!A2:A22) to =sum(allocation!$a$2:$a$22)
that way, when you insert a row within the range of a2 to
a22, the formula will adjust to pick up the change.
Second,
You can name the range, then use the range name in your
sum expression.
Highlight the area that you want to add, then choose
Insert, Name, Define. Type in a name (something like
Expense)
On the P/L page your sum expression becomes =sum
(allocation!'Expense')
 
Back
Top