Full Version: If then?
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
natashaepperson
Apr 19 2006, 10:39 AM
I've got 2 colums of data i'm pulling from a database and pulling into excel as a database sql qry.
What I want to do is on another spreedsheet create a new set of coloms that this would happen:
If I enter a number in Colum A and it ='s a cell in the column in other spreedsheet it will "copy" the data from spreedsheet 2.

Sheet 1- Colum A and Colum D
Sheet 2- Colum A and Coum D

If Sheet 1 Colum A = Sheet 2 Coum A then Sheet 1 Colum D = Sheet 2 Colum D

hope I explained that well enough.
4fit
Apr 19 2006, 12:43 PM
You could place something like this in column D of Sheet1.
CODE
=IF(ISERROR(INDEX(Sheet2!D:D,MATCH(Sheet1!A2,Sheet2!A:A,0))),"Not Found",INDEX(Sheet2!D:D,MATCH(Sheet1!A2,Sheet2!A:A,0)))
4fit
Apr 19 2006, 01:21 PM
This would also work and is a little less hectic::
CODE
=IF(COUNTIF(Sheet2!$A:$A,Sheet1!$A:$A)=0,"Not Found",INDEX(Sheet2!$D:$D,MATCH(Sheet1!$A:$A,Sheet2!$A:$A,0)))
natashaepperson
Apr 19 2006, 01:42 PM
hrm not working, see attached?
4fit
Apr 19 2006, 01:53 PM
You left some of my default sheet names in the formula. This works:
CODE
=IF(COUNTIF(PARTDESCRIPTION!$A:$A,test!$A:$A)=0,"Not Found",INDEX(PARTDESCRIPTION!$B:$B,MATCH(test!$A:$A,PARTDESCRIPTION!$A:$A,0)))


However, since you have some of the ranges named, I would use those names in the formula to simplify things a bit:
CODE
=IF(COUNTIF(PARTNO,test!$A:$A)=0,"Not Found",INDEX(DESCRIPTION,MATCH(test!$A:$A,PARTNO,0)))


You could rename your selection range (column A in "test" sheet) and use all named ranges in the formula. In this example, I named column A in "test" sheet to SelectionList:
CODE
=IF(COUNTIF(PARTNO,SelectionList)=0,"Not Found",INDEX(DESCRIPTION,MATCH(SelectionList,PARTNO,0)))


HTH
natashaepperson
Apr 19 2006, 02:28 PM
awesome!!! thank you so much! i tried to figure it out myself but was totally lost lol.
4fit
Apr 19 2006, 02:53 PM
No problem! Glad to help!!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.