I'm attempting to write a function in google sheets that sums an INDEX INDIRECT MATCH MATCH value across multiple sheets - essentially the total number of hours each employee has allocated towards each client.
I took a first stab at it in cell D2 but I feel like there's gotta be a much more elegant way of writing it rather than copying the current function, adding a "+", then adjusting the tab name reference - especially bc the sheet list is growing by the week so it would be especially nice to auto-update as current weeks are added.
Please see the sample sheet here: https://docs.google.com/spreadsheets/d/1qEAJ1UcNQPD-doUaD3kfZgpDz2phpcXHUJWpVE01I10/edit?usp=sharing
(all sensitive names and clients have been replaced with dummy data)
Thanks so much for your help!
=IF(IFERROR(INDEX(INDIRECT("'"&$B2&"'!"&$A$3),MATCH('client allocation'!E$1,INDIRECT("'"&$B2&"'!"&$A$4),0)),"")=0,"",IFERROR(INDEX(INDIRECT("'"&$B2&"'!"&$A$3),MATCH('client allocation'!E$1,INDIRECT("'"&$B2&"'!"&$A$4),0)),""))
IFERROR()and be done with it. - Neven Suboticadjusting the tab name reference? All the IFs in your function have the same tab name. How would this change? - Mateo Randwolf