Need Help Urgently Please!!!!!

[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!!!!!

 

[Guest]

Just put $ signs before the cell you want to lock
e.g $A$1

 

[llorton]

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

 

[lozwest]

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.

 

[lozwest]

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.

 

[Harlan Grove]

...
...

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

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.

 

[Ken Wright]

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.

 

[Bruce Conley]

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')