Gary's Student:
Thanks again for your fast answer - I love this forum - and feel blessed to
have found someone so helpful!
Here's the info in the first 5 lines (my columns run B,C,D,E) (my rows are
15-19) on my working order sheet:
270017 #REF! Carob Coated Peanuts Non Hyd Oil 23.50
201244 12 oz Honey, OG Imp 6.75
270018 10# Carob Coated Raisins Non Hyd Oil 24.50
331604 16 oz. Cinnamon, Ground 2.60
331034 16 oz. Cumin, Ground 5.70
Where you see the #REF! - the formula I have in that field is this:
=VLOOKUP(B15,M1:M184,2)
My data is in columns M-P starting at row 1 - as you can see through 184 -
so far. I need to add hundreds of more products but will do so once I get
the linked order page set up.
Hope that is what you need to help me. Every time I tried to create the
table using the Data, Table command from the drop down menu, I can't figure
out what cell(s) to reference for the input row cell reference and the input
column cell reference. Perhaps that is my only problem is that I don't have
that referenced yet - at least that is what I discerned from the "#REF!"
error code information I did find on the help screen.
Thanks and God Bless!
"Gary''s Student" wrote:
> To get this to work, we need some specifics. I need you to:
>
> 1. post the data in the first four or five rows, that is E1 thru H5
> 2. set-up the "find value" to find the third row.
> 3. if you have modified the equations I gave, post them as well
>
>
> Here is an example:
>
> In E1 thru H5 we put:
> 170113 soap OG 3.75
> 170114 amonnia AQ 1.56
> 170115 roses PL 10.45
> 170116 hammer HW 19.95
> 170117 toothpaste DR 3.54
>
> In A1 we put:
> 170115
>
> In B1 thru D1 we put:
> =VLOOKUP(A1,E1:H100,2) =VLOOKUP(A1,E1:H100,3) =VLOOKUP(A1,E1:H100,4)
>
> and they display:
> roses PL 10.45
>
> --
> Gary''s Student - gsnu200772
>
>
> "goalonggirl" wrote:
>
> > Gary's Student,
> > Thank you again for such a fast answer. I find that the original solution
> > will probably work for my needs, my only problem now is I can't quite figure
> > how to create a table in Excel? I've entered my data into the cells in 4
> > consecutive columns, and when I went and typed in the formula you gave -
> > adjusting the names as needed for the columns/rows needed, I get an error of
> > REF#. Upon further investigation, I find I must actually create the table -
> > but when I do try that - I get an error of cell input reference invalid.
> > I've searched the online database and tutorials as well as general help for
> > my solution - and there just isn't anything out there that tells you WHAT is
> > invalid about the cell reference. I've tried every concievable combination
> > and hope you can once again save the day for me?
> >
> >
> > "Gary''s Student" wrote:
> >
> > > My suggestion will work if the Item # appears only once in column E. That
> > > specific row is found by VLOOKUP and the associated data is displayed.
> > >
> > > If the item # appears several times in column E, then a different technique
> > > could be used:
> > >
> > > AutoFilter
> > >
> > > Using AutoFilter, ALL rows matching the item # in column E will be displayed.
> > > --
> > > Gary''s Student - gsnu200771
> > >
> > >
> > > "goalonggirl" wrote:
> > >
> > > > Gary's Student - Thanks for that info. one thing I forgot to mention and not
> > > > sure if it is covered by your wonderful solution - perhaps you can tell me -
> > > > my product info won't always be in the same row. For example if I am
> > > > entering orders from our buying group into the order form - one person will
> > > > order a product and another person might order the same product, but it will
> > > > be 30 lines down. Will this option allow for the query to search vertically
> > > > in the column of the table for the matching #, and match the data in the
> > > > other 3 columns similarly? Thanks much and God Bless - its so nice to get
> > > > information so fast!
> > > >
> > > > "Gary''s Student" wrote:
> > > >
> > > > > Use Excel.
> > > > >
> > > > > Build a table using columns E thru H. So column E would contain the item #
> > > > > and F and G and H the other three pieces of data.
> > > > >
> > > > > To use the table, enter the desired item # in cell A1 and the following
> > > > > formulas in B1 thru D1:
> > > > >
> > > > > =VLOOKUP(A1,E1:H100,2)
> > > > > =VLOOKUP(A1,E1:H100,3)
> > > > > =VLOOKUP(A1,E1:H100,4)
> > > > >
> > > > > These will find and display the data from the correct row
> > > > >
> > > > > Adjust the 100 to suit your needs
> > > > > --
> > > > > Gary''s Student - gsnu200771
> > > > >
> > > > >
> > > > > "goalonggirl" wrote:
> > > > >
> > > > > > Not sure if I have the correct group here, or even if I am using the proper
> > > > > > language to explain what I want to do.
> > > > > >
> > > > > > I need to use Excel (or Access or Works) to create a linkable product order
> > > > > > form. This needs to be able to go to the database I create, search for the
> > > > > > item # I input and then return to the next 3 columns in the row I am in the
> > > > > > values that go along with that item # in the database. (ie: 170113 would be
> > > > > > the item # and the next 3 columns would say 12 oz. Cranberry Juice
> > > > > > Concentrate, OG 3.75
|
Similar Threads
