Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hard coded formula fix, VBA for column ordering
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hard coded formula fix, VBA for column ordering
It kinda depends on what you want to do. What is it you want to do? If
it's complicated, you get a complicated formula. You really haven't told us what you do want, just what you don't want. -- HTH, Barb Reinhardt "undrline via OfficeKB.com" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hard coded formula fix, VBA for column ordering
Hi,
First thing you could do to simplify this formula is to use range names, for example: Range Name Closed - Complaint'!Y1:Y65524 Y 'Closed - Complaint'!X1:X65534 X 'Closed - Complaint'!A1:A65534 A 'Closed - Complaint'!AF1:AF65534 AF 'Closed - Complaint'!K1:K65534 K 'Closed - Appeal'!A1:A65535 AA 'Closed - Appeal'!AL1:AL65535 AL 'Closed - Appeal'!C1:C65535 AC 'Closed - Appeal'!AK1:AK65535 AK 'Closed - Appeal'!$V$1:$V$65535 V This would simplify your formula to: =(SUMPRODUCT(--((ISNUMBER(SEARCH("Medicare",Y))=FALSE)*'(ISNUMBER (SEARCH("Medicaid",X))=FALSE)*(A<"")),--((AF="Fully Insured")+(AF="Shared Risk")+(AF="At Risk")),(K))+SUMPRODUCT(--((AA<"")*(AL<"MCRE")*(AL<"MCAID")*(AC"SND")*(A K="FI")*(AA="Provider Dispute")),(V)))/E6 You can modify ISNUMBER(SEARCH("Medicare",Y))=FALSE to read: NOT(ISNUMBER(SEARCH("Medicare",Y))) and another similar change. You can also create defined names for "Medicare" Me "Medicaid" Md "Fully Insured" FI "Shared Risk" SR "At Risk" AR "Provider Dispute" PD And a minor change (K) with K and (V) with V Reducing your formula to: '=(SUMPRODUCT(-- NOT(ISNUMBER(SEARCH(Me,Y)))*NOT(ISNUMBER(SEARCH(Md ,X)))*(A<"")),--((AF=FI)+(AF=SR)+(AF=AR)),K)+SUMPRODUCT(--((AA<"")*(AL<"MCRE")*(AL<"MCAID")*(AC"SND")*(A K="FI")*(AA=PD)),V))/E6 From here you can always define any part of the formula as a name, for example NOT(ISNUMBER(SEARCH(Me,Y))) could be defined as MeY. I may have made a mistake with a parenthesis in my sample formulas above, but the idea should be clear. 2. You could also consider making a custom VBA fuction out of the entire thing. A quick way to rearrange the columns would be to insert a row above the incoming data and enter a formula such as =MATCH(A2,Sheet2!$A1:$AZ1,0) where A1:AZ1 is the row on the summary sheet which contains the titles. This formula will return the numeric position of the matches, so a 1 would mean that column should be in the first position on the second sheet. Now do a Left to Right sort on the data in this sheet using the MATCH row as the sort row. (Data, Sort, Options, Sort left to right). Also, probably can do whatever you are trying to do with your formula with a DSUM or DCOUNT function, take a look at those. -- Thanks, Shane Devenshire "undrline via OfficeKB.com" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
do the same thing to multiple hard coded cells | Excel Worksheet Functions | |||
Convert Hard-coded cell values to constants | Excel Discussion (Misc queries) | |||
formula won't work unless column of data is a hard number | Excel Worksheet Functions | |||
References to open/hidden workbooks become hard-coded in formulas - 2003 | Excel Discussion (Misc queries) |