Excel help - data linking and validation

[NikPreviousAcct]

I've got a list of part numbers in one column, a description of those items in a second column, and two columns of different pricing following those. These are all in one worksheet.

In a second worksheet, I've figured out how to create a drop-down list using data validation. I've selected the first column of part numbers and given that range a name. Then, using data validation, I've created a drop-down list on the second worksheet that allows the user to pick one of the part numbers.

Now my issue is that I'd like to have the description of that part and the two pricing columns auto-populate.

I've constructed IF/THEN statements, but I'll be working with worksheets that contain thousands of part numbers and I need a better way of auto-populating the description and two (or three or five or whatever) pricing fields.

Here's an example of what I've done

Does anyone have a better way of auto-populating those fields, other than if/then statements? Thousands of if/then functions are simply impossible to build.

Ideas?

 

[Traire]

use:

=LOOKUP(x,y,z)

"X" is the cell with your drop down list.

"Y" is the same range of cells that your drop down list points to.

"Z" is the range of cells you want the result to pull from. These should be a parralel set of cells to "Y" that are the same size and shape.

So yours would look something like:
=LOOKUP(A1,Sheet2!A1:A29,Sheet2!B1:B29)

 

[Concillian]

Use the help function to learn about all the "lookup" functions.

HLOOKUP
VLOOKUP
LOOKUP

choose the most appropriate to your application, likely VLOOKUP.

I helped a co-wroker create a very similar type of sheet where the user selects an item from a dropdown.
That dropdown outputs to a cell hidden underneath the dropdown
A lookup function in the next cell references that output cell fo the dropdown to find the price in the table on a hidden or protected worksheet

 

[NikPreviousAcct]

Originally posted by: Traire
use:

=LOOKUP(x,y,z)

"X" is the cell with your drop down list.

"Y" is the same range of cells that your drop down list points to.

"Z" is the range of cells you want the result to pull from. These should be a parralel set of cells to "Y" that are the same size and shape.

So yours would look something like:
=LOOKUP(A1,Sheet2!A1:A29,Sheet2!B1:B29)

Uh, okay. Where does that go? Open the spreadsheet I linked to and tell me where it's going.

 

[NikPreviousAcct]

Originally posted by: Concillian
Use the help function to learn about all the "lookup" functions.

HLOOKUP
VLOOKUP
LOOKUP

choose the most appropriate to your application, likely VLOOKUP.

I helped a co-wroker create a very similar type of sheet where the user selects an item from a dropdown.
That dropdown outputs to a cell hidden underneath the dropdown
A lookup function in the next cell references that output cell fo the dropdown to find the price in the table on a hidden or protected worksheet

I would prefer to not use hidden cells/rows/columns/worksheets.

 

[NikPreviousAcct]

Originally posted by: Traire
use:

=LOOKUP(x,y,z)

"X" is the cell with your drop down list.

"Y" is the same range of cells that your drop down list points to.

"Z" is the range of cells you want the result to pull from. These should be a parralel set of cells to "Y" that are the same size and shape.

So yours would look something like:
=LOOKUP(A1,Sheet2!A1:A29,Sheet2!B1:B29)

Nevermind, I got it. Thanks a WHOLE CRAPLOAD man. :thumbsup:

If you're ever in Oregon, I owe you a :beer: or two

 

[NikPreviousAcct]

Originally posted by: Traire
use:

=LOOKUP(x,y,z)

"X" is the cell with your drop down list.

"Y" is the same range of cells that your drop down list points to.

"Z" is the range of cells you want the result to pull from. These should be a parralel set of cells to "Y" that are the same size and shape.

So yours would look something like:
=LOOKUP(A1,Sheet2!A1:A29,Sheet2!B1:B29)

I've used your coding in the excel spreadsheet, but it seems to work sometimes and not others.

http://www.predigio.net/Book2.xls

Check out the config sheet and change the drop-down arrow. Sometimes it pulls the correct information and sometimes it doesn't. What am I missing?

 

[Traire]

Ok using the example sheet you posted:

1. on the "component" page, you will have to keep the "part number" column sorted alphabetically. Everytime you add something you will need to sort the 4 columns.

2. On the "config" page, in cell B2, right next to your drop down, enter in this:
=LOOKUP(A2,component!A2:A1000,component!B2:B1000)

3. On the "config" page, in cell C2, right next to your drop down, enter in this:
=LOOKUP(A2,component!A2:A1000,component!C2:C1000)

3. On the "config" page, in cell D2, right next to your drop down, enter in this:
=LOOKUP(A2,component!A2:A1000,component!D21000)

 

[NikPreviousAcct]

What was I doing incorrectly? The only difference in those changes is that I used x2:x7 instead of x2:x1000

 

[Traire]

you posted your last question at the same time as I writing that.

Your new example sheet is good, You jsut need to sort your "Component" page alphabetically by the first column.

 

[NikPreviousAcct]

Originally posted by: Traire
you posted your last question at the same time as I writing that.

Your new example sheet is good, You jsut need to sort your "Component" page alphabetically by the first column.

That prevents the LOOKUP function from functioning?

 

[Traire]

yeah Excel likes things to be in alphabetical/numerical order. Most of the advanced functions dont like random lists.

 

[Traire]

Do you know how to make macros? You could record the sorting action to a macro and then make a button that will sort the page for you.

 

[NikPreviousAcct]

Nope, I know nothing of macros. The only thing I use Excel to do, really, is to view pricelists from my distributor. I'm in the process of making a system configurator and wanted to try Excel before being forced to resort to PHP+MySQL. If I can just get this damned thing to make me a working/functioning HTML document, I'm set; however, I'm not finding the option to do so.

 

[PowerEngineer]

Originally posted by: Traire
yeah Excel likes things to be in alphabetical/numerical order. Most of the advanced functions dont like random lists.

Which is why I like to use the combination of MATCH (to find the right row) and then INDEX to pick the right item from the right row. No need to ordering as long as there are no duplicate entries in the "match" column.

Actually, you should be able to use the index number written into a cell by the drop-down box definition in an INDEX statement without resorting to MATCH (if I understand what you've described correctly).

 

[Concillian]

Originally posted by: Nik

I would prefer to not use hidden cells/rows/columns/worksheets.

Hidden is one thing, protected is quite another.

IMO protecting information not meant for direct user consumption is a key part of a function like this. Unless you are the only one using the worksheet. Even then it doesn't hurt to protect your data from accidental change, especially when it may be difficult to determine when errors exist.

Consider when an error occurs because a user inadvertently changed something in the pricing table. Of course they probably won't come right out and tell you they may have changed something there on accident, even if they realized they did.

There was nothing magic about making the pricing table hidden, it just makes the user interface cleaner.

 

[NikPreviousAcct]

Anyone know if Excel can take this multi-worksheet and make a functioning webpage out of it?

 

[Traire]

Like convert it to html tags including the drop down list etc? Or just convert it into a spreadsheet that can be viewed online?

If you want it to be true html, then no.

To get something similar in html, you would need to use java/asp/php etc to get the same functionality. For one thing, html doesnt support linking accross "pages" like in your worksheet. Excel is not meant to create web content.

Do you just want a page with a drop down list that will look up prices?

You can use your spreadsheet as a "database" and have an .asp or .php page reference data from it. I can give you example code for it in .asp (visual basic) but I dont know php very well, so you would need someone else to help you with that.

 

[CycloWizard]

Originally posted by: Nik
Anyone know if Excel can take this multi-worksheet and make a functioning webpage out of it?

This would be very easy to do using a couple SQL commands and asp.

 

[NikPreviousAcct]

To the both of you, ASP won't work. I'm running a *nix server.

 

[Traire]

You'll have to write some php then, if you want to link the excel table dynamically.

An alternative would be to hardcode the data in a html form, maybe using a bit of javascript, but that wouldnt be very user-friendly for updating the info.

 

[NikPreviousAcct]

PHP on a *nix system can open an Excel file and pull data from different worksheets?! :Q Does it function the same way as using the naming scheme used in my little file?

 

[Traire]

Yeah, you can parse the excel file with php, although not the best solution. A better way would be to import the data into a mysql database with a php script, then access the data from mysql.

thats one of the nice things about asp is that it is a bit more flexible in the types of files it can pull data from.