BEER-related: Subtract lbs of malt in inventory based on beer recipe.

[mngeo]

Hello,

I am trying to create a formula that would:
Match value in Recipe!D13 to the value in GRAINLOOKUP!A3:A200, then subtract Recipe!B13 from J3 of the row of match in GRAINLOOKUP!A3:A200

I would like to be able to run this formula by clicking on a button....

beer.png

[Bernie Deitrick]

You want to find the value in column J of GRAINLOOKUP based on the values in column A?

=VLOOKUP(Recipe!D13, GRAINLOOKUP!A:J,10,False)

And maybe this (your explanation is not really clear)

=VLOOKUP(Recipe!D13, GRAINLOOKUP!A:J,10,False) - Recipe!B13

Formulas are recalced automatically - if that is your setting - or by pressing F9.

[mngeo]

Ok. I will explain more in-depth.

I have a beer recipe sheet named Recipe.....the amount/lbs of grain is in cell B13 and the malt type is in D13.
I have an inventory of malt types in sheet GRAINLOOKUP.....the malt type is in A3 through A200 and the inventory/lbs of each malt is in J3 through J200.

So, after each brew is over, I want to be able to click on a button to subtract the amount/lbs of grain in B13 of sheet Recipe from the inventory/lbs of that malt in GRAINLOOKUP

beer.png

[Bernie Deitrick]

A macro like this may work - assign it to your button:

Please Login or Register  to view this content.

But if you want to adjust all the grain levels:

Please Login or Register  to view this content.

[mngeo]

You Sir, deserve beer! Thank you so much for helping me out!
Happy New Year.

[Bernie Deitrick]

I prefer cider

[mngeo]

Ok Bernie,
I have another one for you. I want to do the same for HOPLOOKUP sheet, the only issue is I have hops and amounts in three different places on the Recipe sheet:

hops.PNG

[Bernie Deitrick]

Is all this because I said I like cider?

Anyway, try it like this - I have assumed that the names and inventory levels on HOPLOOKUP are structured similarly to GRAIN...

Please Login or Register  to view this content.

[mngeo]

Yes, this all because you like cider and not beer ;-)

I tried the code, but I get this error:

mismatcherror.PNG

beerhops.PNG

[Bernie Deitrick]

When you get that error, replace that line with these:

Msgbox "Hop level is " & .Value
MsgBox "Usage is " & rngC.Offset(0, 3).Value

What do the message say?

OR can you attach your workbook?

[mngeo]

I have attached the workbook:

[Bernie Deitrick]

OK.

1) the values are in C and not J on the HOPS sheet
2) I was off by one column with my offset (my bad)
3) you have inconsistent merging of columns relative to data entry

To fix 3, select column P and Q on Recipe and unmerge them, then delete column Q

Then use this code:

Please Login or Register  to view this content.

[mngeo]

Bernie,

When I unmerge P and Q, I get this garbled mess. Is this what you wanted me to do?

unmerge.PNG

[Bernie Deitrick]

Don't unmerge, but use this version:

Please Login or Register  to view this content.

[mngeo]

After not unmerging and running the above code, I get this:

mismatcherror3.PNG

[mngeo]

After not unmerging and running the above code, I get this:

mismatcherror4.PNG

[Bernie Deitrick]

Move C2 on sheet HopsLookup to C1, and put a 0 into cell C2.

[mngeo]

Bernie,

Works great! Thank you a ton. Where do I send the cider?

[Bernie Deitrick]

Great - There used to be an 'attach drink' button but they had to remove it for legal reasons.....