Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching & retrieving data across sheets
Hi I have a small problem, one that is probably fairly simple to solve but i hardly ever use excel in depth! I am trying to create a little workbok to facilitate an ordering procedure, it has a stock database on one sheet, and i want to be able to type an id no. into the first sheet, and that to retrieve all data for that id in the second sheet. IE so if i put in a value of 25, the product with the id of 25 will appear in the cells next to it, showing stock description, price etc. This is then repeated down to create an order sheet. I have worked out how this works using an index and match formula, but my problem is that i need a user friendly way of inputting the ID in the first place. Ideally there would be an add button which brought up a parameter input field, which you type the number into, and then that creates a new row, puts in the formula and adjusts the code of it to suit the parameter, but not only have i forgotten how to do this, the final sheet needs to go on a pda with pocket excel, so i cant use any VB etc as it wont work with pocket excel. Anybody have any suggestions? Thanks in advance for any help! Nick -- nd2006 ------------------------------------------------------------------------ nd2006's Profile: http://www.excelforum.com/member.php...o&userid=37806 View this thread: http://www.excelforum.com/showthread...hreadid=573685 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching & retrieving data across sheets
Assuming your order form is something like the example below with data
starting in A2: Product ID Product Description Product Price ....... 25 In Product Description cell (B2): =IF(A2<"",IF(ISERROR(VLOOKUP(A2,Stock!A2:D100,2,F ALSE)),"",VLOOKUP(A2,Stock!A2:D100,2,FALSE))) In Product Price cell (C2): =IF(A2<"",IF(ISERROR(VLOOKUP(A2,Stock!A2:D100,3,F ALSE)),"",VLOOKUP(A2,Stock!A2:D100,3,FALSE))) Copy these down as far as required Your "Stock" DB would like Col A .... Product ID B ......Product Description C .... Product Price D ..... You can just enter your IDs in colum A and it would automatically fill in the other data. If number of products is small, you could use Data Validation (in column A) with your Product IDs as the input list. HTH "nd2006" wrote: Hi I have a small problem, one that is probably fairly simple to solve but i hardly ever use excel in depth! I am trying to create a little workbok to facilitate an ordering procedure, it has a stock database on one sheet, and i want to be able to type an id no. into the first sheet, and that to retrieve all data for that id in the second sheet. IE so if i put in a value of 25, the product with the id of 25 will appear in the cells next to it, showing stock description, price etc. This is then repeated down to create an order sheet. I have worked out how this works using an index and match formula, but my problem is that i need a user friendly way of inputting the ID in the first place. Ideally there would be an add button which brought up a parameter input field, which you type the number into, and then that creates a new row, puts in the formula and adjusts the code of it to suit the parameter, but not only have i forgotten how to do this, the final sheet needs to go on a pda with pocket excel, so i cant use any VB etc as it wont work with pocket excel. Anybody have any suggestions? Thanks in advance for any help! Nick -- nd2006 ------------------------------------------------------------------------ nd2006's Profile: http://www.excelforum.com/member.php...o&userid=37806 View this thread: http://www.excelforum.com/showthread...hreadid=573685 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching & retrieving data across sheets
Typo alert.
I think Toppers wants an extra "" at the far right: =IF(A2<"",IF(ISERROR(VLOOKUP(A2,Stock!A2:D100,2,F ALSE)),"", VLOOKUP(A2,Stock!A2:D100,2,FALSE)),"") So FALSE won't be returned when a2 = "". Toppers wrote: Assuming your order form is something like the example below with data starting in A2: Product ID Product Description Product Price ....... 25 In Product Description cell (B2): =IF(A2<"",IF(ISERROR(VLOOKUP(A2,Stock!A2:D100,2,F ALSE)),"",VLOOKUP(A2,Stock!A2:D100,2,FALSE))) In Product Price cell (C2): =IF(A2<"",IF(ISERROR(VLOOKUP(A2,Stock!A2:D100,3,F ALSE)),"",VLOOKUP(A2,Stock!A2:D100,3,FALSE))) Copy these down as far as required Your "Stock" DB would like Col A .... Product ID B ......Product Description C .... Product Price D ..... You can just enter your IDs in colum A and it would automatically fill in the other data. If number of products is small, you could use Data Validation (in column A) with your Product IDs as the input list. HTH "nd2006" wrote: Hi I have a small problem, one that is probably fairly simple to solve but i hardly ever use excel in depth! I am trying to create a little workbok to facilitate an ordering procedure, it has a stock database on one sheet, and i want to be able to type an id no. into the first sheet, and that to retrieve all data for that id in the second sheet. IE so if i put in a value of 25, the product with the id of 25 will appear in the cells next to it, showing stock description, price etc. This is then repeated down to create an order sheet. I have worked out how this works using an index and match formula, but my problem is that i need a user friendly way of inputting the ID in the first place. Ideally there would be an add button which brought up a parameter input field, which you type the number into, and then that creates a new row, puts in the formula and adjusts the code of it to suit the parameter, but not only have i forgotten how to do this, the final sheet needs to go on a pda with pocket excel, so i cant use any VB etc as it wont work with pocket excel. Anybody have any suggestions? Thanks in advance for any help! Nick -- nd2006 ------------------------------------------------------------------------ nd2006's Profile: http://www.excelforum.com/member.php...o&userid=37806 View this thread: http://www.excelforum.com/showthread...hreadid=573685 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Matching & retrieving data across sheets
Thanks Dave ... well spotted!
"Dave Peterson" wrote: Typo alert. I think Toppers wants an extra "" at the far right: =IF(A2<"",IF(ISERROR(VLOOKUP(A2,Stock!A2:D100,2,F ALSE)),"", VLOOKUP(A2,Stock!A2:D100,2,FALSE)),"") So FALSE won't be returned when a2 = "". Toppers wrote: Assuming your order form is something like the example below with data starting in A2: Product ID Product Description Product Price ....... 25 In Product Description cell (B2): =IF(A2<"",IF(ISERROR(VLOOKUP(A2,Stock!A2:D100,2,F ALSE)),"",VLOOKUP(A2,Stock!A2:D100,2,FALSE))) In Product Price cell (C2): =IF(A2<"",IF(ISERROR(VLOOKUP(A2,Stock!A2:D100,3,F ALSE)),"",VLOOKUP(A2,Stock!A2:D100,3,FALSE))) Copy these down as far as required Your "Stock" DB would like Col A .... Product ID B ......Product Description C .... Product Price D ..... You can just enter your IDs in colum A and it would automatically fill in the other data. If number of products is small, you could use Data Validation (in column A) with your Product IDs as the input list. HTH "nd2006" wrote: Hi I have a small problem, one that is probably fairly simple to solve but i hardly ever use excel in depth! I am trying to create a little workbok to facilitate an ordering procedure, it has a stock database on one sheet, and i want to be able to type an id no. into the first sheet, and that to retrieve all data for that id in the second sheet. IE so if i put in a value of 25, the product with the id of 25 will appear in the cells next to it, showing stock description, price etc. This is then repeated down to create an order sheet. I have worked out how this works using an index and match formula, but my problem is that i need a user friendly way of inputting the ID in the first place. Ideally there would be an add button which brought up a parameter input field, which you type the number into, and then that creates a new row, puts in the formula and adjusts the code of it to suit the parameter, but not only have i forgotten how to do this, the final sheet needs to go on a pda with pocket excel, so i cant use any VB etc as it wont work with pocket excel. Anybody have any suggestions? Thanks in advance for any help! Nick -- nd2006 ------------------------------------------------------------------------ nd2006's Profile: http://www.excelforum.com/member.php...o&userid=37806 View this thread: http://www.excelforum.com/showthread...hreadid=573685 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
collecting data from many sheets to one sheet | Excel Discussion (Misc queries) | |||
Keeping data updated between 2 sheets... novice needs help. | Excel Discussion (Misc queries) | |||
Charts not recognizing source data if original linked data is changed. | Charts and Charting in Excel | |||
Append the data given in diff sheets of an Excel File to one sheet | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |