How to get a value from 1 table to a 2nd.

P

Paul

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
 
C

Claus Busch

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 ??
Click to expand...

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
 
P

Paul

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
 
C

Claus Busch

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.
Click to expand...

please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
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
 
P

Paul

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
 
C

Claus Busch

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.
Click to expand...

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


Regards
Claus Busch
 
P

Paul

Hi Claus,
I checked the VBA sheet and is possible a change:
- when I write the code in (B10:may be B100) to have a C and D cells
automatically updated as discussed before like

- Code 1 in B10; C10=Sample1; D10=E25 or F10 if E25>F10.
and so on may be until row 100, or row77, or row 125 depending from the
number of product/quantity to be despatched.


This will be fantastic as the details rows might be a lot as in some
case the Quant is very high.

and then I can add a formula in range D2:D5 to updated the total
despatch and consequently the Difference E2=C2-C2.

If I not asking to much and I don't "abuse" of your help/patience.
Regards
Paul
 
P

Paul

This is simply perfect!.
Thanks for all your support. I learned something more either with Vba
and Formulas (Min + Index)
Have a great day.

Kind Regards
Paul
 
P

Paul

Hallo Claus,
it is me again.
I am trying to understand and learn I the code works, but I have to
admit that I really far away...

I would like to change the start of table 1(row 2) and table 2 (row 20),
changing the code as follows:

If Intersect(Target, Range("B21:B500")) Is _
Nothing Or Target.Count > 1 Then Exit Sub

Dim LRow1 As Long
Dim rngC1 As Range
Dim rngC2 As Range
'First row of table1
Const Start1 = 2
'First row of table2
Const Start2 = 20

but I get error tun-time 91 when I input code 4:
'Product to column c
Target.Offset(0, 1) = Range(Cells(1, 1), Cells(LRow1, 1)) _
.Find(Target, LookIn:=xlValues).Offset(0, 1)



Is there any other part of the code that needs to be changed.
If want to change the column's start where is necessary to change the code?
Thanks again
Paul
 
C

Claus Busch

Hi Paul,

Am Mon, 08 Apr 2013 20:17:55 +0200 schrieb Paul:
but I get error tun-time 91 when I input code 4:
'Product to column c
Target.Offset(0, 1) = Range(Cells(1, 1), Cells(LRow1, 1)) _
.Find(Target, LookIn:=xlValues).Offset(0, 1)

Is there any other part of the code that needs to be changed.
If want to change the column's start where is necessary to change the code?
Click to expand...

please llok again for your workbook. I changed the rows to 2 and 20. But
there is nothing else to change in the code.
The first row I meant is the row with the header.


Regards
Claus Busch
 
P

Paul

Hi Claus,
sorry, but if I input 4 I get an error.
Code 1, 2, 3 are fine.
It is my version of Excel that is not working anymore?

And in the case I want to move column of table 1 and then table 2 where
is the part of the code to change please?


Thanks and Regards
Paul
 
C

Claus Busch

Hi Paul,

Am Mon, 08 Apr 2013 21:04:15 +0200 schrieb Paul:
Code 1, 2, 3 are fine.
Click to expand...

sorry, that has nothing to do with the code but with LRow1. That
variable must be changed. Please have a look again.
And in the case I want to move column of table 1 and then table 2 where
is the part of the code to change please?
Click to expand...

E.g. the references are Cells(1,1)
The first value in the bracket is the row number, the second one is the
column number.
Same with offset
Offset(1,1) means one row down, one column to right.
Offset(-1,-1) means one row up, one column to left


Regards
Claus Busch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VLOOKUP to find a value that falls between a range? 3
Changing values in a table 2
A list and chart from two pivot tables 0
Get Max and Corresponding Text 3
I need to use a countif but looking a cell value 5
writing a value to a particular cell from function 3
Reverse table array lookup? 8
Help - return date-time from a given # of days 1

Top