Hallo I am back to ask for more help to You.


First table:
A,B,C are input data
D = summ of value from 2nd table
E= C-D
F= input data

A B C D E F
Code Product Quant Despatched Differ. Max/deliver
1 Sample1 300 150 150 50
2 Sample2 100 50 50 50
3 Sample3 47 7 40 50
4 Sample4 193 93 100 50


a detailed table with

A B C D
Date Code Product Quant
10 25.03 1 sample1 10
20 25.03 2 sample2 50
30 26.03 1 sample1 50
40 27.03 3 sample3 ??
50 27.03 1 sample1 ??

With Vlookup I am populate column C.

Form example: how may tell Excel to write 40 in cell D40 that is the
value of the first table =E3.
Than for cell D50 how may tell Excel to write 50 as E1 is 150, but max
deliver per day is 50?
I hope I explain myself.

Thanks and Regards
Paul

Hi Paul,

Am Sat, 06 Apr 2013 11:35:27 +0200 schrieb Paul:

First table:
A,B,C are input data
D = summ of value from 2nd table
E= C-D
F= input data

A B C D E F
Code Product Quant Despatched Differ. Max/deliver
1 Sample1 300 150 150 50
2 Sample2 100 50 50 50
3 Sample3 47 7 40 50
4 Sample4 193 93 100 50

a detailed table with

A B C D
Date Code Product Quant
10 25.03 1 sample1 10
20 25.03 2 sample2 50
30 26.03 1 sample1 50
40 27.03 3 sample3 ??
50 27.03 1 sample1 ??

in D10 try:
=MIN(VLOOKUP(B10,$A$2:$F$5,5,0),VLOOKUP(B10,$A$2:$ F$5,6,0))
and in C10 for the code:
=INDEX($A$1:$A$5,MATCH(C10,$B$1:$B$5,0))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks for the answer.
However I have 0 in C10.
Should I confirm the formula with Crtl+Shift+Enter?
Is there a chance of having this solution with Vba? Just in case I need
to edit the value in D10.

Thanks again
Paul

Hi Paul,

Am Sat, 06 Apr 2013 13:40:01 +0200 schrieb Paul:

However I have 0 in C10.
Should I confirm the formula with Crtl+Shift+Enter?
Is there a chance of having this solution with Vba? Just in case I need
to edit the value in D10.

please look he
https://skydrive.live.com/#cid=9378A...121822A3%21191
for the workbook "Paul".

I have to leave the PC for work. I'm coming home at evening to look for
a VBA solution.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus,
I was wrong and I put the formula in C10 and not in B10.
Also there is way to update range D2:D5 once that i fill row 15, 16, 17
etc in the way to have the difference to 0. (C-D = 9).
This is should be a control that I despatched everthing.

Thanks for your support
Regards
Paul

Hi Paul,

Am Sat, 06 Apr 2013 19:14:54 +0200 schrieb Paul:

I was wrong and I put the formula in C10 and not in B10.
Also there is way to update range D2:D5 once that i fill row 15, 16, 17
etc in the way to have the difference to 0. (C-D = 9).
This is should be a control that I despatched everthing.

please have another look for the workbook.
There are two suggestions in it.


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2