Check for a range of blank cells from one sheet to another and return a message

[Edgarvelez]

Hi all,
I have two sheets one named DashBoard and Sheet1
In Dashboard Starting I4 I will have a range of values (dynamic range).

If C3 in DashBoard equals "Sheet Line No." then look up the values in Dashboard starting at I7 on Sheet1 in Column A and check if Columns H I J K in the same row are blank,
I they are blank then return message box with the Line Number and the words Check OK
If any H I J K are not blank then return message box with the Line Number and the words Shipped.

Example:
I have entered starting in I4
4
5
6
7
8
21

My Message box should return as follows:

Sheet Line No. 4 OK
Sheet Line No. 5 Shipped
Sheet Line No. 6 Shipped
Sheet Line No. 7 Shipped
Sheet Line No. 8 Shipped
Sheet Line No. 21 Shipped

 

[Alex Blakenburg]

See if something like this would work for you. Put this on Dashboard on Row 4.

=IF(COUNTBLANK(INDEX(Sheet1!$H$2:$K$1000,MATCH(I4,Sheet1!$A$2:$A$1000,0),0))=4,"OK","Shipped")

The easiest way to make it dynamic is to convert Sheet1 to an Excel Table and then use Table Structured referencing in the formula.
Otherwise just set the number of rows to more than you are ever going to need.

 

[Edgarvelez]

Your suggestion worked and I have a code to add to each on the dashboard sheet.
What would be the code with sheet1 ending as shown below

 

[Alex Blakenburg]

Sorry I don't understand what you are asking.
What is your Sheet1 XL2BB trying to tell me ?

 

[Edgarvelez]

Apologies, I was asking for a code for if I entered the following in Dashboard
4
5
6
7
8
21
I could get the following results in sheet1

 

[Alex Blakenburg]

I thought you wanted the Dashboard updated from Sheet 1. Are you now saying you want Sheet 1 updated ?
You are showing me an empty dashboard so I don't understand what you are putting into Sheet1.

If you do want the dashboard updated and the formula is not doing that show me what the dashboard should look like after updating it.

 

[Edgarvelez]

Sorry for the confusion.
The formula provided for DashBoard
=IF(COUNTBLANK(INDEX(Sheet1!$H$2:$K$1000,MATCH(I4,Sheet1!$A$2:$A$1000,0),0))=4,"OK","Shipped")
Works great!!

I just needed a code to do something else as follows:
I entered values in DashBoard starting I4

This was the result I am looking for in sheet1

 

[Alex Blakenburg]

If you are saying that you want to filter Sheet 1 based on those numbers, you will need VBA for that.
I am in Australia and login off for the night If you want to keep it moving then I think it is a sufficiently different question to the original (ie a request for VBA code to filter the other sheet) that you can start a new thread for it.

 

[Edgarvelez]

ok and thanks for your help and will start another thread.

 

[Edgarvelez]

I posted but no one has replied