Stuck! Please help

[Jeff]

I have inherited a HUGE spreadsheet that probably needs to be converted to an
Access Database.

However, I have been tasked with making the info retrievable in its present
format.

Please give me suggestion for the following scenario:


10 columns/labels: Employee, Name, location, id#, Rating, etc.

2500 rows or records.

I would like to enter the employee name on a separated spreadsheet, and have
all of that employee’s records, or rows, displayed.

Thank you!!

Jeff

 

[Pete_UK]

Are you implying that you might have more than one record for an
employee? Is the ID# unique - it would be better to use this rather
than name to specify who you want to find.

Pete

 

[Mike H]

Jeff

Your data on sheet 1 colmna A - J

Enter a name in a1 of sheet 2
Put this in b1 of sheet 2 and drag right 9 columns

=VLOOKUP($A$1,Sheet1!$A$1:$J$2500,COLUMN(B1))

Mike

 

[Mike H]

Maybe a bit better

=IF($A$1="","",VLOOKUP($A$1,Sheet1!$A$1:$J$118,COLUMN(B1)))

Mike

 

[Dave Peterson]

And since the OP is matching on a name, I would think that he would want an
exact match.

=IF($A$1="","",VLOOKUP($A$1,Sheet1!$A$1:$J$118,COLUMN(B1),False))

 

[Jeff]

Yes!

More than one record for an employee and there is no unique number.

 

[Pete_UK]

Jeff,

Here's a link to a post I answered a few days ago which is similar to
yours:

http://groups.google.com/group/microsoft.public.excel.misc/browse_frm/thread/597b63d37affa313?q=

Perhaps you can adapt the suggestions there to your situation -
essentially, make a unique linked reference for each record in a
helper column (based on name), and then use that to bring multiple
records together on another sheet.

Hope this helps.

Pete

 

[Jeff]

Rock on!

U DA MAN!


Would you agree, however, that ACCESS would be a better choice for this?

 

[Pete_UK]

Thanks for the feedback, Jeff.

You described it as a HUGE database, but Excel comfortably handles
2,500 records - versions before XL2007 could manage 65,536 records in
each sheet - so why complicate it by using Access?

Pete

 

[Jeff]

Not sure....but couldn't I then pull up records with multiple criteria easy?

For example, I want to pull up all "excellent" reviews in "Deparment"
Maintenance between "dates" of 1-1-04 & 1-1-07?
--
Jeff

Thanks for the feedback, Jeff.

You described it as a HUGE database, but Excel comfortably handles
2,500 records - versions before XL2007 could manage 65,536 records in
each sheet - so why complicate it by using Access?

Pete

 

[Pete_UK]

Well, you can do that in Excel if you apply filters to the Department
and Date columns (interactively), whereas in Access you would have to
write a report to do the same thing (but then it would be there
forever, and you could re-run it).

You can also use Advanced Filter in Excel for more complex
relationships, and set up formulae using SUBTOTAL, SUMIF, COUNTIF and
SUMPRODUCT for summary counts and totals.

I analyse telephone call records for my clients each month. Some of
these have 40,000 to 50,000 records, but I can manage quite well using
Excel to produce various tables and graphs automatically - I just copy/
paste the new data in each month and then I have my reports on
separate sheets.

It all depends on what you are happier using - I'm an Excel man,
myself.

Pete

 

[Jeff]

I agree completely! I prefer Excel.

My challenge is that another user (very limited experience) is the person
who needs to access the data on a regular basis.