I am currently using a formula (without using VB) that offsets the lookup on
a row:
=OFFSET(X5,COUNTA(X:X)*1,-1)

I had to add a formula to that same range that is only visible if there is
data from whe it is pulling from.

How do I make the above formula (or make a different formula) look at only
the value of the cell not the contents of the formula.

The contents of the current cell in the range:
=IF('sheet7'!N18="-","",'sheet7'!N18)

Thanks for any help with this!