**Link 2 sheets based on particular field**
 

Hi

I'm working with Ms Office 2000 and I have problem in MsExcel,
I have my information in 2 sheets like below:

sheet1: sheet2:
_______________ __________________

code rate code qty
_____ ______ ______ __________


My desired result is to have multiplied (rate*qty) for each code in 3rd
sheet of my file.
how is it possible? in other words, can I use some concepts of select
statement like in SQL 2000 enviroment?
or is there another solution?

Any help would be thanked.

**Link 2 sheets based on particular field**
 

Try:

Sub aa()
Cnn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\relational.xls;Extended
Properties=Excel 8.0;"
Sql="'SELECT a.Code,a.Qty,b.Rate, (a.Qty * b.Rate) as Cost FROM [Sheet1$] a,
[Sheet2$] b WHERE a.Code = b.Code;"
Set ADORS = CreateObject("ADODB.RecordSet")
ADORS.Open Sql, Cnn
' .. lookup CopyFromRecordSet in the help file if you want to add results to
another Sheet
End Sub

NOTE: Replace c:\relational.xls by your workbook; you can use
activeworkbook.fullname (you will have to concatenate the cnn string
accordingly)

"R-M" wrote:

Hi

I'm working with Ms Office 2000 and I have problem in MsExcel,
I have my information in 2 sheets like below:

sheet1: sheet2:
_______________ __________________

code rate code qty
_____ ______ ______ __________


My desired result is to have multiplied (rate*qty) for each code in 3rd
sheet of my file.
how is it possible? in other words, can I use some concepts of select
statement like in SQL 2000 enviroment?
or is there another solution?

Any help would be thanked.

**Link 2 sheets based on particular field**
 

.... oops!, you need to include these line before End Sub

ADORS.Close
Set ADORS = Nothing

"R-M" wrote:

Hi

I'm working with Ms Office 2000 and I have problem in MsExcel,
I have my information in 2 sheets like below:

sheet1: sheet2:
_______________ __________________

code rate code qty
_____ ______ ______ __________


My desired result is to have multiplied (rate*qty) for each code in 3rd
sheet of my file.
how is it possible? in other words, can I use some concepts of select
statement like in SQL 2000 enviroment?
or is there another solution?

Any help would be thanked.

**Link 2 sheets based on particular field**
 

Thanks, But it seems I need to write this code in VB enviroment! what can
I do to solve my problem just in Excel enviroment without helping of
others?

On Thu, 20 Apr 2006 03:11:02 -0700, AA2e72E
wrote:

Try:

Sub aa()
Cnn="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\relational.xls;Extended
Properties=Excel 8.0;"
Sql="'SELECT a.Code,a.Qty,b.Rate, (a.Qty * b.Rate) as Cost FROM
[Sheet1$] a,
[Sheet2$] b WHERE a.Code = b.Code;"
Set ADORS = CreateObject("ADODB.RecordSet")
ADORS.Open Sql, Cnn
' .. lookup CopyFromRecordSet in the help file if you want to add
results to
another Sheet
End Sub

NOTE: Replace c:\relational.xls by your workbook; you can use
activeworkbook.fullname (you will have to concatenate the cnn string
accordingly)

"R-M" wrote:

Hi

I'm working with Ms Office 2000 and I have problem in MsExcel,
I have my information in 2 sheets like below:

sheet1: sheet2:
_______________ __________________

code rate code qty
_____ ______ ______ __________


My desired result is to have multiplied (rate*qty) for each code in 3rd
sheet of my file.
how is it possible? in other words, can I use some concepts of select
statement like in SQL 2000 enviroment?
or is there another solution?

Any help would be thanked.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

**Link 2 sheets based on particular field**
 

The code (macro) will work in Excel.

"R-M" wrote:

Thanks, But it seems I need to write this code in VB enviroment! what can
I do to solve my problem just in Excel enviroment without helping of
others?

On Thu, 20 Apr 2006 03:11:02 -0700, AA2e72E
wrote:

Try:

Sub aa()
Cnn="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\relational.xls;Extended
Properties=Excel 8.0;"
Sql="'SELECT a.Code,a.Qty,b.Rate, (a.Qty * b.Rate) as Cost FROM
[Sheet1$] a,
[Sheet2$] b WHERE a.Code = b.Code;"
Set ADORS = CreateObject("ADODB.RecordSet")
ADORS.Open Sql, Cnn
' .. lookup CopyFromRecordSet in the help file if you want to add
results to
another Sheet
End Sub

NOTE: Replace c:\relational.xls by your workbook; you can use
activeworkbook.fullname (you will have to concatenate the cnn string
accordingly)

"R-M" wrote:

Hi

I'm working with Ms Office 2000 and I have problem in MsExcel,
I have my information in 2 sheets like below:

sheet1: sheet2:
_______________ __________________

code rate code qty
_____ ______ ______ __________


My desired result is to have multiplied (rate*qty) for each code in 3rd
sheet of my file.
how is it possible? in other words, can I use some concepts of select
statement like in SQL 2000 enviroment?
or is there another solution?

Any help would be thanked.




--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/

**Link 2 sheets based on particular field**
 

Hi,
This problem can be solved without the use of any code as the inbuilt
VLookup or a combination of the Index & Match functions of Excel will
do the job & may be quicker than the code solution already offered.

Assuming your codes are listed in column A of all the sheets & the
other variable is in column B on each of the sheets, enter the
following into column B of the 3rd sheet and copy it down:
(Adjust the lookup ranges to match the amount of data in the sheets.)

=VLOOKUP(Sheet3!A2,Sheet1!$A$1:$B$2,2,FALSE)*VLOOK UP(Sheet3!A2,Sheet2!$A$1:$B$2,2,FALSE)

Or

=INDEX(Sheet1!$A$1:$B$2,MATCH(Sheet3!A2,Sheet1!$A$ 1:$A$2,0),2)*INDEX(Sheet2!$A$1:$B$2,MATCH(Sheet3!A 2,Sheet2!$A$1:$A$2,0),2)

Once the results have been found I'd copy & paste as values to speed up
your workbook & minimise the file size. I usually keep a copy of the
formula in a comment on the header row in case it is needed again when
values ie the rate or the quantity change.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=534484