MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds


  Asked By: Bill    Date: Nov 01    Category: MS Office    Views: 2088

I have a workbook that has a list of approximately 2000 records. This
list is used for a vlookup in 5 additional workbooks. I am trying to
return information from the 5 workbooks back to the original.

Each employee works out of a separate workbook, and I want to know the
status of each of the records on the original workbook.

I have tried a vlookup to the 5 workbooks and that gives me the
current status but I need to consolidate it to one response.

I need it to give me if workbook 1 status is <> then refer back to
what the cell says as the status, if not then check workbook 2, and so
on. If not found in any of the workbooks then "UP or "". I tried a
nested IF function but I don't have enough resources to update the
sheet, so I can't tell if it works. Is there a vba code that I can
run that will alleviate all of the headache?



6 Answers Found

Answer #1    Answered By: Inez Wood     Answered On: Nov 01

Use 5 cells to reference to the values in the other five workbooks. The
write a formula that checks the status of those 5 cells. I am not sure what
your possible values are but as an example:

The 5 cells could return the other workbook's cell's values or FALSE if not
found. Then your formula could count how many times FALSE appears in the 5
cells (use COUNTIF). If the COUNTIF formula returns 5 then you could display
"UP" or ""


Answer #2    Answered By: Natasha Rivera     Answered On: Nov 01

I'm getting closer to what I need it
to do. How would this work with 3 statuses, "Open","Closed", if
neither of their appear in any of the five cells then "unprocessed"

Answer #3    Answered By: Saila Iqbal     Answered On: Nov 01

I think I've got it, I nested your if function. Thanks it works
very well. Now on to my next problem.

Answer #4    Answered By: Wallace Kelly     Answered On: Nov 01

Piecing together this message and your initial one I think you would like
the 5 cells to have one of three values "Open", "Closed", or "Unprocessed"
You then want one cell that checks the 5 cells for EITHER the Open or
Closed. If none of the cells have Open or Closed then the one cell should
read "UP".

The one cell's formula could be modified to read:

Is that more what you are looking for? How does the fact that one workbook's
value is Open or Closed change things? Do all of them need to read Open
and/or Closed at the same time? Or are you asking how to write the formula
for the 5 reference cells?

Answer #5    Answered By: Zack Thompson     Answered On: Nov 01

I was able to get what I needed by using the function you gave me. I
was really wondering if there was a way to use a vba  code to do it.
Unfortunately, every time I open the workbook, it freezes, I guess
because it is doing too much work, accessing 5 other workbooks, and
returning the information for 2000 records.

This workbook  is going to get larger so if there is no code  to use
then maybe I'll just have to have 18 workbooks, 5 original, 12 -one
for each month-and one for the result formulas. This seems like way
too much work.

If you have any suggestions, I'd appreciate anything you could give

Answer #6    Answered By: Krista Warren     Answered On: Nov 01

There is ALWAYS a way to use code. However, I don't have a clear enough
picture of what is actually going on to give a solution just yet. Let me ask
a few questions, gather some information, and then we can hammer out some
code to do this. I will start at the beginning and work our way towards the
final result.

You said: "accessing 5 other workbooks, and returning the information for
2000 records"

When one of the cells checks another workbook, what is it actually doing?
(possessing 2000 records in the other workbook, but what exactly?) Does it
give you an average, the most recent value, or something else?

The more detail you can give without divulging company secrets the better.

Didn't find what you were looking for? Find more on IF VBA Code Or get search suggestion and latest updates.