Hello experts!
I collect information submitted by local constituents using an excel form. I would like automate the tallying of information collected on tabs that may be inserted into the excel form (copies of the tab Part II-SubContracts) using VBA. I will also need to share this macro with co-workers. While I am sure there is an easier way to do this, this is what I would like to try to do. Any assistance is greatly appreciated.
A link to the excel file I'm using: https://www.dropbox.com/s/p966l27nvlo5mlk/ds4513%20test%20subcontract%20line%20totals.xlsx?dl=0
I started a mock up of what I'm looking for this to do.
On the Department Use Only tab, starting in cell L16 and going down (through L57), I want to insert the row headings below. It’s the same information repeated once with two blank rows in between them:
[TABLE="width: 58"]
<tbody>[TR]
[TD]1xx, 293[/TD]
[/TR]
[TR]
[TD]21x[/TD]
[/TR]
[TR]
[TD]22x[/TD]
[/TR]
[TR]
[TD]231[/TD]
[/TR]
[TR]
[TD]232[/TD]
[/TR]
[TR]
[TD]233[/TD]
[/TR]
[TR]
[TD]24x[/TD]
[/TR]
[TR]
[TD]25x[/TD]
[/TR]
[TR]
[TD]26x[/TD]
[/TR]
[TR]
[TD]27x[/TD]
[/TR]
[TR]
[TD]281[/TD]
[/TR]
[TR]
[TD]282[/TD]
[/TR]
[TR]
[TD]283[/TD]
[/TR]
[TR]
[TD]284[/TD]
[/TR]
[TR]
[TD]285[/TD]
[/TR]
[TR]
[TD]289[/TD]
[/TR]
[TR]
[TD]29x[/TD]
[/TR]
[TR]
[TD]3xx[/TD]
[/TR]
[TR]
[TD]4xx[/TD]
[/TR]
[TR]
[TD]45x[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1xx, 293[/TD]
[/TR]
[TR]
[TD]21x[/TD]
[/TR]
[TR]
[TD]22x[/TD]
[/TR]
[TR]
[TD]231[/TD]
[/TR]
[TR]
[TD]232[/TD]
[/TR]
[TR]
[TD]233[/TD]
[/TR]
[TR]
[TD]24x[/TD]
[/TR]
[TR]
[TD]25x[/TD]
[/TR]
[TR]
[TD]26x[/TD]
[/TR]
[TR]
[TD]27x[/TD]
[/TR]
[TR]
[TD]281[/TD]
[/TR]
[TR]
[TD]282[/TD]
[/TR]
[TR]
[TD]283[/TD]
[/TR]
[TR]
[TD]284[/TD]
[/TR]
[TR]
[TD]285[/TD]
[/TR]
[TR]
[TD]289[/TD]
[/TR]
[TR]
[TD]29x[/TD]
[/TR]
[TR]
[TD]3xx[/TD]
[/TR]
[TR]
[TD]4xx[/TD]
[/TR]
[TR]
[TD]45x[/TD]
[/TR]
</tbody>[/TABLE]
Then, starting in M15 and going across, I want to insert the formula =B40. In N15, =B41. In O15, =B42. This will continue across through column V. (see example) Finally, insert the word Totals inserted into W15.
Starting in M16, I would like to begin inserting formulas. These formulas will continue down through row 57 and across through column V.
Examples:
M16 =IF(ISERROR(INDIRECT("'"&$B$40&"'!AV6")),0,INDIRECT("'"&$B$40&"'!AV6"))
M17 =IF(ISERROR(INDIRECT("'"&$B$40&"'!AV7")),0,INDIRECT("'"&$B$40&"'!AV7"))
M18 =IF(ISERROR(INDIRECT("'"&$B$40&"'!AV8")),0,INDIRECT("'"&$B$40&"'!AV8"))
N16 =IF(ISERROR(INDIRECT("'"&$B$41&"'!AV6")),0,INDIRECT("'"&$B$41&"'!AV6"))
N17 =IF(ISERROR(INDIRECT("'"&$B$41&"'!AV7")),0,INDIRECT("'"&$B$41&"'!AV7"))
N18 =IF(ISERROR(INDIRECT("'"&$B$41&"'!AV8")),0,INDIRECT("'"&$B$41&"'!AV8"))
In cells W16 through W58, I’d like to sum across. For instance:
W16 =SUM(M16:V16)
W17 =SUM(M17:V17)
Finally, I’d like to format W16-W58 as a number (or accounting) with commas and 2 decimal places.
Any help is greatly appreciated. Thank you so much!
I collect information submitted by local constituents using an excel form. I would like automate the tallying of information collected on tabs that may be inserted into the excel form (copies of the tab Part II-SubContracts) using VBA. I will also need to share this macro with co-workers. While I am sure there is an easier way to do this, this is what I would like to try to do. Any assistance is greatly appreciated.
A link to the excel file I'm using: https://www.dropbox.com/s/p966l27nvlo5mlk/ds4513%20test%20subcontract%20line%20totals.xlsx?dl=0
I started a mock up of what I'm looking for this to do.
On the Department Use Only tab, starting in cell L16 and going down (through L57), I want to insert the row headings below. It’s the same information repeated once with two blank rows in between them:
[TABLE="width: 58"]
<tbody>[TR]
[TD]1xx, 293[/TD]
[/TR]
[TR]
[TD]21x[/TD]
[/TR]
[TR]
[TD]22x[/TD]
[/TR]
[TR]
[TD]231[/TD]
[/TR]
[TR]
[TD]232[/TD]
[/TR]
[TR]
[TD]233[/TD]
[/TR]
[TR]
[TD]24x[/TD]
[/TR]
[TR]
[TD]25x[/TD]
[/TR]
[TR]
[TD]26x[/TD]
[/TR]
[TR]
[TD]27x[/TD]
[/TR]
[TR]
[TD]281[/TD]
[/TR]
[TR]
[TD]282[/TD]
[/TR]
[TR]
[TD]283[/TD]
[/TR]
[TR]
[TD]284[/TD]
[/TR]
[TR]
[TD]285[/TD]
[/TR]
[TR]
[TD]289[/TD]
[/TR]
[TR]
[TD]29x[/TD]
[/TR]
[TR]
[TD]3xx[/TD]
[/TR]
[TR]
[TD]4xx[/TD]
[/TR]
[TR]
[TD]45x[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]1xx, 293[/TD]
[/TR]
[TR]
[TD]21x[/TD]
[/TR]
[TR]
[TD]22x[/TD]
[/TR]
[TR]
[TD]231[/TD]
[/TR]
[TR]
[TD]232[/TD]
[/TR]
[TR]
[TD]233[/TD]
[/TR]
[TR]
[TD]24x[/TD]
[/TR]
[TR]
[TD]25x[/TD]
[/TR]
[TR]
[TD]26x[/TD]
[/TR]
[TR]
[TD]27x[/TD]
[/TR]
[TR]
[TD]281[/TD]
[/TR]
[TR]
[TD]282[/TD]
[/TR]
[TR]
[TD]283[/TD]
[/TR]
[TR]
[TD]284[/TD]
[/TR]
[TR]
[TD]285[/TD]
[/TR]
[TR]
[TD]289[/TD]
[/TR]
[TR]
[TD]29x[/TD]
[/TR]
[TR]
[TD]3xx[/TD]
[/TR]
[TR]
[TD]4xx[/TD]
[/TR]
[TR]
[TD]45x[/TD]
[/TR]
</tbody>[/TABLE]
Then, starting in M15 and going across, I want to insert the formula =B40. In N15, =B41. In O15, =B42. This will continue across through column V. (see example) Finally, insert the word Totals inserted into W15.
Starting in M16, I would like to begin inserting formulas. These formulas will continue down through row 57 and across through column V.
Examples:
M16 =IF(ISERROR(INDIRECT("'"&$B$40&"'!AV6")),0,INDIRECT("'"&$B$40&"'!AV6"))
M17 =IF(ISERROR(INDIRECT("'"&$B$40&"'!AV7")),0,INDIRECT("'"&$B$40&"'!AV7"))
M18 =IF(ISERROR(INDIRECT("'"&$B$40&"'!AV8")),0,INDIRECT("'"&$B$40&"'!AV8"))
N16 =IF(ISERROR(INDIRECT("'"&$B$41&"'!AV6")),0,INDIRECT("'"&$B$41&"'!AV6"))
N17 =IF(ISERROR(INDIRECT("'"&$B$41&"'!AV7")),0,INDIRECT("'"&$B$41&"'!AV7"))
N18 =IF(ISERROR(INDIRECT("'"&$B$41&"'!AV8")),0,INDIRECT("'"&$B$41&"'!AV8"))
In cells W16 through W58, I’d like to sum across. For instance:
W16 =SUM(M16:V16)
W17 =SUM(M17:V17)
Finally, I’d like to format W16-W58 as a number (or accounting) with commas and 2 decimal places.
Any help is greatly appreciated. Thank you so much!