Posts by slackmaster

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Re: Search Cell for Multiple Criteria with a List

    This formula

    =IF(SUM(--ISNUMBER(SEARCH(A2,'Flagged Addresses'!$A$1:$A$100)))>0,"present","Not Present")

    Appears to work completely (Thanks Justin), except for one issue.

    When the address has an apartment.


    "7700 Delaware Dr" is in the list of flagged addresses. An address comes up as "7700 Delaware Dr APT 3121".

    Because of the apartment, it doesn't get flagged by the formula. Is there a way to make so that it doesn't have to be an exact match?

    I've tried:

    =IF(SUM(--ISNUMBER(SEARCH(A2,('Flagged Addresses'!$A$1:$A$100)&"*")))>0,"present","Not Present")

    but that doesn't work.

    Here's the new sheet with the new formula.


    I've been searching (and maybe I'm blind), but I'm trying to figure out how to use the Search function to search an individual cell to see if it contains any address from a list of addresses in the same sheet.

    Example: I have Column A which contains the list of addresses I want to check. Column B contains the list of addresses that I want to be flagged. In column C I would like a formula that checks column A to see if the address in each cell matches any one of the addresses in column B.

    So far, this is what I have:

    First, I want to use the search function since it's not case sensitive so:


    This works great for checking one cell. I added the "*" so that if an address in the list shows 1100 "Maple Ave Apt 2516" and one of the flagged addresses is "1100 Maple Ave" it will still get flagged even though it's not an exact match.

    Again, this works great for one cell. The problem I have is when I'm trying to search one cell for multiple addresses.

    I want to search A1 to see if it contains any of the addresses in B1:B100.

    When I do:


    It doesn't work.

    [COLOR="red"]EDIT by Moderator: Do not use code tags for formula. They are reserved for VBA code only.[/COLOR]


    I have a cell with the following formula:


    I'm using this formula because I want to average the selected cells, but if A2,C2, or D2 are blank I don't want it to return the #DIV/0! error, hence the "ISERROR" part.

    Now... I want to conditionally format the cell with the above formula in it so that if it is equal to or greater than 80% it's green.

    Here's the problem, the conditional format colors the cell green if it is blank too.

    How do I stop that?

    Below is an attached spreadsheet to show what I'm trying to do.

    Thanks again, sorry if I missed this somewhere on the site. I looked for a bit and found some good information to try, but none of it worked for this specific example.


    My apologies if this has already been answered. I looked and found similar situations but not this specific one.

    Here's the formula that's in use.


    The result of this formula is "3.25"

    I've made a conditional format where if the cell value is less than 4 it colors the text red.

    If I manually enter the 3.25 in the cell the conditional format works. However if the 3.25 is the result of the VLOOKUP formula, the conditional format does not work.

    I need the conditional format to work on the result of the VLOOKUP formula.

    Is this possible?

    Re: Copy Values Only Of A Specified Range From One Workbook To Another

    If I try to record the actions this is the code it generates

    It's as if it's not talking to the other workbook. Is there something I have to enable in excel to allow this workbook to access other workbooks?[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I figured it out.

    I was opening two separate instances of excel and opening each workbook. I used the same excel window to open up the second workbook and it's working now.

    Thanks for all the help. Hopefully this thread will be helpful to others as well.

    Re: Copy Values Only Of A Specified Range From One Workbook To Another

    I typed the following into the Visual Basic Editor

    Sub ACD_Week_1()
    Workbooks("template.xls").Sheets("ACD").Range("A1").PasteSpecial Paste:=xlPasteValues
    End Sub

    It gives me a runtime error '9' 'subscript out of range' and when I click debug it highlights this row


    Sorry to be such a noob at all this. I've read other threads and I understand what the code is saying, I'm just not sure how to format it in the VB editor properly.

    I have both workbooks open and ready to go and they're all named properly.