Vlookup Formula help required?

[Alan T]

I'm trying to run the following formula:-

=IF(B2="","",VLOOKUP($B2,'Data Field'!$A$7:$B$12,2))

I need the formula to run in each cell from B2 to B10000. I know I can
drag the formula down through each cell. But this increases the size of
the sheet.

Is there any way to have the formula run in each of these cells without
dragging down?

 

[Paul]

I'm trying to run the following formula:-

=IF(B2="","",VLOOKUP($B2,'Data Field'!$A$7:$B$12,2))

I need the formula to run in each cell from B2 to B10000. I know I can
drag the formula down through each cell. But this increases the size of
the sheet.

Is there any way to have the formula run in each of these cells without
dragging down?

Dragging down is what you should do. Of course it increases the size of the
sheet - you don't get 10000 answers for the price of one!

 

[Alan T]

That's kinda what I thought ... I had wondered if it was possible to us
Conditional Formatting to format the column.

I was looking for a way to take the formula out of the cell as I hav
one macro which will cut and paste data from that sheet to anothe
workbook, but this removes the formula from the cell. I have anothe
macro which populates the original sheet from a form, but this i
rendered pointless unless the data it outputs is converted by th

 

[Don Guillett]

You might like this a lot better.

Sub lookupnonblank()
Set Rng = [c2:c10000]
Rng.Formula = _
"=IF(b2="""","""",VLOOKUP(b2,'Data Field'!$A$7:$B$12,2,0))"
Rng.Formula = Rng.Value 'deletes formulas
End Sub