# How do I extract excel data from multiple worksheets and put into one sheet?

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 Oct 23 '12 at 19:39
• what would that look like?could you show me a formula please. – user167210 Oct 23 '12 at 21:41
• VLOOKUP will not provide the required result, but I will keep testing and if I find it, I will post answer. – Carl B Oct 24 '12 at 12:40
• Would your expected result yeild 6 rows (for Mon thru Sat) of each name on your front page? – Carl B Oct 24 '12 at 12:43
• Yes on the front back it would show up. – user167210 Oct 24 '12 at 18:56