How to find a name in a cell

[christiaan89]

Hello,

What i'm looking for is the following:

I have a excel file with 12 tabs. All an unique name.
But what I not want to do is to find someone name when it's printed.

I have made a name list on the first tab, and want a on this tab you can find this person.

Like:
Find: Christiaan89 SHOW tab name

Cell A1
Christiaan89

Cell C1
Find on .......

I don't have a test spreadsheet, and can't share the current one..

[Tony Valko]

Where are the names located on the other sheets? In a specific row or column or scattered anywhere?

Can a name appear on more than one sheet? If so, what should happen?

[christiaan89]

The names you can find on every page in the cells B1:K1
The names are not on multiple page's, only on 1

[Tony Valko]

OK, what are the real sheet names?

You'll have to either list the names in a range of cells or, if the names follow some sort sequential naming pattern, then you can "build" the sheet names directly into the formula.

[christiaan89]

Barcode overzicht (1)
Barcode overzicht (2)
Barcode overzicht (3)

Till

Barcode overzicht 15)

[Tony Valko]

Try this...

Data Range

	A	B
1	Name	On sheet
2	Sad
3	Mad	Barcode overzicht (1)
4	Gad
5	Dad	Barcode overzicht (2)
6	Tad	Barcode overzicht (1)
7	Fad
8	Pad
9	Rad	Barcode overzicht (2)
10	Wad

This array formula** entered in B2 and copied down:

=IFERROR(INDEX("Barcode overzicht ("&ROW(INDIRECT("1:15"))&")",MATCH(TRUE,COUNTIF(INDIRECT("'Barcode overzicht ("&ROW(INDIRECT("1:15"))&")'!B1:K1"),A2)>0,0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

You may have to replace the commas with semi-colons.

[christiaan89]

You are amazing.
Work perfect.
I have spend a night at this but nothing works.

[Tony Valko]

You're welcome. Thanks for the feedback!

[christiaan89]

Just one question further:

Is it possible to get additional info on the index page also.

Like:
next barcode: (Cell B4:K4)
5 digit code: (Cell B3:K3)
The count of entry for that specific name (Cell B2:K2)

And that for all the persons individual?

[Tony Valko]

Need more specific details.

[christiaan89]

On index page cell a4 will Come Total extra's (cell b2:k2 will stand that value)

In index page cell a5 will Come 5 digit code (cell b3:k3 will stand that value)

In index page cell a6 will Come stand the next barcode (cell b4:k4 will stand that value)

What otter info do you need?

[Tony Valko]

Due to our language differences, I'm having a hard time understanding you.

[christiaan89]

Due to our language differences, I'm having a hard time understanding you.

I get that.

In cell A4 on the index sheet I want the value that is in the cell B2 till K2 (on the barcode sheets (here I have sum up the Total barcodes for the name) ) off course from the name I selected in cell a2 on the index sheet

Hopefully you understand the above, if so thanks the next step is the next one

[Tony Valko]

Can you post a SMALL sample file?

It doesn't have to be your real file. Just make up a small sample file with 2 or 3 sheets so I can see your data structure.

Also make sure to include the results you expect.

[sanram]

You can use this formula. I have just edited the formula of Tony Valko to get your desired output :

Please Login or Register  to view this content.

You need to change the red colored range only. Here I have used B4:K4, which will index the data of 4th row. You can change this as you want.

By the by this is also array formula. So confirm Ctrl+Shift+Enter.

[christiaan89]

in the attachment i include a file with just one barcode overzicht page.
My name is in the first row, and in red font I typed what I want.

[sanram]

According to your attached sample :
On D2 :

Please Login or Register  to view this content.

On E2 :

Please Login or Register  to view this content.

On F2 :

Please Login or Register  to view this content.

See the attachment.

[christiaan89]

You can use this formula. I have just edited the formula of Tony Valko to get your desired output :

Please Login or Register  to view this content.

You need to change the red colored range only. Here I have used B4:K4, which will index the data of 4th row. You can change this as you want.

By the by this is also array formula. So confirm Ctrl+Shift+Enter.

When I copy paste your formula, it doen's work. It shows me only Not found...

[sanram]

I have attached a sample on post 17. See this. May be you didn't use Ctrl+Shift+Enter.

[christiaan89]

According to your attached sample :
On D2 :

Please Login or Register  to view this content.

On E2 :

Please Login or Register  to view this content.

On F2 :

Please Login or Register  to view this content.

See the attachment.

In my original file it won't work. It shows me only 0 or Not found.

[christiaan89]

According to your attached sample :
On D2 :

Please Login or Register  to view this content.

On E2 :

Please Login or Register  to view this content.

On F2 :

Please Login or Register  to view this content.

See the attachment.

In my original file it won't work. It shows me only 0 or Not found.

[christiaan89]

Okay, I have entered the three formula's again, and the cells E2 and F2 works.

Either the D2 cell isn't showing the correct amount, it shows me 0 Barcode's...
The formula in the cell is:

=IFERROR(INDEX(INDIRECT("'Barcode overzicht ("&MATCH(TRUE;COUNTIF(INDIRECT("'Barcode overzicht ("&ROW(INDIRECT("1:15"))&")'!B1:K1");A3)>0)&")'!B2:K2");MATCH(A3;INDIRECT("'Barcode overzicht ("&MATCH(TRUE;COUNTIF(INDIRECT("'Barcode overzicht ("&ROW(INDIRECT("1:15"))&")'!B1:K1");A3)>0;0)&")'!B1:K1");0));"Not Found")

[sanram]

Change A3 to A2 on the formula.

[christiaan89]

I have copied the formula in cells D2 to D114
In cell D2 it checks the value of A2
In cell D3 it checks the value of A3

I have made a typo in my post in cell D3 it isn't showing the correct amount, it shows 0 Barcode's instead of 5

[sanram]

Please confirm 2 things first.

1. On which row the Barcode will be found and if the row number varies from sheet to sheet.
2. If there remain any Barcode against that name.

[christiaan89]

on every sheet the total amount will be found in the cell B2:K2 (this value I want to show in Cell Dx on the index page.
The second question I don't understand.

[sanram]

2nd question means:
Say the name on A3 is "ABCD".
It founds on Cell C1 at Sheet "Barcode overzicht (1)".
Now please check if Cell C2 at Sheet "Barcode overzicht (1)" contains any value or not?

[christiaan89]

Yes it does have a value.

[sanram]

Then I have no idea. Because the formula is working fine in my system.
Please see this attachment again, if you can find anything.