Is there a VBA option to do the same thing an excel SUMPRODUCT(SUMIF(INDIRECT)) formula does but that dynamically works with a changing number of worksheet names...or better still...is there a way to code/send/call 10 sheets from 1 workbook and 1 sheet from a 2nd workbook onto a 3rd workbook and collate everything based on the unique identifier that is common to all worksheets within all 3 workbooks?
I've been self-learning VBA for 6 months and enjoying myself immensely, but I'm currently trying to automate a Payroll Summary and have a snag that I have not been able to solve in 8 days of trying ?
I have 1 workbook of 52+ tabs, Sheet Names mainly LongDates corresponding to every Sunday in the UK tax year (used for weekly time sheet input). A 2nd workbook has 12+ tabs, with the sheet names mainly Mmm months (used for monthly expenses input). Both workbooks have a couple of additional sheets containing employee information and both are populated via UserForms which work fine (yay)
A 3rd 'Summary' workbook has 12+ tabs and MMM month Sheet Names and corresponding column headers to the other 2 workbooks.
I am trying to find a way to collate the respective values for each person from specific sheets on the other two workbooks as a summary for each month.
Each time sheet has 4 column values that need to feed into one summary month and a further 6 column values that need to feed into a different summary month, so each month I need to access 10 different time sheet worksheets and one expense worksheet.
Unable to find a way to collate directly to/from my Summary workbook I eventually found the following formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&HRSshtList&"'!B3:B60"),M5,INDIRECT("'"&HRSshtList&"'!E3:E60"))).
This I placed on a worksheet within the 52+ tab workbook and it works fine when a fixed named range list or fixed table is used, but it won't work with a dynamically changing named range or table.
A coded 'Run' button updates the respective weeks when clicked...and all my efforts to then put those updated week dates into a dynamic named range (using OFFSET) or into a table has created #REF across the formula cells.
I am completely stuck and would be so grateful for any help anyone can offer...many thanks in advance...and in retrospect of all the help I've gleaned from you all so far too.
I've been self-learning VBA for 6 months and enjoying myself immensely, but I'm currently trying to automate a Payroll Summary and have a snag that I have not been able to solve in 8 days of trying ?
I have 1 workbook of 52+ tabs, Sheet Names mainly LongDates corresponding to every Sunday in the UK tax year (used for weekly time sheet input). A 2nd workbook has 12+ tabs, with the sheet names mainly Mmm months (used for monthly expenses input). Both workbooks have a couple of additional sheets containing employee information and both are populated via UserForms which work fine (yay)
A 3rd 'Summary' workbook has 12+ tabs and MMM month Sheet Names and corresponding column headers to the other 2 workbooks.
I am trying to find a way to collate the respective values for each person from specific sheets on the other two workbooks as a summary for each month.
Each time sheet has 4 column values that need to feed into one summary month and a further 6 column values that need to feed into a different summary month, so each month I need to access 10 different time sheet worksheets and one expense worksheet.
Unable to find a way to collate directly to/from my Summary workbook I eventually found the following formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&HRSshtList&"'!B3:B60"),M5,INDIRECT("'"&HRSshtList&"'!E3:E60"))).
This I placed on a worksheet within the 52+ tab workbook and it works fine when a fixed named range list or fixed table is used, but it won't work with a dynamically changing named range or table.
A coded 'Run' button updates the respective weeks when clicked...and all my efforts to then put those updated week dates into a dynamic named range (using OFFSET) or into a table has created #REF across the formula cells.
I am completely stuck and would be so grateful for any help anyone can offer...many thanks in advance...and in retrospect of all the help I've gleaned from you all so far too.
