Excel 2007 : Consolidating 4 growing lists

[lmiddleham]

Hi

I've got a sheet that pulls in info from 4 lists and puts it into the same format. Here's an example but just of the first 2 lists:

SDB
RECEIPT DATE AMOUNT NARRATIVE TYPE
1 23.02.2010 15 0 Cash Sales
2 23.02.2010 20 0 Cash Sales
3 24.02.2010 20 0 Cash Sales
4 25.02.2010 -5 refund overpay Cash Sales
5 0 0 0 0

I'm trying to create another list in this worksheet with the same headings.
I basically want it to keep going down this first list until it gets to a line with no amount, then move onto the 2nd list, and do the same there, then onto the 3rd list and so on.
Each list will keep growing over time as things are added to the lists.

Any ideas? The closest I've got is a chain of IF statements but it either doesn't pick up the 1st row of the 2nd list (because I've dragged the formula down a few lines, it picks up a few lines down the list) or a chain of IF statement that picks up the first line of the 2nd list (because I use $ to force it to look at the first line) but then keeps looking at that first line (because of the $)

Thanks

[JBeaucaire]

Seems like the simplest would be to have this "consolidated" list on a sheet of its own, and have Excel automatically copy all the data onto that list each time you bring the sheet up onscreen. It's a macro, a VBA solution, but it's robust and shouldn't require any maintenance.

If that's ok, then create a sample workbook showing your complete actual layout. Don't leave anything out, make sure the consolidated list is shown on a sheet of its own. Click GO ADVANCED and use the paperclip icon to post up your workbook and we'll suggest something specifically appropriate for your design.

[lmiddleham]

Hi

I've attached the spreadsheet as it is now.
If you look at the tab called bank - I'm trying to take the info from the lists headed SDB (feeds in from the SDB tab) PDB (feeds from PDB tab) and the 2 journal lists (from Journal tab) and put it into the list labelled Bank and prefrably in date order.

Sorry the bank tab looks a little messy, I'll be hiding all the workings once I can get the Bank list to only pull through items with an amount.

Thanks for your help.

[JBeaucaire]

It only looks messy because you need to go to TOOLS > OPTIONS > [ ] Zero Values and uncheck that option. Will that suffice?

[lmiddleham]

When I get a macro or formula to consolidate the lists I'll probably just hide that whole sheet.
I didn't think of using macros to consolidate the lists. I guess you just programme it to copy and paste the 4 lists onto 1 big list, cut out all the stuff that is zeros, and then sort it into date order?

Sadly, I have no clue how to write a macro, so no clue how to do what I just said!

[lmiddleham]

Hi

Sorted it.

I did it manually and recorded a macro of me doing it.
Thanks for putting the idea of macros into my head.