I have taken all your comments into account.
- extra column is added for credit notes
- dates are now correctly formatted
- Macro A converts the data, macro B adds the values in the credit note column, Macro C tranfers the values to sheet Consolidated
Run the macros in turn "A" then "B" then "C" (allows you to look at intermediate stages)
Notes
- date formatting issue resolved by utilizing "DateSerial" function
- avoided creating a secondary look-up table by building it into your table of values. Credit note column appears before invoice column because vlookup cannot look up the other way round
- proof total check added at end of macro A (there will always be a minor difference due to roundings, but the check should tell you if the difference exceeds 1 in total)
- "Evaluate" function used to sum the values in column "L"
(build the formula like you would in the spreadsheet, and wrap it in the "Evaluate" function to achieve what the formula would have done - in this case sums column "L")
- invoice/credit note matching based on perfect match (one for one) - would be very slow otherwise
- there is a check for overlapping dates when Macro C is run (have deliberately included 1 day overlap to prove it works - but ignore it and consolidate)
Bookmarks