+ Reply to Thread
Results 1 to 11 of 11

Using xlookup/vlookup formula into a validated cell to return a value into that cell

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    57

    Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Hi all,

    Could you please help? I'm trying to use a lookup formula to return results into a column that has validations (must be 11 digits).

    I've used lookup for other validated columns returning results like email addresses and countries. These did not work at first when I selected table names/named ranges in the formula - validation error appeared and also '#spill' error. When I just selected the columns "long-hand", they worked. I can't seem to get the telephone numbers to return though? At first I found a problem with one phone number that had a space and a length check LEN formula seems to count space as a digit. Once I closed that up I thought it would work. Then I thought extra blank rows/fields in the table may be a problem so I got rid of blanks in the column of telephone numbers and it still doesn't work. Thoughts please?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Hi,
    I refreshed the rule and it seems to work now.
    Dont know what the problem.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    57

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Thanks for coming back to me Belinda. Hi there in Israel

    What do you mean you by 'refreshed the rule'? I'm not aware of this function. I looked around and in the DATA menu there's a 'refresh all'. Tried that and I'm still getting validation error if trying to vlookup into the validated column. Please clarify what you did? Thank you!

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Hi there in London

    What I meant was that I removed the validation and applied the rule all over again, to "refresh" it.
    I suspect that the formula was reading as a code and not producing the output of the code (see attached to show what I mean).

    That's why it was determined as more than 11 characters.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    57

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Oh ok, you cleared and re-did the validation. Ok, I did that also on my real worksheet and even on your returned worksheet my entries don't work. I've tried making sure the source+ lookup columns are both 'text' and the return column is 'general'. I've tried switching from my selection of 'Text Length' as '11' in the validation dialogue box to using 'Custom' and 'Formula' as = TEXT($B2,"00000000000"). Still my lookup formula gets blocked.

    I wonder why it might be reading the lookup formula formula as the output (and determining it as more than 11 characters).

    I notice when in your cells, the formula can be seen in the top display box but for my others that have worked so far (other lookups of country, emails), even though they have returned correct results, the underlying cells display as blank. My real worksheet is protected and these are the input columns.
    I don't know where my settings somewhere are different from yours.

    Any other ideas welcome!
    Last edited by Questray; 05-12-2020 at 08:38 AM.

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Yes, exactly. Sorry I wasnt clear enough in the first time.

    Good point regarding the cell formatting, I returned back to your initial file, and saw that indeed they were formatted as text (see print screen).
    You should change it to number (not general). It works on your original source with the chnging of the format, I tested that as well.


    If it still doesnt come through - please uploap again the latest file you are working on to inspect the reason for the failure.

    Good Luck.
    Attached Images Attached Images

  7. #7
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    57

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    No worries at all. I got worn out trying lots of variations.

    You're right - output cell as 'NUMBER' format returns the result in my test sheet and actually 'GENERAL' did too and both retained the all-important leading zero in the result.

    I realise now why the input cell was text - to enable users to manually enter numbers retaining the leading zero. It doesn't seem possible to cover all entry options.
    (1) If you make the input cell NUMBER or GENERAL you'll be able to lookup into it and get results retaining leading zeros but
    (2) You can't manually input with leading zeros and get past validation with NUMBER or GENERAL only with TEXT format

    And after so many tries I don't know if a 'corruption' happens (and maybe the 'refresh' you did is needed) because initial tries on my real worksheet worked (with the understanding above) then later tries failed again.
    Last edited by Questray; 05-12-2020 at 10:21 AM.

  8. #8
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Just a thought...
    If you format as number and put Apostrophe (')before it - it will show the number including leading zeros. I dont think that the apostrophe is counted as a character , it also isnt visible in the cell display.

  9. #9
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    57

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Yes sure using a ' in front keeps leading zero.

    In the end I think as it's a users' spreadsheet, not knowing what they'll do and perhaps lower likelihood of direct lookup into the sheet, I'll keep it TEXT formatted.

    After submission, I can deal with getting the leading zeros in if any problem. Also a work-around is that they can look-up outside the sheet and just paste results into the columns which overrides validations anyway!

    Thanks Belinda for engaging in my minutiae

  10. #10
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Glad you found a compromise solution, and good luck!

  11. #11
    Registered User
    Join Date
    04-19-2020
    Location
    London, England
    MS-Off Ver
    2007/2016/MS 365
    Posts
    57

    Re: Using xlookup/vlookup formula into a validated cell to return a value into that cell

    Thank you Belinda!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Xlookup for first word in cell?
    By haskenazi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-06-2020, 02:05 PM
  2. Replies: 2
    Last Post: 03-22-2020, 09:05 PM
  3. Xlookup blank cells - doesn't return error!
    By Jedab in forum Office 365
    Replies: 3
    Last Post: 02-11-2020, 06:05 PM
  4. Replies: 2
    Last Post: 04-16-2019, 10:04 AM
  5. [SOLVED] Formula to always return cell below what a vlookup would return
    By KCHEXCEL in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2013, 12:57 AM
  6. Vlookup changed validated cell value in macro
    By nebb in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-03-2009, 08:16 AM
  7. cell data not validated if navigating cell to cell with mouse
    By LoveThatMouse in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-21-2006, 04:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1