Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
OldFella

I've Messed Up =LOOKUP

Question

Hi Caleeco

Looks like you've been having it pretty easy with me not pestering you with my problems.

However, I have a new problem. Still on the same spreadsheet as previous, but a little enhancement has stopped working.

I've been using   =LOOKUP(2,1/(1-ISBLANK(S:S)),S:S)   to find the last value in a column that consists of blanks and manually entered numbers. It worked fine until I decided to use a formula to automatically enter new values in the column instead of me entering them manually. There are now about a hundred blank cells in the lower section of the column, each with an underlying formula. Now my =LOOKUP cell is blank; presumably it's showing the value of the last formula in the column - a blank.

How do I resolve this? I searched the 'net and tried an assortment of similar formulae, but nothing worked. Now I need proper help.

As always, I'm open to using a formula or VBA, whichever is the more efficient.

Many thanks.

  • Like 1

Share this post


Link to post
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Hey OldFella, 

 

Good to see you, hope yo've been keeping well!

 

Yes, I haven't really been promoting the forum in recent months, my 'real' job is taking up most of my time these days. Always happy to answer Excel questions though, so thanks for posting. Without seeing your sheet, this is a bit of a stab in the dark, but we could make use of Mathmatical operators!

 

Try using this formula instead:

=LOOKUP(2,1/(S:S<>""),S:S)

 

Let me know how you get on

Cheers

Caleeco

 

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco

For a "stab in the dark", that was pretty well aimed! As expected, it works perfectly - but I've learned by now to expect nothing less   :D

 

Thank you so much for that, now the sheet is performing as normal again.

 

I'll try to come up with something that taxes your brain a bit better next time.

 

Cheers

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Awesome, thanks for letting me know that it worked!

 

Haha, no problem.. don't mind doing quick fire questions too, helps me sharpen my skill set ;) 

 

All the best

Caleeco

 

Marked As Solved!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×