+ Reply to Thread
Results 1 to 6 of 6

Disable Vlook Up if a row contains "SOLD"

  1. #1
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Disable Vlook Up if a row contains "SOLD"

    Hey Guys,
    I am stuck with a problem

    In sheet "INVOICE" - "B11:B29" I type the product code and get its details through VLookup.

    In sheet "PRODUCT" I type the product details.

    In sheet "PRODUCT", range "P" , It shows "SOLD" if a particular product in sold,


    So basically when i write a product code in sheet "INVOICE", I dont want it to VLOOKUP the codes which are already sold.

    Because it happens that I write the wrong code and make wrong invoice.


    Is there any way to stop that ?

    Maybe show a error message or anything?
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: Disable Vlook Up if a row contains "SOLD"

    Cannot edit the cell you have the vlookup function in. I don't know what you have done here, but everytime I place the cursor in that cell, it immediately jumps down one cell and over to the right. Until this is rectified, I can offer no help with this issue.

  3. #3
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Disable Vlook Up if a row contains "SOLD"

    Hey Alan,

    Sorry,

    A macro was activated.

    I have updated and attached the sample file. plese check
    Attached Files Attached Files

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: Disable Vlook Up if a row contains "SOLD"

    In O11 =IF(ISBLANK(B11),"",IF(VLOOKUP(B11,PRODUCT!A4:P20,16,FALSE)="SOLD","",VLOOKUP(B11,test.xlsm!All,4,FALSE)))

  5. #5
    Forum Contributor
    Join Date
    08-18-2017
    Location
    india
    MS-Off Ver
    2010
    Posts
    490

    Re: Disable Vlook Up if a row contains "SOLD"

    Hey Alan,

    It works fine, Thank you.

    But in sheet "Invoice" there is a "PAID" or "DUE" button.

    I press these button depending whether the customer pays me or not.

    These button saves the invoice to a specified folder and updates the "Product" sheet by mentioning SOLD.


    Now I have pasted your code in my excel file.

    The problem is that If i want to cross check the invoice then i do not get the details as it disables VLOOKUP.


    i have attched the screenshot.


    Maybe there is a alternative solution through VBA?
    Attached Images Attached Images

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,671

    Re: Disable Vlook Up if a row contains "SOLD"

    I don't understand this.

    The problem is that If i want to cross check the invoice then i do not get the details as it disables VLOOKUP.
    If vlookup is a formula in your worksheet, how is it disabled. Show me the specifics.

    And showing me a picture is no help as I cannot see nor understand what is happening in a picture. You must explain in clear English with examples of what steps you take and what is actually happening. Here is a picture of a solution, not yours. Can you tell what is happening. No, and neither can I.

    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    NAME
    DESCRIPTION
    GUID
    IS MISSING?
    FULL PATH
    2
    VBA
    Visual Basic For Applications
    {000204EF-0000-0000-C000-000000000046}
    FALSE
    C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
    3
    Excel
    Microsoft Excel 16.0 Object Library
    {00020813-0000-0000-C000-000000000046}
    FALSE
    C:\Program Files (x86)\Microsoft Office\Root\Office16\EXCEL.EXE
    4
    stdole
    OLE Automation
    {00020430-0000-0000-C000-000000000046}
    FALSE
    C:\Windows\SysWOW64\stdole2.tlb
    5
    Office
    Microsoft Office 16.0 Object Library
    {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
    FALSE
    C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\MSO.DLL
    6
    MSForms
    Microsoft Forms 2.0 Object Library
    {0D452EE1-E08F-101A-852E-02608C4D0BB4}
    FALSE
    C:\WINDOWS\SysWoW64\FM20.DLL
    7
    MSComCtl2
    Microsoft Windows Common Controls-2 6.0 (SP6)
    {86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}
    FALSE
    c:\windows\SysWow64\MSCOMCT2.ocx
    8
    VBIDE
    Microsoft Visual Basic for Applications Extensibility 5.3
    {0002E157-0000-0000-C000-000000000046}
    FALSE
    C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB

+ 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. Replies: 5
    Last Post: 02-05-2019, 12:03 AM
  2. Replies: 16
    Last Post: 12-23-2017, 11:37 AM
  3. Use (LEFT(A4;FIND(" ";A4)-1) to count stock sold
    By Luther.King in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-02-2014, 12:02 PM
  4. [SOLVED] Moving entire rows to new sheet once item has a status of "Sold"
    By Linklog in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-30-2013, 04:10 PM
  5. sum amount of units sold at "x" price
    By dn1016 in forum Excel General
    Replies: 2
    Last Post: 11-01-2011, 06:47 PM
  6. [SOLVED] Disable "Right Click" ... or any ability to "cut", "insert", etc.
    By jonvanwyk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2011, 09:26 AM
  7. How to change a Command Button caption from "Enable" to "Disable"?
    By Infinity in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2007, 12:14 AM

Tags for this Thread

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