i have 1 workbook with about 20 sheets, i need to search this workbook for a name in one column on each sheet. i made a list of the sheets in the 2nd workbook in column a. i tried this formula in column b, but i can't get it to recognize the cell in my formula:

=SUM(SUMIF(INDIRECT("'[cfshows.xls]&A2'!U:U"),"Brown, Bob"))

i got it to work... kind of.. with:

=COUNTIF(INDIRECT("'[cfshows.xls]"&A38&"'!U:U"),"=Brown, Bob")

but it always returns 1, even if bob is on there 20 times

On Wednesday, May 15, 2013 10:38:17 AM UTC-7, JoshBrunz wrote:
i have 1 workbook with about 20 sheets, i need to search this workbook

for a name in one column on each sheet. i made a list of the sheets in

the 2nd workbook in column a. i tried this formula in column b, but i

can't get it to recognize the cell in my formula:



=SUM(SUMIF(INDIRECT("'[cfshows.xls]&A2'!U:U"),"Brown, Bob"))









--

JoshBrunz

Hi Josh,

What do you want the formula to return once it finds the name in one of the sheets?

Regards,
Howard

What do you want the formula to return once it finds the name in one of the sheets?



Regards,

Howard

My bad, looks like you want a count of the name from all the columns.

Howard

just a count of how many times the name bob brown appears in the U column on each page

just a count of how many times the name bob brown appears in the U

column on each page

JoshBrunz

Well, one way, on each sheet in a discreet cell enter this formula, (I used F1 on each sheet). Adjust formula to that sheet's name.

=COUNTIF(Sheet2!U:U,Sheet1!A2)

Bob Brown is in cell A2 of sheet1. And A2 could be a drop down with other names.

Then on sheet1 this formula =SUM(Sheet2:Sheet6!F1)

Where sheet2 is the first sheet and sheet6 is the last sheet.

Regards,
Howard