Lists Within Entries In A List

[Shane]

Is it possible, in Excel 2004, to produce a list such that the entries
within that list contain lists for some fields?


An example of the format which I wish to create would be a list of the
customers of a computer repair shop with a small history of the
dealings with each customer, as in the diagram below, which hopefully
is clear, though I realise that it is subject to the whims of
formatting.


ID Name Address History

1 Tom Fictional Lane 3/4 - brought in
4/4 - repaired
5/4 - picked up
2 Dick Imaginary Road 4/4 - brought in
5/4 - replacement ordered
3 Harry Mythical Avenue 5/4 - brought in


Though I could make a list of customers that was not managed by the
List Manager and create a List Manager-managed list for the histories,
I would lose many of the advantages of being able to use List Manager
to manipulate the list.


Because my concern is to be able to create a functioning list as
described, if one cannot create lists within lists, any imaginative
solutions to my problem are welcome.

 

[JE McGimpsey]

Is it possible, in Excel 2004, to produce a list such that the entries
within that list contain lists for some fields?


Because my concern is to be able to create a functioning list as
described, if one cannot create lists within lists, any imaginative
solutions to my problem are welcome.

There's no way to have a list within a list, but you may be able to
achieve the effect you want with Conditional formatting, setting the
font color to the background color (white, by default).

It appears from your example that you don't want to show the information
in the first three fields if the ID number is the same as the one above
it. To accomplish that, select column A in your list and choose the
Column Settings List toolbar button. Click the Conditional Formatting
button in the Settings dialog. Enter

CF: =A2=A1
Format: Font/<white>

Click OK, OK. Repeat for columns B & C.

Now, whenever the ID number in the row matches the ID number in the row
above it, the information in the first three columns will be hidden. So
when you add a row, choose Sort Ascending from the Autofilter dropdown.

I've put an example, using your data, at

ftp://ftp.mcgimpsey.com/excel/shane_demo.xls

The demo uses VLOOKUP() to fill in columns B & C once an ID number is
entered in column A. The data will be visible until you sort (unless the
ID matches the last entry).

Post back if I've missed the boat on what you're after.

 

[Shane]

I've tried out the technique that you came up with, and it is quite
effective for presenting a document that looks as I want it. However,
I also have a column for the cost of the work done, and when I do as
you say, the totals row at the bottom is not accurate, because it ends
up adding many items more than once. If I were to only put the cost in
one entry I would lose the convenience of seeing all the data in a
single row. Any ideas for how to make the totals result only add the
values which aren't formatted with white text?