Help with drop down selection and populating related data...

[5SpdSolara]

Hi everyone...

I'd like to get some help with creating a drop down list that upon
selection (i.e. A1), will auto-populate related data (from another shee
based upon the original drop down selection) in A2, A3, etc...

Does anyone know how to do this without have to get involved wit
macros, vb?

I can create a drop down list and name selection but that's as far as
can get.

Basically, I'm trying to create a sales order environment, where once
select a particular drop down SKU, the description, weight, price, an
other info (as needed) will auto populate on a particular sales orde
sheet. The data of course would need to be drawn from another sheet.

Thank you all for your help and guidance

 

[GS]

Have a look at the LOOKUP() functions...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[5SpdSolara]

Thanks for the tip Garry... will try it out.

;1612946']Have a look at the LOOKUP() functions...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussio

 

[lhkittle]

Hi everyone...



I'd like to get some help with creating a drop down list that upon a

selection (i.e. A1), will auto-populate related data (from another sheet

based upon the original drop down selection) in A2, A3, etc...



Does anyone know how to do this without have to get involved with

macros, vb?



I can create a drop down list and name selection but that's as far as I

can get.



Basically, I'm trying to create a sales order environment, where once I

select a particular drop down SKU, the description, weight, price, and

other info (as needed) will auto populate on a particular sales order

sheet. The data of course would need to be drawn from another sheet.



Thank you all for your help and guidance!


5SpdSolara

Take a look at this link.
A simple example of formula AND vba code.

The reason I added the code is that if you are selecting items and info about that item, yes a formula will do that. But the next item overwrites the last.

The event code on this example will develop a list of the items you select from the drop down and list them one below the other.

https://www.dropbox.com/s/ko6n5efdkte0eob/Copy MS Forum Example Drop Box.xlsm

Regards,
Howard

 

[Claus Busch]

Hi Howard,

Am Sun, 28 Jul 2013 22:49:10 -0700 (PDT) schrieb (e-mail address removed):

Take a look at this link.
A simple example of formula AND vba code.

why did you create an array?
I would use in C2:
=IFERROR(VLOOKUP($A$1,Sheet2!$A$2:$E$5,COLUMN(B1),0),"")
and copy to F2



Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Tue, 30 Jul 2013 10:19:35 +0200 schrieb Claus Busch:

=IFERROR(VLOOKUP($A$1,Sheet2!$A$2:$E$5,COLUMN(B1),0),"")

please have a look
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "CopyMSForum"
I changed the formula and the code


Regards
Claus B.

 

[lhkittle]

Hi Howard,



Am Sun, 28 Jul 2013 22:49:10 -0700 (PDT) schrieb (e-mail address removed):







why did you create an array?

I would use in C2:

=IFERROR(VLOOKUP($A$1,Sheet2!$A$2:$E$5,COLUMN(B1),0),"")

and copy to F2







Regards

Claus B.

Hi Claus,

No specific reason other than just going with what I know and have used.
Your formula is much less cumbersome than the array entered one and of course easier to maintain. You don't have to select all the cells to make a change and often as not users forget to re-commit with ctrl + alt + shift.

I presume the OP will try your solution and I'm guessing will be able to install
it, adapted to the workbook he/she is using.

I don't see any jpeg's attached and I don't know what an "sku sheet" is.

Regards,
Howard

 

[lhkittle]

Hi Howard,



Am Tue, 30 Jul 2013 10:19:35 +0200 schrieb Claus Busch:






please have a look

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for the workbook "CopyMSForum"

I changed the formula and the code





Regards

Claus B.

Can't argue with the master. The OP should easily be able to adapt the formula you offer but may still struggle dealing with the code as was mentioned.

What would you say is the major drawback to using array vlookup?

Regards,
Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 30 Jul 2013 01:50:22 -0700 (PDT) schrieb (e-mail address removed):

What would you say is the major drawback to using array vlookup?

I avoid arrays so I you can't change a cell in the array. The only way
to do this is to delete the whole array and start again with entering
formulas.
Arrays need more ressources.
But this is only my preference.


Regards
Claus B.

 

[GS]

Have a look at the LOOKUP() functions...

Here's what I use with my PointOfSale app. The template can be toggled
to reflect a Quote, Sales Order, or Invoice but the line items all work
the same...

Column layout:
Qty~Item ID~Item Name~Description~Unit Price~Amount

Once ItemID (SKU) is selected from its dropdown, the following formulas
populate their respective columns:

Item Name:
=IF(ItemID="","",VLOOKUP(ItemID,PriceList,PriceList_Name,0))

Description:
=IF(ItemID="","",VLOOKUP(ItemID,PriceList,PriceList_Description,0))

Unit Price:
=IF(OR(ItemID="",Qty=""),"",VLOOKUP(ItemID,PriceList,PriceList_Price,0))

Amount:
=IF(UnitPrice<>"",ROUND(UnitPrice*Qty,2),"")

The template also provides for over-riding the formulas if desired.
This has been working well since 2004.

The product info is stored on the template for easy editing when
changes dictate. This preserves existing values on previous sheets
since they don't ref a master product list. This is necessary for
archival purposes as the QT/SO/IN sheets are stored in a single file
for 1 fiscal period (Jan-Dec). If the product info is stored on a
master sheet then all sheets ref any changes made after they're issued
to the client. This is clearly not desireable and since the app offers
template editing on-the-fly then it's an easy maintenance solution,
IMO!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion