Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
PAR
 
Posts: n/a
Default Reference multiple cells in if statement

I have combined data from 3 sources on one spreadsheet. The data has been
matched by EE # (same # in from all sources) so all data from the 3 sources
are on the same line for a particular EE #. Columns B through R contain data
from Source 1, columns T through AJ contain data from Source 2, columns AL
through BB contain data from Source 3.

1.) Formula in column A needs to look at data in column B, T & BB.
If there is data all three columns then the cell containing the formula
should be blank.
If there is no data in a column B the formula should return "No data in
Source 1".
If there is no data in multiple columns, the formula should return "No data
in Source 1 or 2", or "No data in Source 2 or 3 or "No data in Source 1 or 3"
whatever the case may be.

2.) I need to varify the data for certain columns plus identify which column
contains different data. Source 1 is the standard to measure the other
sources against. The data contained in column E from Source 1 should be the
same as column V from Source 2 and column AP from Source 3. A formula in
column BC needs to look at the data in E and compare the values of V and AP,
if either has a value different from the value in column E the formula needs
to return €śValue from Source 2 is different€ť or €śValue from Source 3 is
different or €śValue from Source 2 and 3 are Different€ť or €śNo value in
Source 1€ť whatever the case may be.

I tried to evaluate using 2 sources, but ran into problems
If((B30)*(T3=0),€ťData in Source 1, but not in Source 2€ť, €ś €ś) worked, but
it references only the 2 columns.

However, If((T30)*(B3=0),€ťData in Source 2, but not in Source 1€ť, €ś €ś) does
not work. Again I am only referencing 2 of the data sources and I need to
compare all three.

  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

For #1:

=IF(COUNTA(B1,T1,BB1)=3,"","No data in source "&IF(B1="","1
","")&IF(T1="","2 ","")&IF(BB1="","3 ",""))

You should be able to adapt that formula for #2.

Biff

"PAR" wrote in message
...
I have combined data from 3 sources on one spreadsheet. The data has been
matched by EE # (same # in from all sources) so all data from the 3
sources
are on the same line for a particular EE #. Columns B through R contain
data
from Source 1, columns T through AJ contain data from Source 2, columns AL
through BB contain data from Source 3.

1.) Formula in column A needs to look at data in column B, T & BB.
If there is data all three columns then the cell containing the formula
should be blank.
If there is no data in a column B the formula should return "No data in
Source 1".
If there is no data in multiple columns, the formula should return "No
data
in Source 1 or 2", or "No data in Source 2 or 3 or "No data in Source 1 or
3"
whatever the case may be.

2.) I need to varify the data for certain columns plus identify which
column
contains different data. Source 1 is the standard to measure the other
sources against. The data contained in column E from Source 1 should be
the
same as column V from Source 2 and column AP from Source 3. A formula in
column BC needs to look at the data in E and compare the values of V and
AP,
if either has a value different from the value in column E the formula
needs
to return "Value from Source 2 is different" or "Value from Source 3 is
different or "Value from Source 2 and 3 are Different" or "No value in
Source 1" whatever the case may be.

I tried to evaluate using 2 sources, but ran into problems
If((B30)*(T3=0),"Data in Source 1, but not in Source 2", " ") worked, but
it references only the 2 columns.

However, If((T30)*(B3=0),"Data in Source 2, but not in Source 1", " ")
does
not work. Again I am only referencing 2 of the data sources and I need to
compare all three.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Entering same data into multiple cells Gary H Excel Discussion (Misc queries) 3 April 14th 05 09:55 PM
How can I have excel search and add multiple cells to find a targe Blakepro Excel Discussion (Misc queries) 1 April 1st 05 02:37 AM
selecting multiple cells with ctrl key JDW Charts and Charting in Excel 0 March 30th 05 10:49 PM
I Need to change reference sheet for all cells on a form Brent E Excel Discussion (Misc queries) 1 February 11th 05 02:36 AM
background formatting across multiple cells Casper Excel Discussion (Misc queries) 0 November 26th 04 12:18 PM


All times are GMT +1. The time now is 09:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"