Excel Search
 

I'm trying to set up a way to search for information on individuals in a
worksheet that's pretty long (some 20,000 rows). The way it's set up now is
that we sort ascending, expand the search, find the name of the person we're
looking for, then look for what we want from there. The problem with this is
that the data is monthly information from March of 2004, and has been set up
until December of 2006 (linked to several other workbooks), and will expand
on indefinitely into the future. This means if I want to know what John Jones
did for the last year, I need to look through 34 entries to find what I want,
copy and paste it into another workbook, then find averages, etc from there.
Still MUCH better than looking through 20,000 rows, but what I'd like to do
is pick from a drop-down list in a ComboBox on a UserForm a name, then choose
a date range, I assume a ComboBox also or maybe option buttons, so that only
the needed information is displayed, along with averages, etc, and can then
be printed. Each person's name and data are on one row, and there are 15
columns of data.

I'm building this for a user who is all but computer illiterate, so the
easier to run the better. I'm getting better with VBA, but still have a very
long way to go, and can't figure out how to make this work. I know Access or
Crystal Reports would be great for this, but I can't use those. Any
suggestions? Thanks for your help.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200507/1

Excel Search
 

hi, Frank !

... set up a way to search for information on individuals... (some 20,000 rows)
... now... we sort ascending, expand the search, find the name... then look for what we want
... the data is monthly information from March of 2004... until December of 2006
... (linked to several other workbooks), and will expand on indefinitely into the future
... to know what John Jones did for the last year, I need to look through 34 entries
... copy and paste it into another workbook, then find averages, etc from there
... I'd like to... pick from a drop-down list... a name, then choose a date range
... so that only the needed information is displayed, along with averages, etc, and can then be printed
... I'm building this for a user who is all but computer illiterate, so the easier to run the better
I'm getting better with VBA, but still have a very long way to go, and can't figure out how to make this work [...]

some ideas...
- create a named dynamic range w/ unique individuals' name entries
http://www.cpearson.com/excel/duplicat.htm
- link that named range to a data validation list [a cell in another worksheets for names picking]
you will be able to 'detect' for the selected name which are the 'matching rows'
- do as above -for 2 more validated cells- to choose a date range [from...to]
- 'fill' a summary report 'extracting/formulating' based on 'rows-count' for 'matching-entries'
- names as 'Print_Area' can also be re/defined as dynamic ranges
- [probably] you wili need no vba [or maybe just a few]

I can not figure out about your actual data lay-out, [for a more detailed examples] but...
if you think this may work and you want send to me [a sample of] your data [NO linked workbooks]...
please, fell free to do so {just remove 'NO...SPAM...PLS' fom my e-mail address]

hth,
hector.

Excel Search
 

I don't see your address.

Héctor Miguel wrote:
hi, Frank !

... set up a way to search for information on individuals... (some 20,000 rows)
... now... we sort ascending, expand the search, find the name... then look for what we want
[quoted text clipped - 6 lines]
... I'm building this for a user who is all but computer illiterate, so the easier to run the better
I'm getting better with VBA, but still have a very long way to go, and can't figure out how to make this work [...]

some ideas...
- create a named dynamic range w/ unique individuals' name entries
http://www.cpearson.com/excel/duplicat.htm
- link that named range to a data validation list [a cell in another worksheets for names picking]
you will be able to 'detect' for the selected name which are the 'matching rows'
- do as above -for 2 more validated cells- to choose a date range [from...to]
- 'fill' a summary report 'extracting/formulating' based on 'rows-count' for 'matching-entries'
- names as 'Print_Area' can also be re/defined as dynamic ranges
- [probably] you wili need no vba [or maybe just a few]

I can not figure out about your actual data lay-out, [for a more detailed examples] but...
if you think this may work and you want send to me [a sample of] your data [NO linked workbooks]...
please, fell free to do so {just remove 'NO...SPAM...PLS' fom my e-mail address]

hth,
hector.


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200508/1