Hmmm. Formatting leaves a little to be desired. Let's see:
1st sheet:
Database looks like this:
Sheet 1:
ID Name Price ID
1 Shirt 6 1
2 Pants 5 2
3 Hat 3 3
4 Gloves 2 4
Sheet 2:
Code Description Min. Bid
3 ;lkajsdfk ;aljsdf 2
3 ;lkjdf 2
4 ;akldjf ;laksf ;alskdfj 1
2 oisel 3
1 ;lakjsdf 4
1 woe 4
4 ldklw 1
Sheet 2 would look like:
Code Description Min. Bid Item Name
3 ;lkajsdfk ;aljsdf 2 Hat
3 ;lkjdf 2 Hat
4 ;akldjf ;laksf ;alskdfj 1 Gloves
2 oisel 3 Pants
1 ;lakjsdf 4 Shirt
1 woe 4 Shirt
4 ldklw 1 Gloves
Final sheet:
Code Description Min. Bid Item Name
3 ;lkajsdfk ;aljsdf 2 Hat
3 ;lkjdf 2 Hat
4 ;akldjf ;laksf ;alskdfj 1 Gloves
2 oisel 3 Pants
1 ;lakjsdf 4 Shirt
1 woe 4 Shirt
4 ldklw 1 Gloves
3 Hat
4 Gloves
4 Gloves
4 Gloves
1 Shirt
#N/A
#N/A
#N/A
HTH clarify. Again, I can post this if it helps.
Carl
On 2/10/14 10:07 PM, Carl Paulsen wrote:
OK, the item that you want to look up to return the price or name
needs to be in both spreadsheets (otherwise you won't find a match!).
IF (big IF here) the ID# and item code are the same for each item,
then this could be done as follows:
Database looks like this:
Sheet 1:
ID Name Price ID
1 Shirt 6 1
2 Pants 5 2
3 Hat 3 3
4 Gloves 2 4
Sheet 2:
Code Description Min. Bid
3 ;lkajsdfk ;aljsdf 2
3 ;lkjdf 2
4 ;akldjf ;laksf ;alskdfj 1
2 oisel 3
1 ;lakjsdf 4
1 woe 4
4 ldklw 1
(Gibberish was intentional. I didn't want to bother with actual
descriptions.) In this case, take the code you're given (3 in the
first line, sheet 2) and look up the value in sheet 1 (in this case it
would be "Hat"). To do this:
Sheet 2, add the following column in D: =VLOOKUP(A2,Sheet1.$A$1:$D$5,2,0)
copy and paste this formula as far down the sheet as you need to.
Note the dollar symbol is needed to create a static reference to the
array that you are looking at for the lookup. Otherwise it starts at
the row you're looking at and goes down from there, meaning eventually
you'll almost certainly NOT find a match. The lookup value (A2 in
the first cell of D2) will change for each row the formula is pasted
into.
Final sheet 2 would look like this:
Code Description Min. Bid Item Name
3 ;lkajsdfk ;aljsdf 2 Hat
3 ;lkjdf 2 Hat
4 ;akldjf ;laksf ;alskdfj 1 Gloves
2 oisel 3 Pants
1 ;lakjsdf 4 Shirt
1 woe 4 Shirt
4 ldklw 1 Gloves
In Column D, you would see the item name returned, but the actual
contents of that column would be the formula above, with the first
part after the vlookup (A2) changing with each row from A2 to A3, A4,
A5, A6, A7 and A8. What this does is looks up whatever is in A1 and
returns the name based on what it matches for ID in sheet 1.
Read carefully and I think you'll eventually get it. It can be
confusing, but it makes sense if you're careful to follow the logic.
Now, after the 7 lines of sample data above, I copied and pasted the
vlookup formula into the rest of Column D (as far as you think you
need to go). That's the setup. After that, when a number (from 1-4
for the item ID in sheet 1) is typed into column A, an item name is
returned in Column D. I typed a bunch of numbers into Column A to
illustrate below. See:
Code Description Min. Bid Item Name
3 ;lkajsdfk ;aljsdf 2 Hat
3 ;lkjdf 2 Hat
4 ;akldjf ;laksf ;alskdfj 1 Gloves
2 oisel 3 Pants
1 ;lakjsdf 4 Shirt
1 woe 4 Shirt
4 ldklw 1 Gloves
3
Hat
4
Gloves
4
--
Carl Paulsen
Dover, NH 03820
--
To unsubscribe e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
