Posts by slackmaster

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.

Example:

"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.

forum.ozgrid.com/index.php?attachment/35736/

Re: Search Cell for Multiple Criteria with a List

Hello,

Attached is an example workbook. I'm using excel 2003 (all that's available at my job). Thanks for the help.

forum.ozgrid.com/index.php?attachment/35732/

Hello,

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:

=SEARCH(A1,(B1&"*"),1)

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:

=SEARCH(A1,(B1:B100&"*"),1)

It doesn't work.

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

Hello,

I have a cell with the following formula:

=IF(ISERROR(AVERAGE(A2,C2,D2)),"",(AVERAGE(A2,C2,D2))

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.

Re: Conditional Format Result Of Formula

The table for the VLOOKUP was copied from other cells that were formatted as text. Good call Bert01. Thanks for the assistance.

This website is awesome!!!

Hello,

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.

=IF(ISNA(VLOOKUP(E10,Sheet1!$O$1:$V$1000,4,FALSE)),"",VLOOKUP(E10,Sheet1!$O$1:$V$1000,4,FALSE))

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

Sub testing()
'
' testing Macro
' Macro recorded 05/12/2008 by Rick
'


'
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
        False
End Sub

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("drc_mtd_tx200804.xls").Sheets("APR").Range("C479:Y529").Copy
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

Workbooks("drc_mtd_tx200804.xls").Sheets("APR").Range("C479:Y529").Copy

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.

I'm trying to copy values only from a specified range on one workbook to another.

Example:

Copy the values only (not the formulas or formatting) from cells C479:Y529 on "sheet 1" of "workbook A"

to Cell A1 on "sheet 1" of "workbook B".

Any help would be appreciated.