Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating an invoice with a lookup list
I am trying to create an invoice template where I am able to populate the
items sold list and prices from another worksheet containing all the items I sell and their relevant prices. I thought that I would do this by using a drop down list for each cell in the list of items purchased. When I select an item from the drop down list of item descriptions I want the associated price to also be automatically added to the 'unit price' column. The first problem I have come across is creating the drop down list. I tried to use the Data|Validation method as described by the Excel help which says that you can reference other worksheets or workbooks as the 'source'. When I try this Excel complains that sources from other worksheets or workbooks CANNOT be used. First question is - am I choosing the best way to achieve my aim? Next - should I be able to reference another worksheet using the Data|Validation method of creating a drop down list? Finally - having successfully selected an item from my list of descriptions how can I get the associated price to populate the 'unit price' column? Sorry there are a number of things I need help with here, but I would appreciate any ideas/suggestions that may help me achieve the aim. |
#2
|
|||
|
|||
Creating an invoice with a lookup list
I have now found the answers to my own question by delving into the other
similar topics here. There are a couple of good sites that seem to have what I need: http://www.contextures.com/xlOrderForm01.html http://www.officearticles.com/tutori...soft_excel.htm So thanks to those who have given these answers in the past - hopefully I am on the right track now. Cheers, Wings "wings" wrote: I am trying to create an invoice template where I am able to populate the items sold list and prices from another worksheet containing all the items I sell and their relevant prices. I thought that I would do this by using a drop down list for each cell in the list of items purchased. When I select an item from the drop down list of item descriptions I want the associated price to also be automatically added to the 'unit price' column. The first problem I have come across is creating the drop down list. I tried to use the Data|Validation method as described by the Excel help which says that you can reference other worksheets or workbooks as the 'source'. When I try this Excel complains that sources from other worksheets or workbooks CANNOT be used. First question is - am I choosing the best way to achieve my aim? Next - should I be able to reference another worksheet using the Data|Validation method of creating a drop down list? Finally - having successfully selected an item from my list of descriptions how can I get the associated price to populate the 'unit price' column? Sorry there are a number of things I need help with here, but I would appreciate any ideas/suggestions that may help me achieve the aim. |
#3
|
|||
|
|||
Creating an invoice with a lookup list
Take a look at Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html She shows how to use a named range to do what you want. So say your list of items/prices is in Sheet2!a1:B200. And your cell with data|validation is in A1 of sheet1. You could use this formula: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) wings wrote: I am trying to create an invoice template where I am able to populate the items sold list and prices from another worksheet containing all the items I sell and their relevant prices. I thought that I would do this by using a drop down list for each cell in the list of items purchased. When I select an item from the drop down list of item descriptions I want the associated price to also be automatically added to the 'unit price' column. The first problem I have come across is creating the drop down list. I tried to use the Data|Validation method as described by the Excel help which says that you can reference other worksheets or workbooks as the 'source'. When I try this Excel complains that sources from other worksheets or workbooks CANNOT be used. First question is - am I choosing the best way to achieve my aim? Next - should I be able to reference another worksheet using the Data|Validation method of creating a drop down list? Finally - having successfully selected an item from my list of descriptions how can I get the associated price to populate the 'unit price' column? Sorry there are a number of things I need help with here, but I would appreciate any ideas/suggestions that may help me achieve the aim. -- Dave Peterson |
#4
|
|||
|
|||
Creating an invoice with a lookup list
Dave,
Thanks for that - I think I am getting the hang of what I need to do now. One thing perplexes my though. When I first tried to use Data|Validation to call up a range of cells from another worksheet for my drop down list, I defined a name for the range of cells on the sheet and in 'source' for the drop down list entered '=sheet!rangename'. Excel did not like this and stated that is was not possible to enter a source for another worksheet or workbook (contrary to what the help pages say). In the end I got it to work by simple entering '=rangename' as the source entry despite the fact that the range was on another sheet. I still cannot see why prefixing this with 'sheet!' does not work. Can you enlighten me? David "Dave Peterson" wrote: Take a look at Debra Dalgleish's site: http://contextures.com/xlDataVal01.html She shows how to use a named range to do what you want. So say your list of items/prices is in Sheet2!a1:B200. And your cell with data|validation is in A1 of sheet1. You could use this formula: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) wings wrote: I am trying to create an invoice template where I am able to populate the items sold list and prices from another worksheet containing all the items I sell and their relevant prices. I thought that I would do this by using a drop down list for each cell in the list of items purchased. When I select an item from the drop down list of item descriptions I want the associated price to also be automatically added to the 'unit price' column. The first problem I have come across is creating the drop down list. I tried to use the Data|Validation method as described by the Excel help which says that you can reference other worksheets or workbooks as the 'source'. When I try this Excel complains that sources from other worksheets or workbooks CANNOT be used. First question is - am I choosing the best way to achieve my aim? Next - should I be able to reference another worksheet using the Data|Validation method of creating a drop down list? Finally - having successfully selected an item from my list of descriptions how can I get the associated price to populate the 'unit price' column? Sorry there are a number of things I need help with here, but I would appreciate any ideas/suggestions that may help me achieve the aim. -- Dave Peterson |
#5
|
|||
|
|||
Creating an invoice with a lookup list
You can create a range name that is local to a worksheet:
Insert|Name|Define Include the sheet name in the "names in workbook" box sheet1!myName refers to: =sheet1!A1:A10 (say) This is called a local or sheet level name. When you try to use this range name for a cell in a different worksheet, excel knows that it isn't part of that activesheet and yells. By making it a global or workbook level name, excel doesn't seem to care. You'd use Insert|Name|define and not put any sheet name in that "names in workbook" box. === Why excel cares where that range is, I have no idea--I guess the developers thought that it was important--but not important enough to outlaw that global name (on a different sheet). wings wrote: Dave, Thanks for that - I think I am getting the hang of what I need to do now. One thing perplexes my though. When I first tried to use Data|Validation to call up a range of cells from another worksheet for my drop down list, I defined a name for the range of cells on the sheet and in 'source' for the drop down list entered '=sheet!rangename'. Excel did not like this and stated that is was not possible to enter a source for another worksheet or workbook (contrary to what the help pages say). In the end I got it to work by simple entering '=rangename' as the source entry despite the fact that the range was on another sheet. I still cannot see why prefixing this with 'sheet!' does not work. Can you enlighten me? David "Dave Peterson" wrote: Take a look at Debra Dalgleish's site: http://contextures.com/xlDataVal01.html She shows how to use a named range to do what you want. So say your list of items/prices is in Sheet2!a1:B200. And your cell with data|validation is in A1 of sheet1. You could use this formula: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) wings wrote: I am trying to create an invoice template where I am able to populate the items sold list and prices from another worksheet containing all the items I sell and their relevant prices. I thought that I would do this by using a drop down list for each cell in the list of items purchased. When I select an item from the drop down list of item descriptions I want the associated price to also be automatically added to the 'unit price' column. The first problem I have come across is creating the drop down list. I tried to use the Data|Validation method as described by the Excel help which says that you can reference other worksheets or workbooks as the 'source'. When I try this Excel complains that sources from other worksheets or workbooks CANNOT be used. First question is - am I choosing the best way to achieve my aim? Next - should I be able to reference another worksheet using the Data|Validation method of creating a drop down list? Finally - having successfully selected an item from my list of descriptions how can I get the associated price to populate the 'unit price' column? Sorry there are a number of things I need help with here, but I would appreciate any ideas/suggestions that may help me achieve the aim. -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
Creating an invoice with a lookup list
Dave,
Thanks for that - clear as mud now. Seriously though it is really helpful to have people who know what they are talking about helping us newbies - thanks a lot! David "Dave Peterson" wrote: You can create a range name that is local to a worksheet: Insert|Name|Define Include the sheet name in the "names in workbook" box sheet1!myName refers to: =sheet1!A1:A10 (say) This is called a local or sheet level name. When you try to use this range name for a cell in a different worksheet, excel knows that it isn't part of that activesheet and yells. By making it a global or workbook level name, excel doesn't seem to care. You'd use Insert|Name|define and not put any sheet name in that "names in workbook" box. === Why excel cares where that range is, I have no idea--I guess the developers thought that it was important--but not important enough to outlaw that global name (on a different sheet). wings wrote: Dave, Thanks for that - I think I am getting the hang of what I need to do now. One thing perplexes my though. When I first tried to use Data|Validation to call up a range of cells from another worksheet for my drop down list, I defined a name for the range of cells on the sheet and in 'source' for the drop down list entered '=sheet!rangename'. Excel did not like this and stated that is was not possible to enter a source for another worksheet or workbook (contrary to what the help pages say). In the end I got it to work by simple entering '=rangename' as the source entry despite the fact that the range was on another sheet. I still cannot see why prefixing this with 'sheet!' does not work. Can you enlighten me? David "Dave Peterson" wrote: Take a look at Debra Dalgleish's site: http://contextures.com/xlDataVal01.html She shows how to use a named range to do what you want. So say your list of items/prices is in Sheet2!a1:B200. And your cell with data|validation is in A1 of sheet1. You could use this formula: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) wings wrote: I am trying to create an invoice template where I am able to populate the items sold list and prices from another worksheet containing all the items I sell and their relevant prices. I thought that I would do this by using a drop down list for each cell in the list of items purchased. When I select an item from the drop down list of item descriptions I want the associated price to also be automatically added to the 'unit price' column. The first problem I have come across is creating the drop down list. I tried to use the Data|Validation method as described by the Excel help which says that you can reference other worksheets or workbooks as the 'source'. When I try this Excel complains that sources from other worksheets or workbooks CANNOT be used. First question is - am I choosing the best way to achieve my aim? Next - should I be able to reference another worksheet using the Data|Validation method of creating a drop down list? Finally - having successfully selected an item from my list of descriptions how can I get the associated price to populate the 'unit price' column? Sorry there are a number of things I need help with here, but I would appreciate any ideas/suggestions that may help me achieve the aim. -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
Creating an invoice with a lookup list
Once you have a workbook with a few names, you may find it easier to see what's
going on with any/all of them if you use Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager: NameManager.Zip from http://www.oaltd.co.uk/mvp wings wrote: Dave, Thanks for that - clear as mud now. Seriously though it is really helpful to have people who know what they are talking about helping us newbies - thanks a lot! David "Dave Peterson" wrote: You can create a range name that is local to a worksheet: Insert|Name|Define Include the sheet name in the "names in workbook" box sheet1!myName refers to: =sheet1!A1:A10 (say) This is called a local or sheet level name. When you try to use this range name for a cell in a different worksheet, excel knows that it isn't part of that activesheet and yells. By making it a global or workbook level name, excel doesn't seem to care. You'd use Insert|Name|define and not put any sheet name in that "names in workbook" box. === Why excel cares where that range is, I have no idea--I guess the developers thought that it was important--but not important enough to outlaw that global name (on a different sheet). wings wrote: Dave, Thanks for that - I think I am getting the hang of what I need to do now. One thing perplexes my though. When I first tried to use Data|Validation to call up a range of cells from another worksheet for my drop down list, I defined a name for the range of cells on the sheet and in 'source' for the drop down list entered '=sheet!rangename'. Excel did not like this and stated that is was not possible to enter a source for another worksheet or workbook (contrary to what the help pages say). In the end I got it to work by simple entering '=rangename' as the source entry despite the fact that the range was on another sheet. I still cannot see why prefixing this with 'sheet!' does not work. Can you enlighten me? David "Dave Peterson" wrote: Take a look at Debra Dalgleish's site: http://contextures.com/xlDataVal01.html She shows how to use a named range to do what you want. So say your list of items/prices is in Sheet2!a1:B200. And your cell with data|validation is in A1 of sheet1. You could use this formula: =if(a1="","",vlookup(a1,sheet2!a:b,2,false)) wings wrote: I am trying to create an invoice template where I am able to populate the items sold list and prices from another worksheet containing all the items I sell and their relevant prices. I thought that I would do this by using a drop down list for each cell in the list of items purchased. When I select an item from the drop down list of item descriptions I want the associated price to also be automatically added to the 'unit price' column. The first problem I have come across is creating the drop down list. I tried to use the Data|Validation method as described by the Excel help which says that you can reference other worksheets or workbooks as the 'source'. When I try this Excel complains that sources from other worksheets or workbooks CANNOT be used. First question is - am I choosing the best way to achieve my aim? Next - should I be able to reference another worksheet using the Data|Validation method of creating a drop down list? Finally - having successfully selected an item from my list of descriptions how can I get the associated price to populate the 'unit price' column? Sorry there are a number of things I need help with here, but I would appreciate any ideas/suggestions that may help me achieve the aim. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating macro to lookup data | Excel Discussion (Misc queries) | |||
Master invoice number | Excel Worksheet Functions | |||
Creating drop down list for multiple cell ranges | Excel Discussion (Misc queries) | |||
Lookup List to Index | Excel Worksheet Functions | |||
Lookup closest number in list | Excel Discussion (Misc queries) |