I'm having some trouble creating a Data Validation List formula that works.
I have the following Price list table: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 64"]Item[/TD]
[TD="width: 64"]LIST[/TD]
[TD="width: 64"]P1[/TD]
[TD="width: 64"]P2[/TD]
[TD="width: 64"]P3[/TD]
Item1
[/td]$5.00
[/td]$4.00
[/td]$7.00
[/td]$2.00
[/td]Item2
[/td]$6.00
[/td]$5.00
[/td]$3.00
[/td]$5.00
[/td]Item3
[/td]$4.00
[/td]$3.00
[/td]$5.00
[/td]$7.00
[/td]Item4
[/td]$5.00
[/td]$5.00
[/td]$2.00
[/td]$3.00
[/td]
[/TABLE]
On my Main sheet I have a table as follows: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]
[TD="width: 64"]Item[/TD]
[TD="width: 64"]LIST[/TD]
[TD="width: 98"]Price[/TD]
Item1
[/td]$5.00
[/td]DROPDOWN
[/td]Item3
[/td]$4.00
[/td]DROPDOWN
[/td]
[/TABLE]
What I'm trying to do is on the main sheet, based on the selection on the item column, I need the 3 prices (P1, P2, P3) to be in a dropdown in the "Price" column.
to get the list column i am using.
I thought maybe by using INDIRECT I could do that 3 times separated by comma's in the DV formula but that doesn't work. I would also have to go to each cell and increment the cell reference (B14). I would like to use OFFSET in the datavalidation formula but I cannot get anything to work. Is this possible?