rcbricker
03-02-2007, 10:05 AM
I have an IF statement in a cell that fires a Vlookup if a part number exists as MFG. If not there is no value returned. I need to have multiple layers of this till I get to the root Purchased item. However, when searching multiple parts I do not want to carry over the #N/A value that is returned when a sub component is not MFG.
Cell A1 = New Part #
Cell B1 = Component #
Cell C1 = existed - returns a "yes" found in the vlookup column if the component part exists, returns the standard #N/A if the part does not exist.
Cell D1 = price - returns this value from a vlookup
Cell E1 = M/P? - returns MFG for manufactured and PUR for Purchased.
Cell F1 = Sub Asm - This is a IF statement that states if E1 is = to MFG then vlookup column 5 which contains the sub part to make that component. If E1 contains PUR then "".
Now the problem is with the next stage.
Cell G1 = a new vlookup that searches for the sub asm part. This works fine when F1 contains a value. However if the F1 referred to a PUR part there is no value other than the IF statement. I need G1 to bring back no value (stay empty) if F1 is empty and if F1 is a MFG part I need G1 to return data based on the original commands of A-F.
Basically this series of IFs and Vlookups should continue till all parts in the row have been mapped down to their PUR parts.
Any help would be great.
Cell A1 = New Part #
Cell B1 = Component #
Cell C1 = existed - returns a "yes" found in the vlookup column if the component part exists, returns the standard #N/A if the part does not exist.
Cell D1 = price - returns this value from a vlookup
Cell E1 = M/P? - returns MFG for manufactured and PUR for Purchased.
Cell F1 = Sub Asm - This is a IF statement that states if E1 is = to MFG then vlookup column 5 which contains the sub part to make that component. If E1 contains PUR then "".
Now the problem is with the next stage.
Cell G1 = a new vlookup that searches for the sub asm part. This works fine when F1 contains a value. However if the F1 referred to a PUR part there is no value other than the IF statement. I need G1 to bring back no value (stay empty) if F1 is empty and if F1 is a MFG part I need G1 to return data based on the original commands of A-F.
Basically this series of IFs and Vlookups should continue till all parts in the row have been mapped down to their PUR parts.
Any help would be great.