Excel Everlasting list

robert_harper_2000

I would like to create a list which expands as fields are filled in. I have a list which has a total at the end and want the spreadsheet to still be able to calculate the list total. But as one field is filled it automatially expands to lsit another field underneath.

Does this make sense lol?

Stompa

robert_harper_2000 wrote: »

Does this make sense lol?

Not entirely! Had you considered putting the SUM at the top of the list rather than the bottom?

PhilCo_2

You can use the offset and count formulas to create a dynamic range. If your list is in column A, creating a named range with =OFFSET(Sheet1!$A$1,,,COUNT(Sheet1!$A:$A),) (use counta if text) will make the range expand to match the size of the list. Sheet1!$A$1 is the starting point of the list.

robert_harper_2000

For example I want to fill in 'B' and if there are more than 5 refunds I would like Excel Spreadsheet to enter another box below the last.
I would then like the total field below to automatically recalculate and includ any additional boxes it creates.

'A' will then need to re-adjust and copied the sum of the field.

I don't quite know if this is what Philco meant?

Cheers

Stompa

It sounds like you need something a bit like this:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.excel.misc&tid=791897f1-4ce0-4bbe-a4c4-5b63ef15940b&cat=en_us_fc36b261-85c3-48f6-a88a-fea4ddf14032&lang=en&cr=us&sloc=&p=1

robert_harper_2000

Looks great! But I can't get it to do what I want still, I changed Row to 35 which is the one just above Total, I also changed the Column to 4 as it is in Column D

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 4 And Target.Row = _
Cells(Rows.Count, 35).End(xlUp).Row - 1 Then
Rows(Target.Row + 1).Insert
End If
Application.EnableEvents = True
End Sub

Sub fixifbroke()
Application.EnableEvents = True
End Sub

Stompa

It's a little difficult to tell why it's not working for you, but FWIW the original code appears to work for me.

robert_harper_2000

hmm.. been playing around with it but had little success so far... any other ideas?

(also cheers for trying )

Stompa

At a guess I'd say it is working but perhaps not on the row you were expecting. It looks as though the code needs to be adjusted to take into account the number of rows containing data below the row you want to move down. From your screenshot it looks as if you have other cells containing data below your 'Total' cell. The 4th post in that thread may help?