Home 
Search 
Today's Posts 
#1




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Entering same data into multiple cells  Excel Discussion (Misc queries)  
How can I have excel search and add multiple cells to find a targe  Excel Discussion (Misc queries)  
selecting multiple cells with ctrl key  Charts and Charting in Excel  
I Need to change reference sheet for all cells on a form  Excel Discussion (Misc queries)  
background formatting across multiple cells  Excel Discussion (Misc queries) 