Copy Data based on selection in drop down control

**smssms** · 04-25-2013, 01:39 PM

I'm trying to copy data from one sheet to another sheet automatically based on a number selected in a drop down control.

I have used OFFSET to pull some information successfully from a list. This is easy for me to do with single entries and a list and has worked well. I want to do similar but with a dataset not just single cells.

But, I can't seem to make OFFSET work to show a set of data easily.

Perhaps I will need VB code to do so? Or there is another control I'm not aware of? I've done very little with VB.

I'll attach a sample file to try to show better what I'm trying to do.

Thanks for any help.

**newdoverman** · 04-25-2013, 03:19 PM

I named all the ranges on the Data sheet using Table and the number assigned to the range of data. I entered the word "Table" in Data!A1.

I then selected the range L6:O22 and entered this array formula:

Formula:

Please Login or Register  to view this content.

See the attachment to see how it works.

**scott.s.fower** · 04-25-2013, 03:19 PM

hope this works for you

sample (3).xlsx

**scott.s.fower** · 04-25-2013, 03:26 PM

hey, newdoverman, that's cool, a lot more efficient than my method - i will use that a ton in the future

you could just do =INDIRECT("table"&J5) instead, so you don't need the word Table in Data!A1

smssms, still take a look at mine, i set it up so you don't need the 2 in cell A21 or the awkward looking dropdown linked to A1, just use a data validation cell in J4 or actually you could put it in the cells above Result, Result 2, Result 3, Summary Data so you don't need a cell in the J column, just to make is a bit nicer

PS my method you don't need named ranges if you have tons of part numbers you will have tons of ranges to name

**newdoverman** · 04-25-2013, 03:45 PM

I agree that Data Validation is the way to go. I took a look at your example and also agree with you about A21 and J4.

I did try the =INDIRECT("Table"&J5) but I must have been making a typo because I kept getting an error. In the end, I resorted to Data!A1 with Table entered in the cell.

This sample is a combination (done quickly) combining both of our ideas....I think.

**smssms** · 04-25-2013, 09:31 PM

Thank you both for your help! THis helps a lot! I had the thought of going the data validation route after stepping away from the data for a few hours. I appreciate your help, these skills will be very helpful for me!

**smssms** · 04-25-2013, 11:35 PM

I have tried to play with these functions. When I use INDIRECT I am always getting #VALUE error. I've tried with writing in the cell and without. Am I missing something? I've never worked with INDIRECT before.

Can I type INDIRECT in one cell at a time or do I need to create an array somehow then paste it in?

I'm still trying to make the long formula from Scott work as well without error. Taking some time :-)

**smssms** · 04-26-2013, 08:53 AM

I figured it out and it's working! Thank you so much!!

Copy Data based on selection in drop down control

LinkBack

Thread Tools

Rate This Thread

Display

Copy Data based on selection in drop down control

Re: Copy Data based on selection in drop down control

Re: Copy Data based on selection in drop down control

Re: Copy Data based on selection in drop down control

Re: Copy Data based on selection in drop down control

Re: Copy Data based on selection in drop down control

Re: Copy Data based on selection in drop down control

Re: Copy Data based on selection in drop down control

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions