Hi All -
I'm working with a workbook that has 20 spreadsheets (tabs) and I want to search all 20 and extract a given text that is in 1 cell in each spreadsheet if the critera is met. I'm using the below formula to extract and sum (24hr) time in all 20 sheets, but I can't seem to modify it to work for text ?
=SUMPRODUCT(SUMIF(INDIRECT("'"&$N$3:$N$22&"'!E3:E36"),H4,INDIRECT("'"&$N$3:$N$22&"'!F3:F36")))
I have a table built ($N$3:$N$22) that matches the text (names) used in the spreadsheet tabs.
H4 is text and the SUMIF criteria.
I thought a simple approach would be to use a IF() statement:
=IF(INDIRECT("'"&$N$3:$N$22&"'!D3:D36=H4"),INDIRECT("'"&$N$3:$N$22&"'!A1"),"")
The above generates a #REF! error. Can the IF() function be used in a 3D formula along with Indirect() ?
How would I integrate the criteria (D4) inside the Indirect() formula ?
Many Thanks - first post !
Randy
I'm working with a workbook that has 20 spreadsheets (tabs) and I want to search all 20 and extract a given text that is in 1 cell in each spreadsheet if the critera is met. I'm using the below formula to extract and sum (24hr) time in all 20 sheets, but I can't seem to modify it to work for text ?
=SUMPRODUCT(SUMIF(INDIRECT("'"&$N$3:$N$22&"'!E3:E36"),H4,INDIRECT("'"&$N$3:$N$22&"'!F3:F36")))
I have a table built ($N$3:$N$22) that matches the text (names) used in the spreadsheet tabs.
H4 is text and the SUMIF criteria.
I thought a simple approach would be to use a IF() statement:
=IF(INDIRECT("'"&$N$3:$N$22&"'!D3:D36=H4"),INDIRECT("'"&$N$3:$N$22&"'!A1"),"")
The above generates a #REF! error. Can the IF() function be used in a 3D formula along with Indirect() ?
How would I integrate the criteria (D4) inside the Indirect() formula ?
Many Thanks - first post !
Randy
