Link rows to tables

[SirLunk]

Howdy!

Up for a challenge?

I need to provide auditors with data. Each row in the spreadsheet represents a unique person. In addition to simple, 1 dimensional values such as name, location and occupation being stored in columns, I need to link the people records with 2 dimensional tables of additional information that are unique to each person.

For example:
Bob,39,Mercedes,England,Comedian,[TABLE],Reviewed
Jan,23,Ferrari,Spain,"Dancer",[TABLE],Pending Review
Lunk,30,Toyota,England,ExcelNoob,[TABLE],Pending Review

Bob's [TABLE] might look something like this:

Titanic,"Made me want to learn to sketch",8 stars
Die Hard,"I learned a new phrase!",7 stars
UP,"I cried for Ellie",9 stars

Key considerations:

• Each person's [TABLE] may contain as many as 100 records, although fewer than five is typical.
• Each person's [TABLE] doesn't necessarily need to appear in a column (as indicated above). In fact, I'd prefer it if the [TABLE] information wasn't visible by default. However, it must be easy for auditors to access each person's [TABLE] to review the data by clikcing a button or something...
• Reviewing each person's [TABLE] should not result in the the auditor getting lost in a multitute of spreadsheets. They must be able to click a button (e.g. BACK) and return to the original record.
• Auditors must not lose the ability to sort or filter person records.
• Auditors don't need to sort or filter [TABLE] records

I realise that Excel is probably the wrong tool for the job and a web-based report would be far better suited for this. However, we have no time, skills or money for that (this year), so Excel is being used as a quick and dirty way to provide this audit data. The Excel solution doesn't have to be pretty, but it must be functional and intuitive. Of course the same constraints apply to Excel as they do for web development, so we're looking to avoid loads of custom development.

Any ideas?

Thanks in advance,
Lunk

[royUK]

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.