Value of sheet A cell = sheet B cell => fill in sheet name

A

AA Arens

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
 
M

Max

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
 
D

Dave Peterson

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","")))
 
A

AA Arens

Both solutions offered works, but it needs a few seconds calculating
time. Is the a way to speed it up?

Bart
 
D

Dave Peterson

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top