PivotTable formatting means Vlookup won't work!

**MonacoIntern** · 06-27-2008, 01:55 PM

Hey there!

I'm working on a bill of materials that uses a pivot table to sum all the wire used in a wire harness. To the right of the pivot table are two columns, one to calculate the footage of each wire type from the pivot table's inches, and the other is the part number for the wire. (See picture)

http://img246.imageshack.us/img246/6...tformatrq7.png

I am pulling the part numbers from a separate file, our 'Master List', which is formatted like so:

http://img170.imageshack.us/img170/1...mformatym6.png

Now, in the Master list, I have no problem making a column that's simply =GA&COLOR and filling it down to reduce the problem to one criteria.

My problem comes from autofilling a formula down the BOM page. See, due to the formatting of the pivot table, 2GA Red and 4GA BLK have no gauge. Technically, to excel the gauge cells for those are blank. Though to a user it's obvious due to the line formatting on the sheet, to a formula it doesn't work.

I realize in the picture of the BOM there are part numbers, but in each BOM, the pivot table adjusts, so in some the table is longer, in others it's shorter, and the order of the colors within the gauge is fairly random. Thus fixing the bills when a change is made takes easily 30-45 minutes.

I've looked at adding the Part Number to the database the pivot table uses, but the wires appear many many numerous times and I have about 1000 of these BOMs.

So, how can I use a formula to (recursively?) look to the cell above if the guage cell is blank? Is there a different tool I could use?

Thanks for all your help!!

**mrice** · 06-28-2008, 04:19 PM

This user defined function may help

Function NextValueUp(Cell As Range)
If Cell.Value = "" Then
    NextValueUp = Cell.End(xlUp).Value
Else
    NextValueUp = Cell.Value
End If
End Function

Paste this into a new module in the VBA editor (Alt F11).

In the sheet you can use a formula like