2
2
In a workbook I have 7 sheets(Totals and then Mon to Sat),I want to extract rows which have the word "CHEQ" in its cell (this is a dropdown list with two options-CHEQ/PAID)from all sheets. On my front sheet I used this formula:
=IF(ROWS(A$13:A13)>$C$10,"",INDEX(Monday!A$3:A$62,SMALL(IF(Monday[Paid]=$A$10,ROW(Monday[Paid])-ROW(Monday!$I$3)+1),ROWS(A$13:A13))))
This formula works fine for one worksheet (eg. Monday) but is it possible to show the extracted rows from all 6 sheets on the front page? I only have Excel NOT Access. These are the 12 headers on row A12
Col Name Cod House Car Date Discount 2nd Paid Extra Letter Posted
The exported data appears like this (this just an example):
Col Name Cod House Car Date Discount 2nd Paid Extra Letter Posted
12 Robbs 1244 Ren 11/10 10% 5 CHEQ 0 0 No
15 Jones 7784 Ren 12/10 15% 1 CHEQ 0 0 No
18 Doese 1184 Ren 12/11 12% 1 CHEQ 0 0 No
Any ideas on what to do to this formula? I am using Excel 2010.
VLOOKUP might be a prospect if you are looking to get "CHEQ" from 6 sheets to a single sheet. (CHEQ would be in the 9th column?). you would have to do this for each sheet from the master sheet. – Carl B – 2012-10-23T19:39:53.197
what would that look like?could you show me a formula please. – user167210 – 2012-10-23T21:41:15.017
VLOOKUP will not provide the required result, but I will keep testing and if I find it, I will post answer. – Carl B – 2012-10-24T12:40:15.210
Would your expected result yeild 6 rows (for Mon thru Sat) of each name on your front page? – Carl B – 2012-10-24T12:43:03.007
Yes on the front back it would show up. – user167210 – 2012-10-24T18:56:09.117
oops. Yes on the front page it would show up the results. cells A15 to K15 and A30 to K30 has the formula I used (shown at the top of this page). cell A12 has droplist "Paid"/"Cheq". cell B12 has =COUNTIF(Monday[Paid],A12) – user167210 – 2012-10-24T19:07:48.557