oblix
Board Regular
- Joined
- Mar 29, 2017
- Messages
- 183
- Office Version
- 365
- 2021
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- 2003 or older
- Platform
- Windows
- MacOS
- Mobile
- Web
Hi there all
I have a complicated index formula I can not resolve.
Background:
I created a sheet with a shopping list (sheet name - SL1)
in this sheet
column B has the product description (eg apples)
column Q the name of the company I entend to buy from (eg company A)
column R the oty i intend buying (eg 10)
column S the price of product for this company (eg $10-00)
in onother sheet i created a purchase order form with these formulas (sheet name PO1)
column B product decription
=IFERROR(INDEX('SL1'!$B$7:$B$605,SMALL(IF(('SL1'!$R$7:$R$605>0)*('SL1'!$Q$7:$Q$605=$K$5),ROW('SL1'!$B$7:$B$605)),ROW(1:1))-6,1),"")
column E Qty: =IFERROR(ROUNDUP(INDEX('SL1'!$R$7:$R$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),0),"")
column F price: =IF(ISERROR(MATCH(A20,'SL1'!$B$7:$B$489,0)),"",INDEX('SL1'!$S$7:$S$489,MATCH(A20,'SL1'!$B$7:$B$489,0)))
k5 = name of company I want to order from
This formulas worked beautifully and was able to drag formulas down the columns ad there is my order form.
If I change the name of the company (k5), the order updates for that company.
Problem:
The boss asked me to add columns in the shopping list to add another possible company per product line.
I did this by adding:
column U: Company name 2
column V: QTY 2
column W: Price 2
Now the purchase order needs to look at two set of columns (q,r,s) and (U,V,W) to populate as company of that order can be in one of those column groups.
For the life of me I dont know how to ammend those three formulas to make this work and believe I have tried.
Please please is there any body that can assist!
Thank you in advance
I have a complicated index formula I can not resolve.
Background:
I created a sheet with a shopping list (sheet name - SL1)
in this sheet
column B has the product description (eg apples)
column Q the name of the company I entend to buy from (eg company A)
column R the oty i intend buying (eg 10)
column S the price of product for this company (eg $10-00)
in onother sheet i created a purchase order form with these formulas (sheet name PO1)
column B product decription
=IFERROR(INDEX('SL1'!$B$7:$B$605,SMALL(IF(('SL1'!$R$7:$R$605>0)*('SL1'!$Q$7:$Q$605=$K$5),ROW('SL1'!$B$7:$B$605)),ROW(1:1))-6,1),"")
column E Qty: =IFERROR(ROUNDUP(INDEX('SL1'!$R$7:$R$489,MATCH(A20,'SL1'!$B$7:$B$489,0)),0),"")
column F price: =IF(ISERROR(MATCH(A20,'SL1'!$B$7:$B$489,0)),"",INDEX('SL1'!$S$7:$S$489,MATCH(A20,'SL1'!$B$7:$B$489,0)))
k5 = name of company I want to order from
This formulas worked beautifully and was able to drag formulas down the columns ad there is my order form.
If I change the name of the company (k5), the order updates for that company.
Problem:
The boss asked me to add columns in the shopping list to add another possible company per product line.
I did this by adding:
column U: Company name 2
column V: QTY 2
column W: Price 2
Now the purchase order needs to look at two set of columns (q,r,s) and (U,V,W) to populate as company of that order can be in one of those column groups.
For the life of me I dont know how to ammend those three formulas to make this work and believe I have tried.
Please please is there any body that can assist!
Thank you in advance
Last edited by a moderator: