Creating a Summary Sheet with Drop down options

**PaulKetley** · 10-03-2011, 12:54 PM

Hello

This is my first post here, so go easy on me!

I am trying to design a Summary Page (if that's the right term) which (via a drop down) will display data from another sheet.

Let me explain. The datasheet contains the fields "Product ID, Product Name, Product Author, Version No, Product Status, Comments etc"
On the Summary Sheet i want to be able to use the drop down to select either a Product ID, Name or Author and display the relevent assosciated data.

I've attached an example spreadsheet.

Having been trying to work this out, as want ot be able to teach myself but have failed so far...

Any help appreciated.

Thanks

Paul

**royUK** · 10-03-2011, 01:39 PM

Use VLOOKUP

**tlafferty** · 10-03-2011, 01:39 PM

Only one dropdown was needed (product ID). The rest was accomplished through the VLOOKUP formula. Check out Excel help to see how it works. Look at the data validation help topic to see how the dropdown for product ID was created (hint: I used a named range and referred to it in a formula.)

**PaulKetley** · 10-10-2011, 11:38 AM

It took me a little while to work it out but i did...

Very much appreciated!

**PaulKetley** · 10-25-2011, 07:13 AM

Hi Thomas...

I've done some extra work, post what you showed me. I'm not sure if this should be in a new post (i'm not sure how it works here!) but thought i'd reply here first.

I decided that i would add 'floating' drop down lists See <Product Summary (3)> and was impressed that i got them to function right by using the useful INDIRECT formula.

However there are a couple of problems i have. 1 minor 1 major.

I can't for the life of me work out how to change the look of the dropdowns. I want to make the font bigger and matching the rest of the text. I've searched and searched but can't find a solution. It's probably really simple, but not being an expert i thought i would ask!

Secondly, after setting up my ranges for the dropdown, i realised that the empty rows on the sheet (which will over time be filled in with new 'products') are displayed, so when you scroll down you can scroll through 10000s of blank rows. i thought i had solved this by limiting my range to the line under the current one. However en add new items, i constantly have to keep changing the range so it all works. Is there a formula to make the range 'expand' on addition of a new row of data?

any help much appreciated

Paul