08-19-2020 11:27 PM
08-19-2020 11:27 PM
I'm trying to create a macro in Excel 2013 through Developer. I need to be able to have it take information from multiple cells on Sheet 1 for a date that is recorded in the A column with the months across the top on the A row. I need them to take information put in column F and add it together and put it in the proper cell on Sheet 4, where it can be shown as a total. I'm not sure how to write a macro that would be able to do that for multiple pieces of information that need to consolidated on different sheets in my workbook. Please help. I'm working with different dollar amounts entered on Sheet 1 that will be added and totaled on Sheet 4. I know that there are three wildcard symbols I can use. I'm thinking that I'll need more than one to get this done right.
08-20-2020 11:51 AM
I am not sure I understand fully. By "Row A" you meant row 1, correct? I am assuming you did.
For sheet 1, you put the date in the A column, and based on the fact it has a date, when you press "go" you want it to take the amounts in different cells and add them to the cell in F, then transfer that to a cell on sheet 4. Correct? Are all the amounts to be added in the same row? After the macro would run, what happens to the date on sheet 1? If you run the macro again, where would the next total be placed? Below the last one? On sheet 1, is there a column that has unique entries, like a part number or a name that is used only once?
If there is a unique identifier column on sheet 1 you may be able to do this with formulas.
08-20-2020 01:11 PM
08-20-2020 04:03 PM
@SqueakySneakersHere is a copy of the spreadsheet I'm working with. I have actual data that I need to assimilate. I just need to be pointed ion a direction that will allow me to transfer data between sheets as it's entered, instead of having to hit shortcut keys.
08-21-2020 06:09 AM
First thing I noticed was there is a "space" at the end of the word MAIN on the MAIN tab. I removed it from my copy. Spaces like that make formulas not work if they are not accounted for. On the Earnings tab I put a sumif equation in the first cell for you to show how it will add all of the 1/1/2020 amounts, and all the other amounts are for examples. The formula will drag and drop. On the Earnings tab, row 40, I added a year (a40) and the numbers represent the months. These are used to make the formulas work. You can hide this row. For February 29 I had to create a special formula that accounts for leap year so do not copy over that one. Let me know if this works.
There are circular references in this spreadsheet so you will need to fix those. Ex. J35 has a formula that sums itself. (=sum(h35:j35)). Others have this same error.