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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...