What I am looking for is a way to locate supplies. I have been told that a
VLOOKUP would work but I cannot seem to come up with the proper syntax. What
I am looking for is to have a dialog box that I would enter the STORE DESC
and have it list all of the items that come under that column with the ROW,
SELF, BRAND DESC.
Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
A 4 Bags HB564 Rich Paper Bags 0.23 2
G 8 Bags HB564 Rich Paper Bags 0.23 2
V 9 Bags HB564 Rich Paper Bags 0.23 2
D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
Thanks in advance for the help.
=Vlookup() returns a single value--not a list.
I'd select the whole range and do data|filter|autofilter.
Then I could use the dropdown arrow in the "store desc" field and choose to see
the item that I want. (There's lots of things you can do with that filter!)
Then data|filter|show all to see everything.
Daniell wrote:
>
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
--
Dave Peterson
Is your data in columns as you have laid it out? If so why not use the Data\Filter\AutoFilter and then you can use the drop down list in the column header to "locate" the Store Desc you want and only those rows will appear that have the Store Desc you selected.
I might be missing something but this seems like it would work for you.
---------------------------------------------------
ONLY APPLIES TO VBA RESPONSES WHERE APPROPRIATE
To insert code into the VBE (Visual Basic Editor)
- Copy the code.
- Open workbook to paste code into.
- Right click any worksheet tab, select View Code
- VBE (Visual Basic Editor) opens to that sheets object
- You may change to another sheets object or the This Workbook object by double clicking it in the Project window
- In the blank space below the word "General" paste the copied code.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
hi Daniell,
Dave & Excellenator has given you good suggestions to use Autofilter.
And to VLOOKUP you need to have at least one column unique so that
VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
column.
Try rearranging your table if possible.
Thanks
Shail
Daniell wrote:
> What I am looking for is a way to locate supplies. I have been told that a
> VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> I am looking for is to have a dialog box that I would enter the STORE DESC
> and have it list all of the items that come under that column with the ROW,
> SELF, BRAND DESC.
>
>
>
> Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> A 4 Bags HB564 Rich Paper Bags 0.23 2
> G 8 Bags HB564 Rich Paper Bags 0.23 2
> V 9 Bags HB564 Rich Paper Bags 0.23 2
> D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> Thanks in advance for the help.
That column to match doesn't have to be the first column in the worksheet--it
just has to be the leftmost column in the lookup range.
If it isn't the leftmost column, then you could use =index(match()) (instead of
=vlookup()).
Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
And the data in the first column doesn't have to be unique--but with a formula
like:
=vlookup(a1,sheet2!a:e,4,false)
Only the data that is on the same row as the first match will be returned.
shail wrote:
>
> hi Daniell,
>
> Dave & Excellenator has given you good suggestions to use Autofilter.
>
> And to VLOOKUP you need to have at least one column unique so that
> VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
> column.
>
> Try rearranging your table if possible.
>
> Thanks
>
> Shail
>
> Daniell wrote:
> > What I am looking for is a way to locate supplies. I have been told that a
> > VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> > I am looking for is to have a dialog box that I would enter the STORE DESC
> > and have it list all of the items that come under that column with the ROW,
> > SELF, BRAND DESC.
> >
> >
> >
> > Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> > A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> > D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> > V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> > A 4 Bags HB564 Rich Paper Bags 0.23 2
> > G 8 Bags HB564 Rich Paper Bags 0.23 2
> > V 9 Bags HB564 Rich Paper Bags 0.23 2
> > D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > Thanks in advance for the help.
--
Dave Peterson
Thanks Guys for all the help.
"shail" wrote:
> hi Daniell,
>
> Dave & Excellenator has given you good suggestions to use Autofilter.
>
> And to VLOOKUP you need to have at least one column unique so that
> VLOOKUP can work normal. Moreover, the STORE DESC must be at the first
> column.
>
> Try rearranging your table if possible.
>
> Thanks
>
> Shail
>
>
> Daniell wrote:
> > What I am looking for is a way to locate supplies. I have been told that a
> > VLOOKUP would work but I cannot seem to come up with the proper syntax. What
> > I am looking for is to have a dialog box that I would enter the STORE DESC
> > and have it list all of the items that come under that column with the ROW,
> > SELF, BRAND DESC.
> >
> >
> >
> > Row Shelf Store Desc Supplier Desc Brand Dec Cost Lead/Days
> > A 3 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> > D 5 Cleaner CLR425 Ultra Hand Cleaner 1.12 3
> > V 4 Cleaner CLR425 Ultra Hand Cleaner 1.23 3
> > A 4 Bags HB564 Rich Paper Bags 0.23 2
> > G 8 Bags HB564 Rich Paper Bags 0.23 2
> > V 9 Bags HB564 Rich Paper Bags 0.23 2
> > D 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > G 5 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > S 3 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > S 6 Rubber Bands RB897 Secure Rubber Bands 0.1 2
> > Thanks in advance for the help.
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks