Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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?

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 ""

=IF(COUNTIF($B$1:$B$5,"=FALSE")=5,"UP","DOWN")

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"

I think I've got it, I nested your if function. Thanks it works

very well. Now on to my next problem.

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:

=IF(COUNTIF($B$1:$B$5,"=Unprocessed")=5,"UP","DOWN")

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?

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

me.

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.

Related Topics:

- writing vba code to generate vba code
- writing vba code to generate vba code
- Convert code from excel 2000 vba to excel 2003 vba
- Placing VBA Code "Live" Into Another WkBook Via VBA
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Cannot use the VBA add table command of MS word in MS Excel VBA
- Column Change - VBA coding
- Need a VBA code to run a web query faster
- vba code samples
- "Contains" autofilter in VBA code
- Printing VBA Code
- VBA Code for pasting a set of formulas down a data range
- excel vba code
- VBA code to upload file in library on our site
- VBA code to convert all values of a column in a row separated by semi colon
- VBA Excel Coding for dynamically changing function range
- Need a VBA code
- Problem Bug VBA in Excel 2010- Impossible to get into the code anymore
- Code to make a whole column VBA
- Can any one help me with VBA Code?
- VBA Coding
- VBA code for different recipients in Outlook
- Can we make PC to Phone calls using Macro Code in Excel VBA
- need vba excel code
- vba code help