Brian from Maui said:
LarrySC said:
This would be easier if I could send you a copy of the workbook in question. Then, we would exchange ideas either via email or an online chat or instant message program.
I use Yahoo! Instant Messenger and AOL Instant Messenger. Either one works for me.
See,
http://216.92.17.166/board2/viewtopic.php?t=49751
in particular 6,8, and 9.
Sorry for the breach of protocol. I am new here and didn't realize the usual and accepted method of asking questions. Please forgive me!
Let me try to explain my situation.
I have a page in a workbook that I have 645 lines of data listed. There are seven columns in each record. They are labeled as such:
Column A: Shape Code
Column B: 1st Dimension
Column C: 2nd Dimension
Column D: 3rd Dimension
Column E: Unit of Measure
Column F: A formula that 'builds' a string of columns A, B, C, and D (depending on if there is a value in those columns)
Column G: A unit weight for each shape code
Now, on another page of the workbook, I have set up a 'bill of material' table for the user to enter items of each shop drawing. I want the spreadsheet to do a lookup on each line of material and find the unit weight, then calculate the total weight for that line item based on the size (area or length) and quantity. The columns on the BOM page are labeled like this:
Column A: Quantity
Column B: Ship Mark
Column C: Piece Mark
Column D: Shape Code
Column E: 1st Dimension
Column F: 2nd Dimension
Column G: 3rd Dimension
Column H: Feet
Column I: Inches
Column J: Fraction
Column K: Remarks
Column L: Unit Weight
this is the cell that contains the vlookup formula: =IF($O4<>"",+$O4,IF(D4="","",VLOOKUP($P4,'WEIGHT TABLE'!$F$4:$G$648,2)))
Column M: Area/Length
this is the cell that calculates the length of a piece or the area for plate shapes
Column N: Extended Weight
this is the cell that multiplies the quantity, unit weight, and length/area
Column O: Certain steel shapes have the UNIT WEIGHT in the member size. This column determines if that statement is true.
Column P: Similar to COLUMN F on the WEIGHT TABLE sheet in that it 'builds' a string of information for the vlookup formula to work off of:
=IF($G4="",IF($F4="",CONCATENATE($D4," ",$E4),CONCATENATE($D4," ",$E4," x ",$F4)),CONCATENATE($D4," ",$E4," x ",$F4," x ",$G4))
Column Q: Builds a length
Column R: " " " "
Column S: " " " "
Now, the vlookup formula that is in Column L works correctly on Rows 4 through Row 569. Here, the vlookup formula returns a value of "76.070". which is the correct value for Row 626. This value of "76.070" continues to show up through Row 591. Then, the vlookup formula begins to return a value of "1.2730", which is the correct value for Row 469!
I cannot figure out why the vlookup formula is returning these bogus numbers on Rows 569 through 626.