One to get you thinking.

Ross wrote:
> Hello,
>
> I have a table of data that has dates, production lines and production
> volumes (table one):
>
> Production Line Date Volume
> A02 13/3/06 189
> C32 13/3/06 325
> T12 14/3/06 21
>
>
> The table contains data retrieved from the beginning of the year so
> there is a substantial amount, I have on a separate sheet a list of
> further data (table two):
>
> Production Line Date Tank
> AD02 13/3/06 A
> CD32 13/3/06 C
> TD12 14/3/06 B
>
>
> I want to use a formula that references two cells ( Production line
> and date in the first table) and returns from the second table the
> tank that the particular line was flowing into on that date.
> You will also notice an additional problem between the two table is
> that table one Production Line reads "A02" and table two's
> Production Line reads "AD02" so obviously this formula will have to
> ignore the additional D.
>
> Someone has suggested an array formula but seeing as I am using this
> formula possible several thousand times on one sheet it is slowing
> the whole workbook down far too much.
>
> Thanks for any help in advance.
>
> P


Hi Ross,

the only way without using VBA, AFAIK, is an array formula like this:

=INDEX(Sheet2!C2:C4,,MATCH(A2,LEFT(Sheet2!A2:A4,1)&RIGHT(Sheet2!A2:A4,2),0)*MATCH(B2,Sheet2!B2:B4,0))

Type (or copy and paste) the formula in cell D2 on your first sheet,
assuming that the sheet with tank information is "Sheet2", press
Ctrl+Shift+Enter, then copy down the formula.

You could also try with VBA, but in this case I could not help you, because
VBA it's not my strong point...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Franz Verga wrote:
> Ross wrote:
>> Hello,
>>
>> I have a table of data that has dates, production lines and
>> production volumes (table one):
>>
>> Production Line Date Volume
>> A02 13/3/06 189
>> C32 13/3/06 325
>> T12 14/3/06 21
>>
>>
>> The table contains data retrieved from the beginning of the year so
>> there is a substantial amount, I have on a separate sheet a list of
>> further data (table two):
>>
>> Production Line Date Tank
>> AD02 13/3/06 A
>> CD32 13/3/06 C
>> TD12 14/3/06 B
>>
>>
>> I want to use a formula that references two cells ( Production line
>> and date in the first table) and returns from the second table the
>> tank that the particular line was flowing into on that date.
>> You will also notice an additional problem between the two table is
>> that table one Production Line reads "A02" and table two's
>> Production Line reads "AD02" so obviously this formula will have to
>> ignore the additional D.
>>
>> Someone has suggested an array formula but seeing as I am using this
>> formula possible several thousand times on one sheet it is slowing
>> the whole workbook down far too much.
>>
>> Thanks for any help in advance.
>>
>> P
>
>
> Hi Ross,
>
> the only way without using VBA, AFAIK, is an array formula like this:
>
> =INDEX(Sheet2!C2:C4,,MATCH(A2,LEFT(Sheet2!A2:A4,1)&RIGHT(Sheet2!A2:A4,2),0)*MATCH(B2,Sheet2!B2:B4,0))
>
> Type (or copy and paste) the formula in cell D2 on your first sheet,
> assuming that the sheet with tank information is "Sheet2", press
> Ctrl+Shift+Enter, then copy down the formula.

Maybe this should be better:

=INDEX(Foglio2!$C$2:$C$4,MATCH(1,(LEFT(Foglio2!$A$2:$A$4,1)&RIGHT(Foglio2!$A$2:$A$4,2)=A4)*(Foglio2!$B$2:$B$4=B4),0))

always array entered...

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy