Question about Oracle Database Enterprise Named User Plus

I have a three sheet excel document I want to compare a cell range on sheet3 if any of the cells in the range of C2 - G2 has the same value as any of the cells in the range of A10 - S10 I want to return a value of x on sheet 1.

Ad

I imagine that the only way is with VBA coding. This is possible if you know programming in Excel (VBA - Visual Basic for Applications).

Posted on Apr 23, 2009

Ad

Hi,

A 6ya expert can help you resolve that issue over the phone in a minute or two.

Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.

The service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).

click here to download the app (for users in the US for now) and get all the help you need.

Good luck!

Posted on Jan 02, 2017

Ad

select the cell, type in the bar ( above the sheet ) 2003-01-01/2007-01-01

That will be seen as a range by excel

That will be seen as a range by excel

Feb 21, 2012 | Microsoft Computers & Internet

Try using "count if blank"

I am assuming that you need the value to be on sheet 3

If you want the value to be on another sheet, it is more complicated, you will have to "paste special" the value to a different sheet.

I am assuming that you need the value to be on sheet 3

If you want the value to be on another sheet, it is more complicated, you will have to "paste special" the value to a different sheet.

Jul 22, 2010 | Microsoft Office Excel 2007

You can use IF and ISBLANK. Put this formula on Sheet 1 D1:

=IF(ISBLANK(Sheet3!AM2),"x","")

You can replace "x" by any other value you need.

=IF(ISBLANK(Sheet3!AM2),"x","")

You can replace "x" by any other value you need.

Mar 04, 2010 | Microsoft Excel for PC

Below is the formula for "2". The logic should be obvious from this so you can adapt for "1" or a combination of 1 and 2. This need to be written as one long formula so I have pasted it in to this reply so you can do a copy and paste into your spreadsheet. Sorry its not very readable in this form but you could paste into word so you can break it down and examine structure.

=IF(OR((AND(Sheet3!A12="WA",OR(Sheet3!AH2="Lewis",Sheet3!AH2="Pacific",Sheet3!AH2="Wahkiakum"))),(AND(Sheet3!A12="OR",OR(Sheet3!AH2="Clackmas",Sheet3!AH2="Multnomah",Sheet3!AH2="Washington")))),"X"," ")

=IF(OR((AND(Sheet3!A12="WA",OR(Sheet3!AH2="Lewis",Sheet3!AH2="Pacific",Sheet3!AH2="Wahkiakum"))),(AND(Sheet3!A12="OR",OR(Sheet3!AH2="Clackmas",Sheet3!AH2="Multnomah",Sheet3!AH2="Washington")))),"X"," ")

Aug 18, 2009 | Microsoft Office Excel 2007 Full Version...

Well the symbol for greater than would be => and for lesser than would be +< so if you can incorporate that into your formula it should work. Something like A2=>Sheet3.

Jun 11, 2009 | Microsoft Excel for PC

You need to change the =IF(AND to =IF(OR

Using AND is saying both conditions must be true. Using OR is saying either condition 1 or 2 need to be true

Using AND is saying both conditions must be true. Using OR is saying either condition 1 or 2 need to be true

Apr 16, 2009 | Oracle Database Enterprise Named User Plus

You were of to a good start. Try using the AND function in the IF Formula as follows:

=IF(AND(Sheet3!H2="Clark College",ISBLANK(Sheet3!I2)),"X"," ")

=IF(AND(Sheet3!H2="Clark College",ISBLANK(Sheet3!I2)),"X"," ")

Apr 15, 2009 | Oracle Database Enterprise Named User Plus

Use the IF and Date functions together. On sheet 3, put the birthday (3/15/1954) in cell B1, put the beginning date (1/1/1946) in cell C1 and the ending date (12/31/1964) in cell D1.

Then on Sheet 1, but the following formula in the cell you want an X to be placed in.

=IF(AND(Sheet3!B1>=Sheet3!C1,(Sheet3!B1<=Sheet3!D1)),"X"," ").

Make sure you type the formula Exactly as it appears.

Then on Sheet 1, but the following formula in the cell you want an X to be placed in.

=IF(AND(Sheet3!B1>=Sheet3!C1,(Sheet3!B1<=Sheet3!D1)),"X"," ").

Make sure you type the formula Exactly as it appears.

Mar 20, 2009 | Oracle Database Enterprise Named User Plus

=VLOOKUP(A2;Sheet1.$A$3:D27;2;0)

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets

A2 is the cell I want to look up

Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $

2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns

0 is the value to complete the formula

The cell I created this formula in was Sheet 3 Cell C9 - to show the different sheets

A2 is the cell I want to look up

Sheet1.A3:D27 is the range of cells that contains the data I want to return, The first column relates directly to cell C9 is Sheet 3. I locked the first cell in my range as I wanted to apply the same formula across other cells hence the $

2 is the number of the column that has the data I want to return, I had a choice in this formula of 4 columns

0 is the value to complete the formula

Feb 11, 2009 | Microsoft Excel for PC

Nope, sorry, although I am truly an expert at Excel formulas, I do not understand what you are trying to end up with in the final cell. We can compare a specified field with two spreadsheets - use named ranges and index/match lookup formulas. But then where you really lose me is in reading "a generic field" to find a match, and then placing what "data from another field" into what "other sheet" - ? See the confusion?

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Best way to compare 2 given parameters would be to use a nested if formula, with index/match combo. Here is a simple Excel example of how such a formula could be structured:

Sample Data (columnar arangement):

A1: Part B1: Code C1: Price D1: Find Part E1: Find Code

A2: x B2: 11 C2: 5.00 D2: y E2: 12

A3: x B3: 12 C3: 6.00 D3: y E3: 11

A4: y B4: 11 C4: 7.00 D4: x E4: 12

A5: y B5: 12 C5: 8.00 D5: x E5: 11

To retrieve the price for part y with code 12 and return the value to cell F2, type the following formula in cell F2:

=INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0))

Press CTRL+SHIFT+ENTER to enter the formula as an array formula. The formula returns the value 8.00.

To take this one step further, with range names, this example will find one value at a specified location which matches a specific row header value and column header value. Let's say the range is home values (Range=HomeVal), Column A of HomeVal contains street addresses,"row headers" (Range=StAddress), and Row 1 contains dates of the various values that are in the body of the table, "column headers" (Range=Dates). To return the specific value from the range HomeVal to another sheet, where A1=address specified and A2=date specified:

=INDEX(HomeVal,(MATCH($A$1,StAddress,0)),(MATCH($A$2,Dates,0)))

Then make sure to press CTRL+SHIFT+ENTER to enter the formula as an array formula - if you only hit enter, these types of formulas will not work properly.

Please post back if you need further help, with more details, otherwise thank you for using and rating FixYa!

Jul 08, 2008 | Microsoft Computers & Internet

281 people viewed this question

Usually answered in minutes!

×