Making a "summary worksheet"

T

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
 
L

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.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top