Board index » Excel » Value of sheet A cell = sheet B cell => fill in sheet name

Excel68
I have sheet A and three sheets N1, N2, N3



How to have a value "name of sheet" filled in a cell in sheet A when a

value in a cel in A and a cell in one of the sheets N1, N2, N3 is the

same?



Sheet A

10 | |

11 | | X

12 | |



Sheet N1

..

..

..



Sheet N3

..

11

..



X ->N3



I cound't find it out via the Help section.





Bart


-

Assume data to be compared in sheets: N1, N2, N3 are in col A. Data is

assumed unique in each sheet and across all 3 sheets, ie there are no

duplicates



Then in sheet: A, with the data in A1 down,

Put in B1:

=IF(ISNUMBER(MATCH(A1,'N1'!A:A,0)),"N1",IF(ISNUMBER(MATCH(A1,'N2'!A:A,0)),"N2",IF(ISNUMBER(MATCH(A1,'N3'!A:A,0)),"N3","")))

Copy B1 down

--

Max

Singapore

http://savefile.com/projects/236895

xdemechanik

---

"AA Arens" <bartvandongen@gmail.com>wrote in message



-

One way in B1 of sheet A:



=IF(COUNTIF('N1'!A:A,A1)>0,"N1","")

&IF(COUNTIF('N2'!A:A,A1)>0,"N2","")

&IF(COUNTIF('N3'!A:A,A1)>0,"N3","")



(That's one formula in a single cell)



If the value shows up in all 3 sheets, you'll see N1N2N3.



If you only want the first to show up:

=IF(COUNTIF('N1'!A:A,A1)>0,"N1",

IF(COUNTIF('N2'!A:A,A1)>0,"N2",

IF(COUNTIF('N3'!A:A,A1)>0,"N3","")))







AA Arens wrote:

--



Dave Peterson

-

Both solutions offered works, but it needs a few seconds calculating

time. Is the a way to speed it up?



Bart





On Nov 25, 8:01 pm, Dave Peterson <peter...@verizonXSPAM.net>wrote:

-

If you have lots of data, then Max's formula with =match() should be quicker.



AA Arens wrote:

--



Dave Peterson

-