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:
<tbody>
</tbody>
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:
| 1xx, 293 |
| 21x |
| 22x |
| 231 |
| 232 |
| 233 |
| 24x |
| 25x |
| 26x |
| 27x |
| 281 |
| 282 |
| 283 |
| 284 |
| 285 |
| 289 |
| 29x |
| 3xx |
| 4xx |
| 45x |
| 1xx, 293 |
| 21x |
| 22x |
| 231 |
| 232 |
| 233 |
| 24x |
| 25x |
| 26x |
| 27x |
| 281 |
| 282 |
| 283 |
| 284 |
| 285 |
| 289 |
| 29x |
| 3xx |
| 4xx |
| 45x |
<tbody>
</tbody>
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!
