Making a "summary worksheet"

[Torfinn Brokke]

Hello!

I have a task that I wish to accomplish: Say that I have three Excel
spreadheets representing Person A, Person B and Person C, and in each of
these there is a set of ten outputs in a column ("VALID", "PENDING" or
"EXPIRED"). say from F1 to F10. What would I have to do to make a separate
spreadsheet with one output each for Person A, Person B and Person C that
would give the output "VALID" if all outputs in the sheet of that particular
person is "VALID", "PENDING" if one of the values is "PENDING" and "EXPIRED"
if one of the values is expired?

Perhaps this is easier to understand if I say that my purpose is to make one
single spreadsheet that can gather data from any number of separate
spreadsheets so as to be able to see at a glance if any given person has any
licenses that have expired. That is, I need to find a function that can read
the text output of any number of cells in a column and check for any
occurences of "PENDING" and "EXPIRED".

Thanks in advance for any help!


Best regards,
Torfinn

 

[Luke M]

Lets say you have name of person (which is the same as their sheet name) in
cell A2. In B2:

=IF(COUNTIF(INDIRECT("'"&A2&"'!F:F"),"EXPIRED")>0,"EXPIRED",IF(COUNTIF(INDIRECT("'"&A2&"'!F:F"),"PENDING")>0,"PENDING","VALID")

This is now a general formula that can reference any amount of rows in
column F, and can look in any sheet that is designated in cell A2.

 

[Torfinn Brokke]

Luke,

Thank you, that did the trick! Now, for the one million dollar question:
Will this also work if the different persons have their own separate .XLSX
files, and the summary worksheet itself is in another .XLSX?

Thanks again!


Best regards,
Torfinn