Hello , i stuck with some formula in Excel. I upload my work in website, can somebody help me
I have a list in section 1 names , how many ppl and table no's.
I want to write a formul which is automatically fills the tables.
For example if someone in the list has a reservation on table 1 , formul automatically put it to table 1 . Thanks
Here is the file .
http://www.benmutlu.net/example.xls
Sarah M
Favorite Answer
You need to use array formulas for this:
Copy this into cell B17:
=IF( COUNTIF($C$2:$C$13, $D$17)>=1, INDEX( $A$2:$C$13, SMALL(IF( $C$2:$C$13= $D$17, ROW($C$2:$C$13)- ROW($C$2)+1, ROW($C$13)+1), 1),0), "")
Instead of just pressing ENTER to confirm it, press CTRL+SHIFT+ENTER at the same time. Excel will surround it in {curly brackets} if you do it correctly. Do this whenever you edit/enter an array formula.
For B18, use the same formula but change two of the "1"'s into "2"s. The first one: >=1 near the beginning, and the second one: ,1),0,"") at the end, like so:
=IF( COUNTIF($C$2:$C$13, $D$17)>=2, INDEX( $A$2:$C$13, SMALL(IF( $C$2:$C$13= $D$17, ROW($C$2:$C$13)- ROW($C$2)+1, ROW($C$13)+1), 2),0), "")
For B19, change those into 3's. And so on for each item in your first table.
Now, once you have the first column of your first table set up, copy the formulas from B17:B23 and paste into cells C17:C23. Do a find and replace:
FIND: ),0,"")
REPLACE WITH: ),2,"")
This will populate the second column of your table with the "how many people" data instead of the name info.
Then, select B17:C23, and replace all the $D$17 with D17.
Now you can copy B17:C23 and paste it into your 9 other tables.
Hope my explanation works for you.
"Tree"
i dont think excel has that kind of capability