Excel 2007 VLOOKUP formula question

Question

Hi,

I am having some difficulties trying to fine a way using VLOOKUP to get the result of the content of a range. Here is my problem:

In my workbook I have a main menu sheet name (Main) where the cell address to receive the response is in "F6" in the worksheet within the same workbook, the sheet named (201) a range of status can contain the following value: either "OPEN" or "CLOSED" now. should any of the cell in this range contains a "CLOSED" Statusfirst I need to change the status of all the cells within that range to "CLOSED" so the main menu sheet (Main) address "F6" would result in "CLOSED" but if nothing is found with the range then return a BLANK otherwise a status of "OPEN" must be set for the cell "F6" in Main sheet. The range in sheet (201) is E6:E25

How to do it? I could not find a way to do that. Your help will be very appreciated. By the way, I am new to vba and need someone to write the formula or a macro to do that.

P.S.: The underlined and bold statement can be skipped  since the objective is to get as a result if one is closed and make sure that F6 gets the status "CLOSED"

Thanks in advance.

Chuck

Answer 1

You can't unless you write a  macro.  The problem is you need to change a value before you do the lookup which will cause a "circular formula" which will create an error.

You can do everything in a macro.  I think in this case it is better to use a formula in F6.  run the macro below which will change the data in sheet 201.  After the macro is run the formula in F6 will be correct.

Use the formula in F6

=IF(ISBLANK('201'!E6:E25),"",IF(COUNTIF('201'!E6:E25,"OPEN")<>0,"OPEN","CLOSED"))

Use macro

Sub ChangeStatus()

With Sheets("201")
   Set LookupRange = .Range("E6:E25")

   Set ResponseCell = .Range("F6")
   Set c = LookupRange.Find(What:="CLOSED", _
      lookat:=xlWhole, LookIn:=xlValues)
   If Not c Is Nothing Then
      LookupRange.Value = "CLOSED"
   End If
End With
End Sub

---

jdweng

Answer 2

Not sure I follow but if the only objective is to return the following to F6 depending on the contents in 201:E6:E25

Blank: if all cells in are blank
else
"OPEN": if all cells contain "OPEN"
else
"CLOSED: any other set of contents

=IF(COUNTA('201'!E6:E25)=0,"blank",IF(ROWS('201'!E6:E25)=COUNTIF('201'!E6:E25,"OPEN"),"OPEN","CLOSED"))

After testing change "bank" to ""
If the reference will definitely always be E6:E25 could change ROWS('201'!E6:E25) to 20
Might be easier to Name E6:E25

Peter Thornton

Answer 3

Thanks Joel,

I will give it a try and will let you know.

Cheers!

Chuck

Answer 4

Thanks peter, I will let you know after testing it.

Cheers!

Chuck

Answer 5

Hi Joel and Peter,Thanks for your help.

I have tested the formula but had a problem I have finally used Joel's formula and adjusted it to fit what I am trying to get as a result and now works perfectly. I have adapted the formula and applied it to each of the rows where necessary. Here is the adjustment I made:

=IF(COUNTIF('101'!E6:E25,"CLOSED")<>0,"CLOSED",IF(COUNTIF('101'!E6:E25,"OPEN")<>0,"OPEN",""))

Thank you and will mark it as answered.

Cheers!

Chuck