Changing (setting) a parameter within a formula

[persells]

I have a report that I get in "Excel" format, that is it looks like the printed report but "fires" into cells. I need to look at certain portions of the report which move from report to report in their row location (always the same column). For instance, the report refences a base unit in column c and I can run MATCH and find it starts on row 5 and with MATCH & find it ends on row 48 this month. In column E is a reference that I need to look up in the A column. If I can range the beginning and end of the particular base unit, it's a DSUM and there are 40+ of these to look up. Currently, I have to go into the report and manually search and replace which takes 2 hours to put in the correct row references. How can this process be automated? Neither ADDRESS nor MATCH will let me use them within the DSUM formula.
Dana

[Aladin Akyurek]

On 2002-04-29 14:51, persells wrote:
I have a report that I get in "Excel" format, that is it looks like the printed report but "fires" into cells. I need to look at certain portions of the report which move from report to report in their row location (always the same column). For instance, the report refences a base unit in column c and I can run MATCH and find it starts on row 5 and with MATCH & find it ends on row 48 this month. In column E is a reference that I need to look up in the A column. If I can range the beginning and end of the particular base unit, it's a DSUM and there are 40+ of these to look up. Currently, I have to go into the report and manually search and replace which takes 2 hours to put in the correct row references. How can this process be automated? Neither ADDRESS nor MATCH will let me use them within the DSUM formula.
Dana

Care to post your DSUM formula along with criteria and "database" it refers to?

Aladin

[persells]

The dsum is =IF(COUNTIF('Mar WC GL'!$B$1:$B$20005,$A3)<1,0,(IF(COUNTIF('Mar WC GL'!$C$20000:$C$20001,Convert!$A$4)<1,0,DSUM('Mar WC GL'!$A$20000:$C$20001,1,Convert!$P$1:$P$2))+(IF(COUNTIF('Mar WC GL'!$C$20000:$C$20001,Convert!$A$5)<1,0,DSUM('Mar WC GL'!$A$20000:$C$20001,1,Convert!$Q$1:$Q$2))+(IF(COUNTIF('Mar WC GL'!$C$20000:$C$20001,Convert!$A$6)<1,0,DSUM('Mar WC GL'!$A$20000:$C$20001,1,Convert!$R$1:$R$2))+(IF(COUNTIF('Mar WC GL'!$C$20000:$C$20001,Convert!$A$7)<1,0,DSUM('Mar WC GL'!$A$20000:$C$20001,1,Convert!$S$1:$S$2))+(IF(COUNTIF('Mar WC GL'!$C$20000:$C$20001,Convert!$A$8)<1,0,DSUM('Mar WC GL'!$A$20000:$C$20001,1,Convert!$T$1:$T$2))+(IF(COUNTIF('Mar WC GL'!$C$20000:$C$20001,Convert!$A$9)<1,0,DSUM('Mar WC GL'!$A$20000:$C$20001,1,Convert!$U$1:$U$2)))))))))
What I am trying to do is automate changing the 20000 and 20001 in the above formula to the various row numbers as the months change without having to perform a manual search and replace. For each month and each $A3 at the very beginning, there are 7 rows of similar formulas. For A3 this month, the beginning is at 8 and the end is at 56. Last month it was 8 and 62. This changes each month. A4 started at 57 in March and ended at 122. Last month, it was 62 and 155. I keep thinking that there should be a search and replace method to automate this as the start and end rows are determined in another 2 rows. Currently, I am spending 2+ hours running a search and replace manually and we will probably get into more of this type "stuff" later on.
I've tried a macro without luck and you can't put either MATCH or ADDRESS in the DSUM either. My guess is moving to an array formula or a VBA script if it's possible.
Dana

[Aladin Akyurek]

Dana,

Quite a set up.

What kind of data

'Mar WC GL'!$A$1:$A$20005
'Mar WC GL'!$B$1:$B$20005
'Mar WC GL'!$C$1:$C$20005

each does hold? Dates, Numbers, or Text?

Is the data somehow sorted, albeit constantly changing?

Aladin

[persells]

Column B is the base number for a franchise. The report is grouped by franchises with a varying number of rows per franchise by month. Column C is a billing type which can be text or number and which is a known value if present for that franchise that month. Column A is the amount for the month for that billing type for that franchise.
There will only be one instance of a billing type for any one franchise in one month. However, a billing type will appear many times in the entire report. Therefore, the effort is currently related to limiting the range to that for the particular franchise. In 2 additional, different columns, I use match to find those beginning and ending rows and manually make the change in range. This is time consuming and there are other instances in which we will need to perform similar calculations on different reports. Therefore, it's worth some trouble to find a solution.
Dana

[ This Message was edited by: persells on 2002-04-30 06:24 ]

[persells]

Column B is the franchise # which is a three digit number/text. All of these are known. It is possible there will be nothing for a franchise.
Column C is a billing type. All of these are known but they are not all used in every franchise every month. The list of those types is in "Convert."
Column A is the amount of the billing for a billing type.
I know that the length of the report is never (so far) more than 20000 rows.
What I am trying to do is automate the process of determining the range for each franchise as there are seven rows of similar calculations for each franchise.
Dana

[Aladin Akyurek]

On 2002-04-30 06:43, persells wrote:
Column B is the franchise # which is a three digit number/text. All of these are known. It is possible there will be nothing for a franchise.
Column C is a billing type. All of these are known but they are not all used in every franchise every month. The list of those types is in "Convert."
Column A is the amount of the billing for a billing type.
I know that the length of the report is never (so far) more than 20000 rows.
What I am trying to do is automate the process of determining the range for each franchise as there are seven rows of similar calculations for each franchise.
Dana

Dana,

I have the impression that we have some limit to D-functions in that you can't specify a sub-database within a larger database as a first argument, say, to DSUM.

Is it possible that you send me a cut-down version of your WB?

Regards,

Aladin