Excel Formulae 'Help'

[FootballvBlues]

Hello all

This is not a freebie hat in hand.. I would happily pay to go on a
course but most courses are aligned to a specific content and asking
q's around a particular problem is sometimes not within parameters of
the course or simply because they dont know themsleves.

Anyhow I would like ask if you can help me. I have several worksheets
within the same workbook. I would like to set up a separate worksheet
that analyses data from these worksheets. I am only interested in a
specific column in all the other worksheets to see how many times a
partcular names appears?

The columns are all similar (ie all B) but every worksheet may vary in
column length (ie some are longer than others). The cells in that
column are formatted with borders. The info within each border
contains a name and beneath that an employee number ( ie occupies 2
cells)

I would like set up a comparison or index in a seperate worksheet that
analayses the data in this particular column to see how many times
the same name or employee number appears? (and if possible reference
from which worksheets it appears more than once)

Can you help with such a formula>

I would be most grateful for any assistance?

Thank you

 

[Pete_UK]

Not sure exactly what you want, but one way is to set up your new
sheet with a list of names in column A (starting in A2, so row 1 is
used as a header row), and then in B1, C1, D1 etc enter your worksheet
names, exactly as they appear on your tabs. Then in B2 you can enter
this formula:

=COUNTIF(INDIRECT("'"&B$1&"'!B:B",$A2)

Note the apostrophe between the first two quotes and immediately
before the the ! - this takes care of any spaces in the sheet names.
Then you can copy this across for as many sheets as you have, then
copy these down for as many names as you have. You will then have a
grid showing how many times the name in column A appears in column B
of the other sheets.

Hope this helps.

Pete

 

[FootballvBlues]

Hello Pete

Thankyou for your reply. Apologies for multi posting. I wrote it again
because I thought the 1st one hadn't gone through.

Anyway I have tried your formula. An error came up saying a parethisis
was missing. I added a bracket on end, and then it said to few
arguments..

To keep it simple(over what I'm after) - let me give an example. In
SHEET1, in column A I have a list of names say a, b, c, d , e, f , g
( just names like john peter etc). In SHEET2 again in column A, I have
names , lets again say, a, b, c, d, e, f, g, h, j. k. l. I have many
sheets like this titled JAN, FEB, MCH APR, ETC..

I want to create a separate worksheet (called say ANALYSIS) which
compares the Column A in all the other worksheets and shows me which
'same names' appear in each worksheet (the number of times the same
name and who it is).

I am most grateful for your help

Thank you

Hal

 

[Pete_UK]

Yes, there was a bracket missing. Sorry - it should have been:

=COUNTIF(INDIRECT("'"&B$1&"'!B:B"),$A2)

Hope this helps.

Pete

 

[FootballvBlues]

Yes, there was a bracket missing. Sorry - it should have been:

=COUNTIF(INDIRECT("'"&B$1&"'!B:B"),$A2)

Hope this helps.

Pete










- Show quoted text -

Thankyou for that

However, I have now entered that and it shows a value of 0. This is
incorrect as i purposely set up two worksheets that had same details
in column A..

The COUNTIF function surely only counts? I would like a function that
determines how many times the same name appears in the same column of
each worksheet and what that name(s) is/are.

I apogise and am grateful 4 ur help. Might it be easier if I could
telephone you to explain my enqury? I ma happy to give you my number .

Ifou cannot help any further it is ok. I will understand and wish u
the best

Hal

 

[Pete_UK]

You can send the file to me here:

pashust <at> auditel.net

Change the obvious.

Pete