Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
indirect funtion
A couple of days ago I asked this question and the reply I received I did not
under stand. The response was create new lists for each customer and use the indirect function and this would solve my dilema. I hate to be so thick headed on this, but I do not understand this function. Could an example be shown to help me? Here is the question again: D7 has a dropdown list with customer names (10 -12 customers). F16 thru F38 each have a dropdown list with a vlookup of a products list. Infomation in columns H thru L are dependent on the product that is shown in cells F16 thru F38 for unit size, price and extended price. The dilema is that all of the customers do not have the same pricing. How can I make the cells in F-16 thru F-38 reflect the product and all the associated pricing info for which ever customer I select from the dropdown list? Can it be done? Pete |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
indirect funtion
How about this...
Create a worksheet with two columns in it. Column A has the customer name (use that column for the data|validation list) Column B will have the column that should be used for that customer And cell $C$1 will hold the column that you chose. Then have another worksheet that has your product table. Column A has the product id/part number column B has the description column C has the unit cost column D:X has the unit price for each group of customers (you may have some customers who share the same price list--or not) So your first table could look like: Cust1 2 cust2 3 cust3 2 (shares same price list with cust#1 cust4 4 cust5 2 (another shared list) cust6 7 ....etc (For this purpose, I'm calling that sheet: CustTable) Then your second table could look like: part1 desc1 12.50 37.22 44.22 37.22 88.24 part2 desc2 2.50 7.44 8.00 5.22 18.24 ....etc (For this purpose, I'm calling that sheet: PartTable) These sheets would be hidden (to keep them safe from prying eyes???). Then on your input sheet (called Input), you'd have a cell (say A2) that used data|validation to return the customer name. Debra Dalgleish has some notes how to use a named range for this data|validation cell: http://contextures.com/xlDataVal01.html#Name Then in $c$1 of that custTable sheet: =if(input!a2="","",vlookup(input!a2,a:b,2,false)) This will return the column that should be used in the vlookup() to return the unit price for that part number/customer combination. And you could use this kind of formula to return the stuff you need: With the part number in F16, you could return the description: =vlookup(f16,parttable!a:x,2,false) The unit cost wouldn't be shared with the customer, but it'll be nice to have it on that sheet! And to return the unit price for the chosen customer: =if(custtable!$c$1="",9999999,vlookup(f16,parttabl e!a:x,custtable!$c$1,false)) I like 9999999 since it'll flag any missing customer--everyone will know that something is missing. And it won't mess up any subsequent formulas--like extended price. That would just be =QtyCell * UnitPriceCell on the input sheet. Pete Elbert wrote: A couple of days ago I asked this question and the reply I received I did not under stand. The response was create new lists for each customer and use the indirect function and this would solve my dilema. I hate to be so thick headed on this, but I do not understand this function. Could an example be shown to help me? Here is the question again: D7 has a dropdown list with customer names (10 -12 customers). F16 thru F38 each have a dropdown list with a vlookup of a products list. Infomation in columns H thru L are dependent on the product that is shown in cells F16 thru F38 for unit size, price and extended price. The dilema is that all of the customers do not have the same pricing. How can I make the cells in F-16 thru F-38 reflect the product and all the associated pricing info for which ever customer I select from the dropdown list? Can it be done? Pete -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a formulae down a column that includes an INDIRECT | Excel Discussion (Misc queries) | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
Trying to use the INDIRECT funtion with a relative Row reference | Excel Discussion (Misc queries) | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
is there a NON-volatile version of INDIRECT ?? | Excel Discussion (Misc queries) |