Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Prroblem
Hi:
This formula is in (hidden column G ) =IF(ISNA(VLOOKUP (A6,Material DataBase!$A$9:$H$100,2,0)),, VLOOKUP(A6,Material DataBase!$A$9:$H$100,2,0)) This formula is in cell D =IF(A6 ="","",B6*C6*G6) A B C D E F G Item Cost Amount Total Nails 0.25 10 2.50 Bolts 1.00 8 8.00 A listbox is populated from column A of the Material Database Worksheet (more of a list) The user can pick items from the list and as oblivious from cell Gs formula the price is then in G and the routine is complete. All works as expected: The user can copy, drag down, right click and pick from list to change items in column A and the formula works The problem is if you move (left top corner and drag) an item in column A the formula fails. Is there a way to disable the move function? Is there a way to rewrite the formula? Any help will be greatly appreciated. TK |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Prroblem
If i want to stabilize and address
for example =A6 I could do =Indirect("A6") since A6 in the second instance is a string rather than a cell reference, it is never adjusted. This would also make it hard to enter the formula and drag fill it - so you might want to use code to do it.use a formula to calculate the address =offset(indirect("A1"),row()-1,0) If I understand the question. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: This formula is in (hidden column G ) =IF(ISNA(VLOOKUP (A6,'Material DataBase'!$A$9:$H$100,2,0)),"", VLOOKUP(A6,'Material DataBase'!$A$9:$H$100,2,0)) This formula is in cell D =IF(A6 ="","",B6*C6*G6) A B C D E F G Item Cost Amount Total Nails 0.25 10 2.50 Bolts 1.00 8 8.00 A listbox is populated from column A of the "Material Database" Worksheet (more of a list) The user can pick items from the list and as oblivious from cell G's formula the price is then in G and the routine is complete. All works as expected: The user can copy, drag down, right click and pick from list to change items in column A and the formula works The problem is if you move (left top corner and drag) an item in column A the formula fails. Is there a way to disable the move function? Is there a way to rewrite the formula? Any help will be greatly appreciated. TK |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Prroblem
Tom
Thank You =offset(indirect("A1"),row()-1,0) The above works great as a copy down. however: The formula =IF(A6 ="","",B6*C6*G6) now displays #NA or #REF if a drag or cut and paste is used. I could reference a different column B or C, but maybe you could suggest an alternative. Thanks again for the previous. TK "Tom Ogilvy" wrote: If i want to stabilize and address for example =A6 I could do =Indirect("A6") since A6 in the second instance is a string rather than a cell reference, it is never adjusted. This would also make it hard to enter the formula and drag fill it - so you might want to use code to do it.use a formula to calculate the address =offset(indirect("A1"),row()-1,0) If I understand the question. -- Regards, Tom Ogilvy "TK" wrote in message ... Hi: This formula is in (hidden column G ) =IF(ISNA(VLOOKUP (A6,'Material DataBase'!$A$9:$H$100,2,0)),"", VLOOKUP(A6,'Material DataBase'!$A$9:$H$100,2,0)) This formula is in cell D =IF(A6 ="","",B6*C6*G6) A B C D E F G Item Cost Amount Total Nails 0.25 10 2.50 Bolts 1.00 8 8.00 A listbox is populated from column A of the "Material Database" Worksheet (more of a list) The user can pick items from the list and as oblivious from cell G's formula the price is then in G and the routine is complete. All works as expected: The user can copy, drag down, right click and pick from list to change items in column A and the formula works The problem is if you move (left top corner and drag) an item in column A the formula fails. Is there a way to disable the move function? Is there a way to rewrite the formula? Any help will be greatly appreciated. TK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date range prroblem | Excel Discussion (Misc queries) | |||
listbox B conditional of input in Listbox A | Excel Discussion (Misc queries) | |||
Multicolumn Listbox and ordinary listbox | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming | |||
Is refreshing listbox rowsource in listbox click event possible? | Excel Programming |