I have 2 spreadsheets set up, one called caseloads and one called pipeline.

Basically there is one sheet on the caseloads workbook for each person.

What i want E7 to do is count how many times that persons name appears on the pipeline workbook in column A.

Anyone have any ideas how to do this?

To make it easier the pipeline workbook is currently called Book1


I hope this all makes sense

Perhaps try the COUNTIF function?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I tried a countif, but that still did not seem to work

the formula is the below, i have replaced the name below with %%%

=COUNTIF("[Book1.xlsx]Sheet1!$A:$A","%%%")

This does not work and comes up with this error -

The formula you typed contains an error.
- For information about fixing common formula problems, click Help.
- To get assistance in entering a function, click Function Wizard (Formulas tab, Function Library Group).
- If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark (').

Is the other workbook open?
Are you sur about the double quotes around the range?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

i'm not too sure, i'm using the function wizard for the COUNTIF, and it seems to automatically be doing that

Try single quotes like and make sure Book1 is open. I think you could use SUMPRODUCT instead which works with closed workbooks

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post