Changing (setting) a parameter within a formula

persells

New Member
Joined
Apr 28, 2002
Messages
4
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top