In the attached file the program tab has a list of programs that can be selected by putting an "X" in the Check column. At most a user will mark off 2 programs. I need those 2 programs that are selected to appear on the suggested format tab. The top box on the suggested format tab has no formulas and that is untimately what I want the summary to look like. Below that I did a Vlookup but it seems I can only get the first item marked with an "X" to appear. Please help!Summary.xlsx
Last edited by twhite0217; 02-09-2012 at 03:05 PM.
Try:
confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.=IFERROR(INDEX(Programs!$D$4:$D$168,SMALL(IF(Programs!$B$4:$B$168="X",ROW(Programs!$B$4:$B$168)-ROW(Programs!$B$4)+1),ROWS($A$2:$A2))),"")
Then you can use VLOOKUP to lookup those items in column D of the Programs table, and return adjacent columns.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@NBVC
...And for us that we use Excel 2003 or less??
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Pls see if attachment meet your need.
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
Try then:
Confirmed with CSE key combo.=LOOKUP(REPT("z",255),CHOOSE({1,2},"",INDEX(Programs!$D$4:$D$168,SMALL(IF(Programs!$B$4:$B$168="X",ROW(Programs!$B$4:$B$168)-ROW(Programs!$B$4)+1),ROWS($A$2:$A2)))))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@NVBC
I looked at A column and know that B column can be "X" or "XX"...
Click the star icon in left-corner of my post if you find my post userful!
Quang PT
quangphanidico@yahoo.com
PM me: Y!M: quangphanidico
@NBVC
Fantastic job, ...as usual!!!!!!!!
@bebo
Very good job!!
Thanks both of you. I learned something new to night
Regards
Fotis.
I am proud that i am Greek.
Just to know every one.We Greeks, we are nοt proud of our politicians. Υou?
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Please,mark your thread [SOLVED] if you received your answer.
My Avadar picture, is from Athens Acropolis.
http://www.theacropolismuseum.gr
http://www.visitgreece.gr/
Thank you all. However if you check off a different program these don't seem to work. I am not sure why some of the formulas are referencing the suggested format because i would like to remove that from the spreadsheet and only have the final summary shown. I had deleted some information from the original spreadsheet but I am just going to attach the one with all my information.
Your references to the Programs sheet are different.
Try adjusting my formula to:
Remember then to hold the CTRL and SHIFT keys down and press ENTER, then copy down.=IFERROR(INDEX(Programs!$C$4:$C$168,SMALL(IF(Programs!$A$4:$A$168="X",ROW(Programs!$A$4:$A$168)-ROW(Programs!$A$4)+1),ROWS($A$2:$A2))),"")
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Here is the formula in the attachment.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks