ListBox Prroblem

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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