LinkBack URL
About LinkBacks
I am in need of an IF formula for pulling the inventory number from the field inventory page. I would like to be able to fill in the SLOC # on the Quotation page and have the current inventory column be auto fill based on the Part number listed.
I am in need of an IF formula for pulling the inventory number from the field inventory page. I would like to be able to fill in the SLOC # on the Quotation page and have the current inventory column be auto fill based on the Part number listed.
you want this info to be filled in automatically based on the slock# that is entered manaully?
PART NUMBER QTY DESCRIPTION UNIT PRICE TAXABLE? AMOUNT
So.... You choose a SLOC Number (e.g. 16) in G9. then what??
I made a guess. I sorted your Field inventory by Column B. I then used Data validation to restrict the part numbers to those relevant to the SLOC (B15 of quotation downwards), using this anmed range (called PN_Range):
=INDEX('Field Inventory'!D:D,AGGREGATE(14,6,ROW('Field Inventory'!$D$2:$D$7000)/('Field Inventory'!$B$2:$B$7000=Quotation!$G$9),1)):INDEX('Field Inventory'!D:D,AGGREGATE(15,6,ROW('Field Inventory'!$D$2:$D$7000)/('Field Inventory'!$B$2:$B$7000=Quotation!$G$9),1))
The, a VLOOKUP to return the inventory:
=IFERROR(VLOOKUP(B15,'Field Inventory'!$D:$F,3,FALSE),"")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I have just noticed that you are using Excel 2007. If so, the nice data validation won't work. Let me know if you are NOT using 2010+ (you joined quite a few years ago and may have forgotten to update your profile). I'll fix it in the morning, if needed.
That didn't seem to work. I will add a little more to the example.
So far I have conditional formatting in for I15-I24 so that if the quantity in C15-C24 is greater than those in column I it highlights the quantity showing we need to order more because we do not have enough currently. I have the part number column set up to pull a description and unit price from the Price list page (page 3).
So what I need is to be able to search a part number and current inventory number from the field inventory page (page 2). I have the columns highlighted in the corresponding colors to hopefully help explain it better.
B15
=INDEX('Feild Inventory'!$D$1:$D$7028,AGGREGATE(15,6,(ROW('Feild Inventory'!$B$1:$B$7028)/(Quotation!$G$9='Feild Inventory'!$B$1:$B$7028)),ROWS('Feild Inventory'!$I$1:I1)))
E15
=IFERROR(VLOOKUP(B15,'Price List'!A:C,3,0),"")
I15
=INDEX('Feild Inventory'!$F$1:$F$7011,AGGREGATE(15,6,(ROW('Feild Inventory'!$B$1:$B$7028)/(Quotation!$G$9='Feild Inventory'!$B$1:$B$7028)),ROWS('Feild Inventory'!$I$1:I1)))
Last edited by Limor_OP; 12-02-2020 at 01:52 PM.
"That didn't seem to work." tells me very little. Did it work as desired in MY sheet? If not, what was wrong?
What happened if/when you transferred it to your REAL sheet.
As requested, please confirm if you are still using Excel 2007.
Last edited by Glenn Kennedy; 12-02-2020 at 02:12 PM.
That was closer but when I would try to change the part number it would not update the current inventory column. Could it be an index match for G9 and the part number column? If so what would that look like?
In my file it does...please upload the file you are using
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks