OrangeYoda
New Member
- Joined
- Sep 23, 2022
- Messages
- 17
- Office Version
- 365
- Platform
- Windows
Good afternoon all.
I'm fairly familiar with Excel. I have nasty formula that works fine. I would like to see it shorter in order to maintain it in the future.
You can ignore most this formula. I've highlighted the issue. Each item below is one I have to measure by footage whereas all my other products are a simple count/quantity. So, in this formula I've built a carve out exception that looks at my per foot material(wire). If it sees a wire sku it reads a separate table (a table of wire) and brings back the cost per foot instead of per roll.
I need to add more wire types, but this is getting out of hand quickly. Is there a more efficient way to do this?
IF(ISNA(VLOOKUP(Q111,Pricebook!$A:$E,5,0)),"",IF(OR(Q111=2487703,Q111=2487704,Q111=2487705,Q111=2487710,Q111=2487708,Q111=2487712,Q111=852129,Q111=3571573,Q111=3571565,Q111=3571567,Q111=3571561,Q111=3571562,Q111=136847,Q111=136850),VLOOKUP(Q111,Price_Calc_Mod!$D:$H,5)*R111,VLOOKUP(Q111,Pricebook!$A:$E,5,0)*R111))
I'm fairly familiar with Excel. I have nasty formula that works fine. I would like to see it shorter in order to maintain it in the future.
You can ignore most this formula. I've highlighted the issue. Each item below is one I have to measure by footage whereas all my other products are a simple count/quantity. So, in this formula I've built a carve out exception that looks at my per foot material(wire). If it sees a wire sku it reads a separate table (a table of wire) and brings back the cost per foot instead of per roll.
I need to add more wire types, but this is getting out of hand quickly. Is there a more efficient way to do this?
IF(ISNA(VLOOKUP(Q111,Pricebook!$A:$E,5,0)),"",IF(OR(Q111=2487703,Q111=2487704,Q111=2487705,Q111=2487710,Q111=2487708,Q111=2487712,Q111=852129,Q111=3571573,Q111=3571565,Q111=3571567,Q111=3571561,Q111=3571562,Q111=136847,Q111=136850),VLOOKUP(Q111,Price_Calc_Mod!$D:$H,5)*R111,VLOOKUP(Q111,Pricebook!$A:$E,5,0)*R111))