Home |
Search |
Today's Posts |
#1
|
|||
|
|||
seating chart
page 1 of my worksheet has a list of names, meals and table numbers that I type
page 2 has a list of table numbers (the seating chart) how do i populate the page 2 table numbers automatically without using pivot tables? |
#2
|
|||
|
|||
(Re-sent, apologies for the unintentioned advance dating)
Maybe something along these lines .. Assume you have the sample table below in Sheet1, in A1:B10 Names Table# Name1 2 Name2 3 Name3 4 Name4 1 Name5 3 Name6 3 Name7 4 Name8 1 Name9 2 List across in E1:H1, the 4 table numbers: 1,2,3,4 Put in E2: =IF($B2=E$1,ROW(),"") Copy E2 across to H2, fill down to H10 In Sheet2 --------- List across in A1:D1, the 4 table numbers: 1,2,3,4 Put in A2: =IF(ISERROR(MATCH(SMALL(Sheet1!E$2:E$10,ROWS($A$1: A1)),Sheet1!E$2:E$10,0))," ",INDEX(Sheet1!$A$2:$A$10,MATCH(SMALL(Sheet1!E$2:E $10,ROWS($A$1:A1)),Sheet1! E$2:E$10,0))) Copy A2 across to D2, fill down as many rows as was done in cols E to H in Sheet1, i.e. down to D10 The above will extract and list the names from Sheet1 under each table number Adapt / extend to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- cds wrote in message ... page 1 of my worksheet has a list of names, meals and table numbers that I type page 2 has a list of table numbers (the seating chart) how do i populate the page 2 table numbers automatically without using pivot tables? |
#3
|
|||
|
|||
thanks. that works for the names, however i also need to extrat the 'meal'
next to the person. i tried the lookup but since the data is not sorted it doesn't work. any suggestions? "Max" wrote: Maybe something along these lines .. Assume you have the sample table below in Sheet1, in A1:B10 Names Table# Name1 2 Name2 3 Name3 4 Name4 1 Name5 3 Name6 3 Name7 4 Name8 1 Name9 2 List across in E1:H1, the 4 table numbers: 1,2,3,4 Put in E2: =IF($B2=E$1,ROW(),"") Copy E2 across to H2, fill down to D10 In Sheet2 --------- List across in A1:D1, the 4 table numbers: 1,2,3,4 Put in A2: =IF(ISERROR(MATCH(SMALL(Sheet1!E$2:E$10,ROWS($A$1: A1)),Sheet1!E$2:E$10,0))," ",INDEX(Sheet1!$A$2:$A$10,MATCH(SMALL(Sheet1!E$2:E $10,ROWS($A$1:A1)),Sheet1! E$2:E$10,0))) Copy A2 across to D2, fill down as many rows as was done in cols E to H in Sheet1, i.e. down to D10 The above will extract and list the names from Sheet1 under each table number Adapt / extend to suit -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- cds wrote in message ... page 1 of my worksheet has a list of names, meals and table numbers that I type page 2 has a list of table numbers (the seating chart) how do i populate the page 2 table numbers automatically without using pivot tables? |
#4
|
|||
|
|||
Taking a simple extension
to the assumed set-up in Sheet1 where "Meal" is placed in col C, i.e.: Names Table# Meal Name1 2 Meal1 Name2 3 Meal2 Name3 4 Meal3 etc Make a concatenated field in col D to join the Name to the Meal Put in D2: =A2&"-"&C2 Copy down to D10 In Sheet2 ------------- Just replace the formula in A2 by: =IF(ISERROR(MATCH(SMALL(Sheet1!E$2:E$10,ROWS($A$1: A1)),Sheet1!E$2:E$10,0))," ",INDEX(Sheet1!$D$2:$D$10,MATCH(SMALL(Sheet1!E$2:E $10,ROWS($A$1:A1)),Sheet1! E$2:E$10,0))) Copy across and fill down as before The amendment made was to the ...INDEX(...) part, to point now to col D instead of col A (in Sheet1), viz.: .... INDEX(Sheet1!$A$2:$A$10 changed to: .....INDEX(Sheet1!$D$2:$D$10 The above will return:the concat "Name-Meal"s under each table number -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "cds" wrote in message ... thanks. that works for the names, however i also need to extrat the 'meal' next to the person. i tried the lookup but since the data is not sorted it doesn't work. any suggestions? |
#6
|
|||
|
|||
"Don Guillett" wrote in message
... Max, Your dates are WAY OFF. If by mistake, please correct. I automatically delete these but have ONCE been guilty. Yes, my apologies .. by 24 years, I'm afraid. Corrected immediately the same day - 3 Jan, upon alert by Dave Peterson via a response in ..public.excel (Chuck also sent a private alert a couple of days later) FWIW, lame excuse: Desktop's harddisk reformatted recently .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Oops, typo detected:
.. by 24 years should read : .. by 20 years -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing a Chart Directly?? | Charts and Charting in Excel | |||
Chart formatting | Charts and Charting in Excel | |||
Problem with xlusrgal.xls file | Charts and Charting in Excel | |||
pivot table multi line chart | Charts and Charting in Excel | |||
Why do my text boxes disappear from my chart when I click out? | Charts and Charting in Excel |