VLOOKUP QUESTION

could you please explain or show me the formula... the lookup_value will point to following merged cells (E-J 22)
thanks

bradfork,
Could you please post your formula and describe which cells are merged?

"the lookup value will point to the following merged cells"     Do you mean the formula is placed in that merged range? Or the first parameter of the VLOOKUP is from that merged range. Or that you are looking to return a value from a column in that range?

Here is a sample workbook showing how you handle the column references in a VLOOKUP formula http://home.mchsi.com/~byundt/VLOOKUPwithMergedCellsQ21321490.xls

Brad

=IF(ISERROR(VLOOKUP(c22,Sheet2!A2:D236,2,FALSE)),"",VLOOKUP(c22,Sheet2!A2:D236,2,FALSE))
I'm not going to used the merged cells but excel does not like this formula either

bradfork,
That formula is working perfectly in my test workbook. I'm thinking maybe the problem is unique to your workbook, and that it would be best if I could take a look at your file.

Many ISP allow their customers to create a personal web page. If so, you can post your file there, and then put the link in a Comment. This is the approach that I take.

You can post your workbook for free at Geocities:  http://geocities.yahoo.com/ps/learn2/HowItWorks4_Free.html
Another popular free site is AngelFire http://www.angelfire.lycos.com/doc/subscriptions/index.html

If none of the above work out for you, I would be willing to post a link to your file if you e-mail it to me. My address at the bottom of my Experts-Exchange profile--just double-click my screen name at the top of this comment.

Brad

Brad,
Here's the link to your file. I'll be taking a look at a little later, but wanted to give anyone else looking at this question a chance to work on it in the mean time.
http://home.mchsi.com/~byundt/VLOOKUPwithMergedCells2Q21321490.xlt

Brad

Brad,
The formula in cell D22 returns #N/A error value because C22 is blank.
=VLOOKUP(C22,Sheet2!A2:D236,2,FALSE)

This formula avoids the error value. Column D will be filled when the user chooses something in the data validation dropdown in column C.
=IF(ISERROR(VLOOKUP(C22,Sheet2!A2:D236,2,FALSE)),"",VLOOKUP(c22,Sheet2!A2:D236,2,FALSE))


Brad

Thanks but when I enter the formula I get an error and this is high lighted "C22,Sheet2"

Brad,
I think you are seeing the warning icon (! inside a manilla colored diamond) when Excel 2003 sees a formula in the active cell that returns an error value. You can turn this "feature" off with the Tools...Options...Error Checking menu item by unchecking the box for "Enable background error checking".

Brad

I just tried it again and it says the formulas you typed contains and error and highlights the same section "C22,Sheet2"

Brad,
Which version of Excel are you using? I'd like to open the workbook in the same version as you.

Brad

2003

bradfork,
In my copy of Excel 2003 SP1, I see the warning message "A value is not available to the formula or function."  I'm not seeing any reference to "C22,Sheet2". These comments apply apply to the file just as you mailed it.

You can see the service pack level in the Help...About Excel menu item.


Incidently, if you are protecting the workbook to hide Sheet2 and Sheet3--the user can navigate to those sheets by clicking the dropdown arrow to the right of the Address Bar (the horizontal white rectangle above the intersection of the row and column headers) and choosing inventory, DESCRIPTION, ITEM_NO or SALES_ADVISOR from the resulting dropdown.

The following two macros will do a more effective job of protecting the workbook. The first one runs automatically when the workbook opens, and hides the second and third worksheets. The other macro unhides Sheet2 and Sheet3 so you can update the data. They need to go in the ThisWorkbook code pane.

Both macros can be run from the ALT + F8 macro selector--but won't appear in the list of available macros because of the Private keyword. The trick is you need to type in the command in the "Macro name" field in the ALT + F8 macro selector--either ThisWorkbook!Workbook_Open or ThisWorkbook!UpdateData.

Private Sub Workbook_Open()
Worksheets(2).Visible = xlVeryHidden
Worksheets(3).Visible = xlVeryHidden
End Sub

Private Sub UpdateData()
Worksheets(2).Visible = True
Worksheets(3).Visible = True
End Sub

To install the sub in the code pane for ThisWorkbook:
1) ALT + F11 to open the VBA Editor
2) If you don't see a list of VBA projects on the left, then CTRL + R to open the Project Explorer
3) In the Project Explorer window, double-click ThisWorkbook to open its code pane
4) Paste the suggested code in the resulting module sheet
5) ALT + F11 to return to the spreadsheet

Brad

Brad
I have the same SP1 something starnge is going on with my excel then

Brad,
Did you try unchecking the box for "Enable background error checking"?

Brad

yes

Brad
I installe dthe suggested ocde and when I started the application I got an error pointing to Worksheets(2).Visible = xlVeryHidden

actual error "run-time error '1004' Unabel to set the visiable property of the worksheet class"
hope this helps

Brad,
Did you unprotect the workbook first? Do that, then save your file, close it and reopen it.

Brad

Brad
That fixed the problem with the runtime error..Thanks any more suggestions for the vlookup error with the function:
=IF(ISERROR(VLOOKUP(C22,Sheet2!A2:D236,2,FALSE)),"",VLOOKUP(c22,Sheet2!A2:D236,2,FALSE))