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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 25 Nov 2006 12:20 GMT

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

Reply to this Message

Max - 25 Nov 2006 12:57 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I have sheet A and three sheets N1, N2, N3
>
[quoted text clipped - 22 lines]
>
> Bart

Reply to this Message

Dave Peterson - 25 Nov 2006 13:01 GMT

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

> I have sheet A and three sheets N1, N2, N3
>
[quoted text clipped - 22 lines]
>
> Bart

Signature

Dave Peterson

Reply to this Message

AA Arens - 26 Nov 2006 02:09 GMT

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

Bart

> One way in B1 of sheet A:
>
[quoted text clipped - 41 lines]
>
> Dave Peterson

Reply to this Message

Dave Peterson - 26 Nov 2006 03:10 GMT

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

> Both solutions offered works, but it needs a few seconds calculating
> time. Is the a way to speed it up?
[quoted text clipped - 46 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Rate this thread: