Insert rows in Excel Ledger problem

[Gary Huston]

I am using a ledger for simple accounts, Date, Name, Service, Amount
and Total.
The far right column keeps a running total. I have however omitted
some data and am trying to add rows in various places in date order.
I can insert the rows no problem, however, the final right hand cell
remains blank and will not update the total.
I have tried the format painter which works for all cells but the
last, the format bar is greyed out along with anything else I thought
I could use to solve the problem.
I have tried adding the data on the last row and dragging it up to
where I want it but as soon as I move it even one place the final cell
goes blank again...
I'm sure it's something simple and that I have overcome this problem
before but this time it's driving me nuts!

 

[Bernie Deitrick]

Gary,

Generally, if you insert rows into a table, you will mess up the formula
references. You can easily solve this by copying down the formula for that
column from a row that is above your insertion point, all the way down to
the end of your table.

One way around this is to not use formulas in your table, but to use the
table a the source for a pivot table. In those cases, insertion into the
table may actually be preferred.

HTH,
Bernie
MS Excel MVP

 

[Gary Huston]

Gary,

Generally, if you insert rows into a table, you will mess up the formula
references.  You can easily solve this by copying down the formula for that
column from a row that is above your insertion point, all the way down to
the end of your table.

One way around this is to not use formulas in your table, but to use the
table a the source for a pivot table.  In those cases, insertion into the
table may actually be preferred.

HTH,
Bernie
MS Excel MVP

I would love to copy the formula from above, the problem is I can't
access it, I can't get into formulas, everything is greyed out and it
says formulas are not used in ledgers.

 

[Bernie Deitrick]

Gary,

What version of Excel are you using? And, is the sheet protected?

Bernie

Gary,

Generally, if you insert rows into a table, you will mess up the formula
references. You can easily solve this by copying down the formula for that
column from a row that is above your insertion point, all the way down to
the end of your table.

One way around this is to not use formulas in your table, but to use the
table a the source for a pivot table. In those cases, insertion into the
table may actually be preferred.

HTH,
Bernie
MS Excel MVP

I would love to copy the formula from above, the problem is I can't
access it, I can't get into formulas, everything is greyed out and it
says formulas are not used in ledgers.

 

[Gary Huston]

Gary,

What version of Excel are you using?  And, is the sheet protected?

Bernie








I would love to copy the formula from above, the problem is I can't
access it, I can't get into formulas, everything is greyed out and it
says formulas are not used in ledgers.

I'm using office 2008 for Mac, the sheet is not protected.
I have asked in the Mac users forum but no replies.

 

[Bernie Deitrick]

Gary,

I'm not familiar with Office 2008, so I think that my help will end here,
with one last question - did you download the file from somewhere or create
it yourself?

Bernie

Gary,

What version of Excel are you using? And, is the sheet protected?

Bernie








I would love to copy the formula from above, the problem is I can't
access it, I can't get into formulas, everything is greyed out and it
says formulas are not used in ledgers.

I'm using office 2008 for Mac, the sheet is not protected.
I have asked in the Mac users forum but no replies.

 

[Gary Huston]

Gary,

I'm not familiar with Office 2008, so I think that my help will end here,
with one last question - did you download the file from somewhere or create
it yourself?

Bernie







I'm using office 2008 for Mac, the sheet is not protected.
I have asked in the Mac users forum but no replies.

It was a template that comes bundled, there are dozens to choose
from, I just customized one to suit my needs.
I'm wondering if something has gone wrong within the ledger it's self,
when I open a new one and try to add rows it seems to work fine.
I just didn't want to have to re enter almost a whole years accounts
into a new document.
This one has been working fine up until this week.
Looks like I might be in for a long evenings work!

 

[Bernie Deitrick]

Gary,

Since you have a table of data, just copy the data (possibly column by
column if the columns are in a different order) and use paste special /
values when you migrate to a new workbook. The template that you chose
clearly has some VBA code or protection scheme that prevents you from using
it the way that you want. But migrating to a new workbook should take
minutes, not hours.

Bernie

 

[Gary Huston]

Gary,

Since you have a table of data, just copy the data (possibly column by
column if the columns are in a different order) and use paste special /
values when you migrate to a new workbook.  The template that you chose
clearly has some VBA code or protection scheme that prevents you from using
it the way that you want.  But migrating to a new workbook should take
minutes, not hours.

Bernie

Thanks Bernie, I just pasted special all the data, had to do it all in
one go as It wouldn't let me select just one column at a time, into a
new workbook customized the same as the last one and hey presto it all
works fine. I can add rows no problem. I think there must have been a
bug in the old one.
Thanks for your help.

Gary