+ Reply to Thread
Results 1 to 5 of 5

Complicated VLookup Function with =IF(AND(NOT(ISBLANK

  1. #1
    Registered User
    Join Date
    01-09-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Complicated VLookup Function with =IF(AND(NOT(ISBLANK

    Hey, I'm making an automated spreadsheet for some work I'll need to be doing, and the basis of it is there are multiple tabs (though I have only attached one) that feed into the Main Schedule. Basically there are a range of tests to be done on each section and the answers will tick or ? a box in the main schedule, to track everything.

    So currently I have made cell I10 on the MainSchedule link that there will be a tick if D4 (Income Queries) has been ticked, a question mark if there is a query been filled in in E4 (Income Queries) and a tick with a green background to show the query was cleared if then as well as this the H2 (Income Queries) has been ticked. The formula is fairly complicated, and there may be an easier way to do this. However there may be a time where there are multiple issues for each item I am testing. So i wanted instead of I10 just looking at row 2 in Income queries, it to find any row that in the A column (Income Queries) that has a 2 in it for example to get a tick in I10, all the items that have a specific reference of 2 will be ticked with no query beside it.

    TLDR: Keep all the same formula of what will tick and what will ? and what will tick green, however intead of just picking up a single line that is predetermined for it to look at multiple records if there are for a specific reference and check all of these for the different tests. Thank you.

    Chris.
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Complicated VLookup Function with =IF(AND(NOT(ISBLANK

    =NOT(ISBLANK('Income Queries'!E4)) can be just
    ='Income Queries'!E4<>""
    getting rid of not and isblank that will simplify things
    but you have
    1. posted an xlsx when your profile shows 2003 do you wasnt a solution for 2003 or 2007and later and this would have to be an xlsm for the udf to work
    2. used an udf colour function which seems not to be included in the vba project
    3,left in formulas to hyperlink to some other workbook
    all of which makes this difficult to work on
    Last edited by martindwilson; 01-09-2014 at 07:28 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    01-09-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Complicated VLookup Function with =IF(AND(NOT(ISBLANK

    Okay, thanks for the help. I will send the complete workbook with all the links. I am currently using Excel 2010, and the document is saved as an Excel Macro-Enabled Workbook. This is complete and what I am working on. Can't wait to hear back from you.
    Attached Files Attached Files

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Complicated VLookup Function with =IF(AND(NOT(ISBLANK

    ox ive removed eternal references on sheet main but i still cant see what you want to do!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-09-2014
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Complicated VLookup Function with =IF(AND(NOT(ISBLANK

    basically. if on the income query sheet there is more than one query for Number 2 for example i need it to pick it up. So at the moment it only looks at a specific row that so happens to have item 2 in that row, if there are two queries ill have to add a like below that also has the specific number 2 in column A if the first one is cleared by ticking H4, but the second query is still outstanding, I need it to still be a ? on the main schedule?

    Chris

+ 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. ISBLANK plus IF plus complicated average
    By alleyb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-11-2011, 08:41 PM
  2. Help with ISBLANK function referring to a cell with another ISBLANK formula
    By camdameron in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-23-2011, 12:45 PM
  3. IF(ISBlank with Vlookup
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-23-2010, 01:07 PM
  4. Matching values between 2 files : a VLOOKUP function more complicated ?
    By Psykoflo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2007, 06:05 AM
  5. [SOLVED] Vlookup and isblank (or ???)
    By gublues in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-25-2006, 06:35 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