Evening all, I have spent hours searching threads for an answer to this but to no avail.
I have a workbook with 10 sheets of cost codes, each with a data dump which is a list of specific costs in the cost code. I have a formula in there which picks out the unique suppliers in each dump and also SUMS the value for each supplier.
Now on a summary sheet, I have the following formula which works and returns the cost code (sheet name(s)) the chosen Supplier appears in.
=IFERROR(INDEX(SheetList,SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&SheetList&"'!G2:G14"),$B$4)),ROW(INDIRECT("1:"&COUNTIFS(SheetList,"?*")))),ROWS($A$1:A1))),"")
What I am struggling to create is the formula to tell me what the value is for the chosen supplier on each tab.
I know it possibly sounds complicated, if i can work out how to upload a simple version of my spreadsheet i will do so but any initial suggestions would be great as I have been trying different things all day.
Thanks
I have a workbook with 10 sheets of cost codes, each with a data dump which is a list of specific costs in the cost code. I have a formula in there which picks out the unique suppliers in each dump and also SUMS the value for each supplier.
Now on a summary sheet, I have the following formula which works and returns the cost code (sheet name(s)) the chosen Supplier appears in.
=IFERROR(INDEX(SheetList,SMALL(IF(ISNUMBER(1/COUNTIFS(INDIRECT("'"&SheetList&"'!G2:G14"),$B$4)),ROW(INDIRECT("1:"&COUNTIFS(SheetList,"?*")))),ROWS($A$1:A1))),"")
What I am struggling to create is the formula to tell me what the value is for the chosen supplier on each tab.
I know it possibly sounds complicated, if i can work out how to upload a simple version of my spreadsheet i will do so but any initial suggestions would be great as I have been trying different things all day.
Thanks