Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
Look up using multiple sheets and multiple criteria
I need to look up and populate sheet one with matching criteria on sheet 2. I
need two separate formulas to do the following: Formula 1: If Column B on €śData€ť Sheet is Greater than Cell C3 on Summary Sheet AND Column C on €śData€ť Sheet indicates €śTRUE€ť, I need the €śName€ť (from column A) and €śPercent€ť (from column B) on €śData€ť Sheet to populate in the €śName€ť (column B) and €śPercent€ť (column C) on the €śsummary Sheet€ť. For example, according to the data below, only Jen, Rob, Tony, John, and Jules, along with their corresponding percentage will show up in columns B and C on the €śSummary€ť Sheet. Formula 2: If Column F on the €śData€ť sheet is equal to or greater than 1000, AND Column €śG€ť on the €śData€ť Sheet is less than Cell F3 on the €śSummary€ť Sheet, I need the I need the €śName€ť (from column A) and €śPercent€ť (from column F) on €śData€ť Sheet to populate in the €śName€ť (column E) and €śPercent€ť (column F) on the €śSummary€ť Sheet. For example, according to the data below only Tony, John, and Jules along with their corresponding percentage will show up in columns E and F on the €śSummary€ť Sheet. As always, thank you to everyone in advance for your help! Sheet 1 (Called €śSummary€ť) B C D E F 2 Name Percent Name Percent 3 1.87 115.47 4 5 6 7 8 9 Sheet 2 (Called €śData€ť) A B C D E F G 6 Name Percent Total Percent 7 Jen 2.15 True 2646 117.6 8 Rob 1.99 True 172 142.2 9 Tony 2.52 True 3984 109.9 10 Roe 0.99 False 3599 118.98 11 John 1.94 True 4325 108.13 12 Phil 0.49 False 470 104.44 13 Kurt 1.25 False 931 103.44 14 Vic 4.31 False 0 0 16 Jules 5.45 True 1190 91.54 |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
Look up using multiple sheets and multiple criteria
Here's one play which delivers on both of your 2 orders ..
(kindly refrain from multi-posting. just post in one newsgroup will do.) In Summary, It's presumed that cols A and D are blank to begin with Put in A4: =IF(Data!B6="","",IF(AND(Data!B6$C$3,Data!C6),ROW (),"") (Leave A1:A3 blank) This is the 1st criteria col Put in B4: =IF(ROW(A1)COUNT($A:$A),"",INDEX(Data!A:A,SMALL($ A:$A,ROW(A1))+2)) Copy B4 to C4 Put in D4: =IF(Data!F6="","",IF(AND(Data!F6=1000,Data!G6<$F$ 3),ROW(),"")) (Leave D1:D3 blank) This is the 2nd criteria col Put in E4: =IF(ROW(A1)COUNT($D:$D),"",INDEX(Data!A:A,SMALL($ D:$D,ROW(A1))+2)) Copy E4 to F4 Then just select A4:F4 and copy down to cover the max expected extent of source data in "Data", eg copy down to say, F100. You'd get the results appearing in cols B, C and in cols E, F exactly as required, with all result lines neatly bunched at the top. Hide away cols A and D, if desired. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- kjguillermo wrote: I need to look up and populate sheet one with matching criteria on sheet 2. I need two separate formulas to do the following: Formula 1: If Column B on "Data" Sheet is Greater than Cell C3 on Summary Sheet AND Column C on "Data" Sheet indicates "TRUE", I need the "Name" (from column A) and "Percent" (from column B) on "Data" Sheet to populate in the "Name" (column B) and "Percent" (column C) on the "summary Sheet". For example, according to the data below, only Jen, Rob, Tony, John, and Jules, along with their corresponding percentage will show up in columns B and C on the "Summary" Sheet. Formula 2: If Column F on the "Data" sheet is equal to or greater than 1000, AND Column "G" on the "Data" Sheet is less than Cell F3 on the "Summary" Sheet, I need the I need the "Name" (from column A) and "Percent" (from column F) on "Data" Sheet to populate in the "Name" (column E) and "Percent" (column F) on the "Summary" Sheet. For example, according to the data below only Tony, John, and Jules along with their corresponding percentage will show up in columns E and F on the "Summary" Sheet. As always, thank you to everyone in advance for your help! Sheet 1 (Called "Summary") B C D E F 2 Name Percent Name Percent 3 1.87 115.47 4 5 6 7 8 9 Sheet 2 (Called "Data") A B C D E F G 6 Name Percent Total Percent 7 Jen 2.15 True 2646 117.6 8 Rob 1.99 True 172 142.2 9 Tony 2.52 True 3984 109.9 10 Roe 0.99 False 3599 118.98 11 John 1.94 True 4325 108.13 12 Phil 0.49 False 470 104.44 13 Kurt 1.25 False 931 103.44 14 Vic 4.31 False 0 0 16 Jules 5.45 True 1190 91.54 |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
Look up using multiple sheets and multiple criteria
For easy reference,
here's a sample file with the implemented construct: http://www.savefile.com/files/411923 MultiCriteria Extract wo blank rows.xls (full details, nicely rendered, as usual! <g) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
insert Rows with Formulas in Place on Multiple Sheets? | Excel Discussion (Misc queries) | |||
Autofilter Lists across Multiple Sheets, Maintain Correct Referenc | Excel Worksheet Functions | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Multiple sheets selected | Excel Discussion (Misc queries) |