I have a list of workbook names and the sheet names that I would like to use the counta in column B. I have used this formula to pull all of the data together so that I have a string of text that duplicates if I was to have written the formula for the individual book. i.e:
I have searched and found this code to bring the CONCAT into the Function:
The cells that I have =CONCAT, bring this text back and is added to the SUMPRODUCT formula:
The first cell brings back a correct amount of lines, but If I copy this down, I get the number 1 only, where there should be more than 5 and some are over 100 lines.
Can anyone see where I am going wrong please or have a simpler way of doing this maybe?
Thanks
Excel Formula:
=COUNTA('[ROYS - 01.06.2021 BBC.xlsx]01.06.2021 GBP '!$B2:$B500)
I have searched and found this code to bring the CONCAT into the Function:
Excel Formula:
=SUMPRODUCT(COUNTA(INDIRECT("'["&F3&"'!$B:$B")))
The cells that I have =CONCAT, bring this text back and is added to the SUMPRODUCT formula:
ROYS - 01.06.2021 BBC.xlsx]01.06.2021 GBP |
The first cell brings back a correct amount of lines, but If I copy this down, I get the number 1 only, where there should be more than 5 and some are over 100 lines.
Can anyone see where I am going wrong please or have a simpler way of doing this maybe?
Thanks