track number between two tabs on same workbook

[ThierryR]

I'm trying to see if the same numbers exists in two different spreasheets on the same workbook. Both "asset tags" columns are sorted ascending. If the number appears on both table I would like my formula to add a "Y" (for yes) on the proper cell. Not sure if I should use Vlookup, sumproduct or sumif formula. Here is the main table where I want that "Y" to appear in Column D.
Thanks for any help
Thierry

Fixed Asset FAP.xls
	A	B	C	D
1	ASSETTag	Check?	DESCRIPTION	MajorEquipment
2	09436		SHAPERDELTAWOOD
3	011990		KEYCABDELUXEFLOORTYPE
4	013075		TALLESCOPE19FT
5	039218		TRACTOR51FORD8N409655W/ACC
6	047289	X	TRUCKLIFTELECTW125-90-2139
7	047290		CHARGERBATTERYB-30485
8	048187		SAWPOWERMATIC10INSN066596
9	048384		LATHE13X48SNB10729USED
Sort by - Asset Tag

 

[jeffreybrown]

Hi Thierry,

Give this a try. Change the columns and/or sheet names to match your data.

=IF(COUNTIF(Sheet2!$A$1:$A$8,A1)>0,"Y","")

 

[ThierryR]

Jeff,
Work great I did not think about "Countif" formula, I should have.
I apreciate your help it did fix my issue.
Thanks Thierry

 

[jeffreybrown]

Glad it helped.

 

[ThierryR]

Jeff,
I have a related question, on a different tab in the same workbook I would like to add the check mark from column 2 in this workbook, but I'm not sure what formula to use considering that my "different" tab is not sorted by "Asset Tag" but by another column.

Look below that other tab and what formula should I use in the column B?
Thanks again
Thierry

Fixed Asset FAP.xls
	A	B	C	D
1	ASSETTag	Check?	DESCRIPTION	BookValue
2	113195		MONITORFLATPANELCTX17"
3	113196		MONITORFLATPANELCTX17"
4	54922		BLOWERDAYTONMD2C979
5	58589		DISPLAY,USERINFO4'X51/2
6	58590		DISPLAY,USERINFO4'X51/2
7	58771		ENCLOSUREMOUNTING4997-02BSN16
8	59174		TERMINALCRT925SN8094
9	63090		TYPEWRITERCANONMDAP550
Sort by - Organization

 

[jeffreybrown]

Hi Thierry,

I got a little lost in this explanation. Is it possible to post a shot with a result so I can see how you expect the two sheets to link?

 

[ThierryR]

Yes, first sheet has check box that is mark with an "X" (see sheet 1 below). On Sheet 2 (see below) I want this "X" mark to be repeated on the correct Cell in column B knowing perfectly that in Sheet 2 my column "asset num" is not sorted correctly.
Hope it's clearer.
Thierry

Sheet1:

Fixed Asset FAP.xls
	A	B	C	D
1	ASSETTag	Check?	DESCRIPTION	MajorEquip.
2	09436		SHAPERDELTAWOOD
3	011990		KEYCABDELUXEFLOORTYPE
4	013075		TALLESCOPE19FT
5	039218		TRACTOR51FORD8N409655W/ACC
6	047289	X	TRUCKLIFTELECTW125-90-2139	Y
7	047290		CHARGERBATTERYB-30485
8	048187		SAWPOWERMATIC10INSN066596
9	048384		LATHE13X48SNB10729USED
10	048568		TURFAERATORGREENAIRE
Sort by - Asset Tag

Sheet 2:

Fixed Asset FAP.xls
	A	B	C	D
1	ASSETTag	Check?	DESCRIPTION	BookValue
2	113195		MONITORFLATPANELCTX17"
3	113196		MONITORFLATPANELCTX17"
4	54922		BLOWERDAYTONMD2C979
5	58589		DISPLAY,USERINFO4'X51/2
6	58590		DISPLAY,USERINFO4'X51/2
7	58771		ENCLOSUREMOUNTING4997-02BSN16
8	59174		TERMINALCRT925SN8094
9	63090		TYPEWRITERCANONMDAP550
10	63830		TYPWRTR,CANONELEC16K
11	64373		TYPEWRITER-ELECTRONICW/16K
Sort by - Organization

So basically I want to repeat in sheet 2 column B the "X" sign I enter manually in sheet 1 column B.
Thanks
Thierry

 

[James01]

Perhaps what you need is a vlookup. Paste the formula below in B2 Sheet 2 and then copy paste down

=+VLOOKUP(A2,sheet1!$A$2:$B$119,2,FALSE)

 

[jeffreybrown]

Okay try this in the sheet where you want the X's. Adjust your ranges to match your sheet.

=IF(ISNUMBER(INDEX($A$1:$A$8,MATCH(A1,Sheet1!$A$1:$A$8,0))),"X","")

Also, instead of entering the first X manually why don't you use

=IF(D1="Y","X","")

 

[ThierryR]

Thanks for your idea, I will try it.
Thierry