I am looking for the best VBA way to do this…..
There are two tables: tbl1 and tbl2_divisors
Requirements
Tbl1 has a total column that needs to be converted to three other columns via dividing by a specific number. That specific number is specific to a prod. TBL2_Divisors has the three divisors type isted in three columns for a one each prod.
Need to a VBA to do the following:
• If E5 has a value, and
• Look up the divisor for the combination of (prod + convert) in tbl2_divisor
• Then divide E5/looked up and put in F5.
• Likewise do the same for G and H as well.
• Do the above for all the elements in column D
Just found out that both the rows in the tables can vary and will grow with time, so I am not sure if hardcoding them in formulas will work? What do you think for dynamic ranges?
ahh I see you remove the last ",0" from the old match below
MATCH(""D_""&$D5,$K$5:$K$" & xDivisor_End & ",0)
Got it :)
Rayne
ASKER
Thanks to both the experts for their awesome help :)
terencino
Hi Rayne, just to finish this off for both options, please refer attached using dynamic ranges which will adapt when new rows are added. The named ranges are based on an OFFSET function which makes them dynamic. divisorLookUp-3.xlsx
That works