Here's a crack at automating this using non-array formulas ..
2 key functionalities are taken care of, the first being the
auto-consolidation ("stacking") of lines from all source data sheets into a
single sheet (X), then a DV selectable auto pull-out of the required "balance
lines" results from X into Y, based on the selected key col's omission value
A sample implementation is available at:
http://www.savefile.com/files/9152204
Autoconsol fr var shts n autocopy bal lines to new sht_omission basis.xls
Data is assumed in identically structured source sheets named consistently
as eg: S1, S2, S3 .. etc (numerically sequenced), within cols A to D from
row2 down, where col D = key col housing either values, eg: "Yes", "No Sales"
or nothing (Empty). Data is expected to a max of say, 10 lines per source
sheet.
In a sheet named: X,
with the same col headers pasted into A1
1,
we can auto-"consolidate" all lines from all source sheets: S1, S2, S3 ...
by stacking the lines sequentially in the numeric order of the sheetnames:
lines from S1 first, then those from S2, then from S3, and so on ..
Put in A2:
=IF(ISERROR(INDIRECT("'S"&INT((ROW(A1)-1)/10)+1&"'!A1")),"",IF(OFFSET(INDIRECT("'S"&INT((ROW(A1)-1)/10)+1&"'!A1"),MOD(ROW(A1)-1,10)+1,COLUMN(A1)-1)=0,"",OFFSET(INDIRECT("'S"&INT((ROW(A1)-1)/10)+1&"'!A1"),MOD(ROW(A1)-1,10)+1,COLUMN(A1)-1)))
Copy A2 across to D2, fill down as far as required. Fill down to cover
sufficiently the aggregate number of rows from all the source sheets, viz.:
[assumed 10 rows per source sheet] x [# of source sheets, eg 5] = 10 x 5 = 50
rows. Change the number "10" in the formula in A2 to suit the max expected
number of lines per source sheet (we had assumed this was 10 lines per source
sheet earlier)
Then .. in another sheet: Y (say),
Create a DV droplist in D1 to allow selection of key col's value to be
omitted, ie either: No Sales or Yes. The DV is created via Data > Validation
, Allow: List, Source: No Sales, Yes
Put in A2:
=IF(ROW(A1)>COUNT($D:$D),"",INDEX(X!A:A,MATCH(SMALL($D:$D,ROW(A1)),$D:$D,0)))
Copy A2 to C2
Put in D2:
=IF(OR(X!D2="",$D$1=""),"",IF(X!D2=$D$1,"",ROW()))
Then just select A2
2 and copy down to cover the max expected extent of
data in X. Cols A to C will return the required results* dynamically from X,
depending on the omission value selected in D1, with all results neatly
bunched at the top. *only "balance" lines from X will appear viz. lines
without the value selected in D1. Adapt & extend to suit ...
---
Sunny said:
Using a formula, I need to copy a table to a table omitting rows that meet
certain criteria (like no sales). This needs to be an 'automated' task since
I have some many.
I have worksheets for each of 50 states that list each county/parish, sales,
tax, tax rates. In another section of each of those worksheets I need to
list only those counties/parishes with sales and the resulting table should
include sales, tax, & tax rate.
Can this be done?
Thanks for your help in advance.
