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

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.

user167210

Posted 2012-10-23T18:58:16.573

Reputation: 21

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

Answers

1

I'd probably do this by putting a button on the totals page connected to a VBA macro.

In the macro, I would apply the appropriate filter to each details page. Then copy the filtered results to the totals page. You will need, to track the number of rows pasted and set a variable to keep the next free row straight.

Then you can click the button each time you want to update the totals.

Julian Knight

Posted 2012-10-23T18:58:16.573

Reputation: 13 389

I would but the annoying thing is other staff can't use macros (we are on a system called Cytix) unless I goto everyone's pc and put my macro filled personal.xls file on there excel.so sadly that suggestion isn't possible :( I need to use a macro free answer. – user167210 – 2012-10-24T18:47:14.977

Asked: 2012-10-23T18:58:16.573

Viewed: 9 274 times

Active: 2014-02-17T16:36:18.037