**Link 2 sheets based on particular field**

[R-M]

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.

[AA2e72E]

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

[AA2e72E]

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

[R-M]

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
<[email protected]> 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/

[AA2e72E]

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
> <[email protected]> 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/
>

[broro183]

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)*VLOOKUP(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!A2,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...