Trying to make a Parent row with Children Rows?

[tgelinas]

Hello! I have to make a chart with all items by a specific vendor. My manager would like a chart that can be sorted, but would also like different variations of the items.

what I have for my headers

Item Number, Item, BOH, Cost, Total, Category

example

23729, 15 PC Bath set, 0, $5.50, $0.00, Bathroom
43392, 3 PC Bath Set, 0, $5.50, $0.00, Bathroom
53943, Carmella 5x8, -4, $52.00, -$208.00, Rug
53944, Dier Debwan 5x8, 7, $40.00, $280.00, Rug

Now I have made a chart from that and so all the columns can be sorted, my issue is now I have sheets and different colors of sheets.

Example

12345, Queen Sheet Set, 0, $5.50, $0.00, Bedroom
White
black
blue
green
12346, King Sheet Set, 0, $5.50, $0.00, Bedroom
White
black
purple
orange

My Manager wants the colors separated as you can see above. But each color would be its own row. Is it possible to lock the color rows to the parent, so when I sort the whole spread sheet the colors will stay below the sheet row to which they belong?

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[tgelinas]

ok! I believe the attachment should work now

[Glenn Kennedy]

1. Convert the range to a structured Table.

2. Use this to infill the two columns with gaps:
G2:
=IF(B2="","",LOOKUP(10^300,A$2:A2))

H2:
=IF(B2="","",LOOKUP("Zzzz",F$2:F2))

Tidy up the formula in E2:
=IF(B2="","",D2*C2)

now... sort the table using columns G & H instead of A & F (these can be hidden, if you wish). Everything stays where it should.

[tgelinas]

ahh! thank you!!

[tgelinas]

ahh! thank you!! Do you know if there is an instructional video/webpage? I would like to learn how to do this, and know what all the formulas mean

[Glenn Kennedy]

Hahaha. No idea. You can search YouTube for a long time before finding anything useful, I think....

However, by way of explanation..


=LOOKUP(10^300,A$2:A2) is looking down the column one cell at a time, for the number 10^1300 (10 with 300 zeros after it). It doesn't find it and gives, up... returning the last number it did see, as a default. So any gaps are infilled by the last number in column A.

Same same for this:

=LOOKUP("Zzzz",F$2:F2) where its looking for the word Zzzzz. Again, since it doesn't find it, it gives up and returns the last one it did see.

the other bits are there to ensure that blanks are returned when there are no values. when you add new rows to the table, the table should increase in size and the formulae should copy down automatically.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[tgelinas]

thank you for all your help!! that makes my life so much easier, and now knowing what it does I can use it without being blinded by questions haha.

thank you!!!!!