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

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

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

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

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

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

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

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

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

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

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

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

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

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 E25F10.
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

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

Hi Paul,

Am Sat, 06 Apr 2013 20:51:12 +0200 schrieb 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

have another look.
If you enter the code in column B, columns C and columns D will be
filled automatically.


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

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

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

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

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

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

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?

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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

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

Hi Paul,

Am Mon, 08 Apr 2013 21:04:15 +0200 schrieb Paul:

Code 1, 2, 3 are fine.

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?

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

Hi Claus,
sorry for a late reply.
But I am trying to adjust the project without success.
Anyway your last sample is fine.
Mine, that is not working at all as yours is:
https://skydrive.live.com/redir.aspx...FD181B9505!115

Regards and a 1000 thanks
Paul

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

Hi Paul,

Am Mon, 08 Apr 2013 22:27:02 +0200 schrieb Paul:

Mine, that is not working at all as yours is:
https://skydrive.live.com/redir.aspx...FD181B9505!115

have a look for the workbook "Paul_2_"


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

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

Hi Claus
sorry for a late reply but was a busy day today.
Now, thanks to your late sample, is Ok also on mine.
You can see the an advance progress of the project at
https://skydrive.live.com/?cid=bd518...181B9505%21115

For sure I'll not be able to achieve this result that is really a big help.

Once again thanks and I am sure I'll be back shortly with more questions.

Regards
Paul

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

Ciao Paul,

Am Wed, 10 Apr 2013 00:04:25 +0200 schrieb Paul:

sorry for a late reply but was a busy day today.

never mind.

You can see the an advance progress of the project at
https://skydrive.live.com/?cid=bd518...181B9505%21115

It looks good. Thank you for the feedback.
Your welcome.


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