Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menus - 2nd Problem
Okay! We are making some progress, but I hit another roadblock.
Here is the formula exactly as it is entered in the first row in cell F78. =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,BK68:BK86 ,BK68:BK86)) So when I scroll to "0.25" in cell D78 I get "0.146" in cell F78 GREAT!!! Now, if I go down 1 row to cell D79 and copy the same list for a drop down menu and type the following formula in cell F79: =IF(ISBLANK(D79),"",VLOOKUP(D79,LU_ktype,BK68:BK86 ,BK68:BK86)) I get a reference error. I am referencing the same list name so why won't it just do the same as the cell above? Thanks , Neil |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menus - 2nd Problem
It looks like there might be a misunderstanding of the way the VLOOKUP
function works: =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) Whe -lookup_value = Value to look for -table_array = List of values to seach for that value (uses 1st col, only) -col_index_num = Column that holds the value to return if lookup val is found. Refers to the relative column number in the lookup range Example: if the lookup range is D1:E10 and you want the values in column E, then that is the 2nd column in the lookup range. -range_lookup = FALSE: for exact matches, True: for approximate matches Consequently, in your formula =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,BK68:BK86 ,BK68:BK86)) I think you might try: =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,2,0)) Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "Neil M" wrote: Okay! We are making some progress, but I hit another roadblock. Here is the formula exactly as it is entered in the first row in cell F78. =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,BK68:BK86 ,BK68:BK86)) So when I scroll to "0.25" in cell D78 I get "0.146" in cell F78 GREAT!!! Now, if I go down 1 row to cell D79 and copy the same list for a drop down menu and type the following formula in cell F79: =IF(ISBLANK(D79),"",VLOOKUP(D79,LU_ktype,BK68:BK86 ,BK68:BK86)) I get a reference error. I am referencing the same list name so why won't it just do the same as the cell above? Thanks , Neil |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menus - 2nd Problem
Okay, now I got it. I assumed the 2,0 were just variables that you plugged in
because you did not know my reference cells. I didn't realize it was part of the command/formula and thought I should selected the range of cells. Ron, thank you very much. This spreadsheet is worth a big promotion for me and I can't tell you how much I appreciate your help. Neil "Ron Coderre" wrote: It looks like there might be a misunderstanding of the way the VLOOKUP function works: =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) Whe -lookup_value = Value to look for -table_array = List of values to seach for that value (uses 1st col, only) -col_index_num = Column that holds the value to return if lookup val is found. Refers to the relative column number in the lookup range Example: if the lookup range is D1:E10 and you want the values in column E, then that is the 2nd column in the lookup range. -range_lookup = FALSE: for exact matches, True: for approximate matches Consequently, in your formula =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,BK68:BK86 ,BK68:BK86)) I think you might try: =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,2,0)) Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "Neil M" wrote: Okay! We are making some progress, but I hit another roadblock. Here is the formula exactly as it is entered in the first row in cell F78. =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,BK68:BK86 ,BK68:BK86)) So when I scroll to "0.25" in cell D78 I get "0.146" in cell F78 GREAT!!! Now, if I go down 1 row to cell D79 and copy the same list for a drop down menu and type the following formula in cell F79: =IF(ISBLANK(D79),"",VLOOKUP(D79,LU_ktype,BK68:BK86 ,BK68:BK86)) I get a reference error. I am referencing the same list name so why won't it just do the same as the cell above? Thanks , Neil |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drop Down Menus - 2nd Problem
I'm glad I helped, Neil.
Good luck with that promotion. €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "Neil M" wrote: Okay, now I got it. I assumed the 2,0 were just variables that you plugged in because you did not know my reference cells. I didn't realize it was part of the command/formula and thought I should selected the range of cells. Ron, thank you very much. This spreadsheet is worth a big promotion for me and I can't tell you how much I appreciate your help. Neil "Ron Coderre" wrote: It looks like there might be a misunderstanding of the way the VLOOKUP function works: =VLOOKUP(lookup_value,table_array,col_index_num,ra nge_lookup) Whe -lookup_value = Value to look for -table_array = List of values to seach for that value (uses 1st col, only) -col_index_num = Column that holds the value to return if lookup val is found. Refers to the relative column number in the lookup range Example: if the lookup range is D1:E10 and you want the values in column E, then that is the 2nd column in the lookup range. -range_lookup = FALSE: for exact matches, True: for approximate matches Consequently, in your formula =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,BK68:BK86 ,BK68:BK86)) I think you might try: =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,2,0)) Does that help? €¢€¢€¢€¢€¢€¢€¢€¢€¢€¢ Regards, Ron "Neil M" wrote: Okay! We are making some progress, but I hit another roadblock. Here is the formula exactly as it is entered in the first row in cell F78. =IF(ISBLANK(D78),"",VLOOKUP(D78,LU_Ktype,BK68:BK86 ,BK68:BK86)) So when I scroll to "0.25" in cell D78 I get "0.146" in cell F78 GREAT!!! Now, if I go down 1 row to cell D79 and copy the same list for a drop down menu and type the following formula in cell F79: =IF(ISBLANK(D79),"",VLOOKUP(D79,LU_ktype,BK68:BK86 ,BK68:BK86)) I get a reference error. I am referencing the same list name so why won't it just do the same as the cell above? Thanks , Neil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advanced: synchronizing data value across two worksheet drop boxes | Excel Worksheet Functions | |||
Drop down menus | Excel Discussion (Misc queries) | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions | |||
Adding drop down menus to a spreadsheet | Excel Worksheet Functions | |||
Problem with Pivot Table Drop-Down Menus | Excel Worksheet Functions |