Find row and add variable amount to it

[JeffJ]

I want to choose a selection from a drop-down
list, then type in an amount in a nearby cell,
and I want it to find the matching row of the
item I chose from the drop-down list and enter
that amount into that row (and add it to any
amount that is already there, because that cell
will already have a formula in it, and I want to
add to that formula).

For ex., let's say Column A has labels.
A1, A3 and A5 are labelled "Rafter." A2, A4 and
A6 are labelled "Amount."

A9:A13 are labelled "5001":"5005", respectively.
These represent the various names/types of the rafters.

B1:B6 require a selection or manual entry from the user.
B1, B3 and B5 are existing drop-down lists (corresponding
to the "Rafter" labels; the Data Validation settings for
the drop-down lists reference A9:A13).

B2, B4 and B6 are blank cells with no formulas that
allow *any* number/amount to be typed in by the user.

A9:A13 already have formulas in them, and these formulas
return amounts based on a combination of several different
factors.

But now I want to add another formula(s) to A9:A13 that
says, basically:
"Find the corresponding row (in B9:B13)
of the Rafter that I've chosen from any of the drop-down
lists (in any or all of B1, B3 or B5);
then, add whatever amount I have typed
(in any or all of B2, B4 or B6),
to any amounts already existing in B9:B13."

I'm guessing that possbily VLOOKUP, INDEX (Reference),
or possibly MATCH, or a combination of these, may
provide a solution.

Thank you for any help or replies.

Jeff
(e-mail address removed)
http://lightningfingers.tripod.co

 

[JeffJ]

By the way, B2, B4 and B6 are the "Amounts."

I'm guessing that something similar to the following formula (which, b
the way, is not working) might be what I'm looking for:

=VLOOKUP(B1,A9:A13,1,0)+B2*VLOOKUP(B3,A9:A13,1,0)+B4*VLOOKUP(B5,A9:A13,1,0)+B6


Thank you for any help
Jeff
(e-mail address removed)
http://lightningfingers.tripod.co

 

[Guest]

Hi Jeff
You can't do this with formulas. If you want to change a value in a cell you will need to use VBA

If the values in B9:B13 come from formulas then you could use IF statements

=existing formula + IF($A$1=A9,$A$2,0) + IF($A$3=A9,$A$4,0) + IF($A$5=A9,$A$6,0

Hope this helps

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- JeffJ > wrote: ----

I want to choose a selection from a drop-down
list, then type in an amount in a nearby cell,
and I want it to find the matching row of the
item I chose from the drop-down list and enter
that amount into that row (and add it to an
amount that is already there, because that cel
will already have a formula in it, and I want t
add to that formula)

For ex., let's say Column A has labels
A1, A3 and A5 are labelled "Rafter." A2, A4 and
A6 are labelled "Amount.

A9:A13 are labelled "5001":"5005", respectively
These represent the various names/types of the rafters

B1:B6 require a selection or manual entry from the user
B1, B3 and B5 are existing drop-down lists (corresponding
to the "Rafter" labels; the Data Validation settings fo
the drop-down lists reference A9:A13)

B2, B4 and B6 are blank cells with no formulas tha
allow *any* number/amount to be typed in by the user

A9:A13 already have formulas in them, and these formula
return amounts based on a combination of several differen
factors

But now I want to add another formula(s) to A9:A13 tha
says, basically:
"Find the corresponding row (in B9:B13
of the Rafter that I've chosen from any of the drop-dow
lists (in any or all of B1, B3 or B5);
then, add whatever amount I have typed
(in any or all of B2, B4 or B6),
to any amounts already existing in B9:B13.

I'm guessing that possbily VLOOKUP, INDEX (Reference),
or possibly MATCH, or a combination of these, ma
provide a solution

Thank you for any help or replies

Jef
(e-mail address removed)
http://lightningfingers.tripod.co

 

[JeffJ]

Awesome! That works!
Thank you very much, Mark!

If there is nothing entered, I want the cell
to be blank rather than return 0.
But oddly enough, even if I replace the 0
in your formula with "", it still returns 0
when nothing is entered for Amounts.

However, I realize that is easily solved
by making the 0 invisible using Conditional
Formatting.

Thank you again!

Jeff
(e-mail address removed)
http://lightningfingers.tripod.co

 

[Guest]

Hi Jeff
Glad to help

If you want to hide the zeros, you could also put the whole formula into an IF statement

=IF(existing formula + IF($B$1=A9,$B$2,0) + IF($B$3=A9,$B$4,0) + IF($B$5=A9,$B$6,0)=0,'',existing formula + IF($B$1=A9,$B$2,0) + IF($B$3=A9,$B$4,0) + IF($B$5=A9,$B$6,0)

Good Luck
Mark Graesse
(e-mail address removed)
Boston M


----- JeffJ > wrote: ----

Awesome! That works
Thank you very much, Mark

If there is nothing entered, I want the cel
to be blank rather than return 0
But oddly enough, even if I replace the
in your formula with "", it still returns
when nothing is entered for Amounts

However, I realize that is easily solve
by making the 0 invisible using Conditiona
Formatting

Thank you again

Jef
(e-mail address removed)
http://lightningfingers.tripod.co

 

[JeffJ]

Mark,

Sorry for the late reply. Your reply also helped me solve anothe
issue: that of getting the #VALUE! error.

Thanks again