Searching

  • Thread starter Thread starter sparx
  • Start date Start date
S

sparx

Can anybody help me by providing a formula to look down a column withi
Excel that has a defined name, and list all the row numbers from tha
column that matches a cell content - example - In sheet 1, I have
column with loads of peoples first names and in sheet 2, I want to loo
for the name "Frank" so in in sheet 2 there is a box I write "Frank" an
below that all the row numbers from sheet 1 where "Frank" is writte
would be listed in numeric order - this then would allow me to inde
those row numbers to get other detail
 
sparx said:
Can anybody help me by providing a formula to look down a column within
Excel that has a defined name, and list all the row numbers from that
column that matches a cell content - example - In sheet 1, I have a
column with loads of peoples first names and in sheet 2, I want to look
for the name "Frank" so in in sheet 2 there is a box I write "Frank" and
below that all the row numbers from sheet 1 where "Frank" is written
would be listed in numeric order - this then would allow me to index
those row numbers to get other details
Click to expand...
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, enter the
following and fill down:

=INDEX(ArrayMatch(FrankCell,definedName),ROW(A1),1)+ROW(definedName)-1

Alan Beban
 
Thanks for helping - I wrote the formula but the answer I receive i
#NAME? - is this because I use Excel-XP and dont have extra formula'
installed
 
sparx said:
Thanks for helping - I wrote the formula but the answer I receive is
#NAME? - is this because I use Excel-XP and dont have extra formula's
installed?
Click to expand...
If you want to use the solution I posted you need to download the freely
downloadable file from http://home.pacbell.net/beban and make it
available to your workbook. The version of Excel doesn't matter.

Alan
 
sparx said:
I tried this but PC freezes
Click to expand...
I don't know what steps you took, but you need to download the file from
the site and save it as an add-in. Then in the workbook in which you
want to use it, in the VB Editor select Tools|References and check the
name of the saved file in the list of references.

I don't know what other information I can provide.

Does your PC freeze with other downloads?

Alan Beban
 
:confused: Alan, I downloaded the file and opened it in excel - enablin
the macro. When I look in the functions, I can see all the extra arra
functions. What I am trying to do is to look down a column of name
and I need all the row numbers a name appears - say I have loads o
entries of "Alan" with differign surnames and I want to search fo
alan, I want a column of row numbers to appear under my search - iv
been using the =match then =Index the match number to do this. Th
only thing is - is that it must be a formula not a macro - an
suggestions
 
sparx said:
:confused: Alan, I downloaded the file and opened it in excel - enabling
the macro. When I look in the functions, I can see all the extra array
functions. What I am trying to do is to look down a column of names
and I need all the row numbers a name appears - say I have loads of
entries of "Alan" with differign surnames and I want to search for
alan, I want a column of row numbers to appear under my search - ive
been using the =match then =Index the match number to do this. The
only thing is - is that it must be a formula not a macro - any
suggestions?
Click to expand...
:confused: Do you mean that you have first name and surname in the same
cell? Perhaps an illustration would be helpful showing the contents of
A1:B5, the formula you are using and any other relevant data.

Alan Beban
 
Back
Top