I set up Excel "templates" for some pretty standard database extracts. It has
a summary sheet, and a data sheet. I paste new data in the data sheet, and
voila, all the formulas update in the summary sheet. The internal customers
love this, because it allows them to use whatever meshuggah format for
summaries they want, rather than a simple pivot table. But, the cells in my
summary end up with formulas like this:

=(SUMPRODUCT(--((ISNUMBER(SEARCH("Medicare",'Closed - Complaint'!Y1:Y65534))
=FALSE)*(ISNUMBER(SEARCH("Medicaid",'Closed - Complaint'!X1:X65534))=FALSE)*
('Closed - Complaint'!A1:A65534<"")),--(('Closed - Complaint'!AF1:
AF65534="Fully Insured")+('Closed - Complaint'!AF1:AF65534="Shared Risk")+
('Closed - Complaint'!AF1:AF65534="At Risk")),('Closed - Complaint'!K1:K65534)
)+SUMPRODUCT(--(('Closed - Appeal'!A1:A65535<"")*('Closed - Appeal'!AL1:
AL65535<"MCRE")*('Closed - Appeal'!AL1:AL65535<"MCAID")*('Closed - Appeal'!
C1:C65535<"SND")*('Closed - Appeal'!AK1:AK65535="FI")*('Closed - Appeal'!$A
$1:$A$65535="Provider Dispute")),('Closed - Appeal'!$V$1:$V$65535)))/E6

Firstly, there's gotta be a better way than such a convoluted formula. I mean,
really, it's like I'm reproducing a SQL SELECT statement in Excel-eze, with
only 1,024 characters to get the job done. How would I do this with VLookup,
for instance?

Secondly, if my columns are out of order, it's been easier to rearrange the
100+ columns and put them in order, rather than edit all the formulas, and
possibly have something break. Let's say I were to continue using the formula
(in other words, let's say we ignore my "firstly" above) ... is there a way
to paste the dataset on a temporary worksheet, and rearrange the columns
based on the header row, to be in the same order as the header-only columns
on my data page? I'm assuming it'd take some VBA. Something like, "start on
[prompt for sheet name in inputbox], A1; if the header cell (A1) matches a
cell on the other sheet [again, prompt for destination sheet name in inputbox]
A1 thru IV1, copy the cell below through 65535, and paste it under the
corresponding cell on the other worksheet (if there's more than one, paste it
under all of them); repeat for the column to the right; cycle through all
nonblank columns on first sheet." Oh, and don't take an hour or crash or run
interference with the Office clipboard.

Hey, answering one or both of my questions would help. Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200809/1