Prev: Determine number of rows with data
Next: change date in a sql statement from a cell
From: dbl7 on 16 Mar 2010 23:01
I am a total newbie with this stuff so I am hoping someone has the
patience to assist me.

I have a workbook with 2 tabs:
Tab 1 is called Historical. Historical has 2 columns. Column A1 is
"Date" and Column B1 is "ZAR to USD." Historical has a Date (MM/DD/YY)
in cell A2 and a number value in B2. The data from both columns go
down to row 111.

Tab 2 is called Services. Services has 4 columns. Column A1 is "CPT"
Column B1 is DOS, Column C1 is "ZAR" and Column D1 is "USD." The
Services tab has a 5 digit number in A2, a Date (MM/DD/YY) in B2 and a
currency value in C2 (i.e. 214.32).

I am trying to figure out how to put a formula or function (not sure
what it should be) on the Services Tab 2 in cell D2 that will take the
Date from Services Tab 2 in cell B2, match it to the same Date from
Historical Tab 1 in cell A2, then take the corresponding number value
from Historical Tab 1 in cell B2 (that matches the dates exactly), and
multiply it by the currency value from Services Tab 2 in Cell C2 with
the result being displayed on the Services Tab 2 in cell D2 called
"USD."

I appreciate any help!
Thanks!
David
From: Zoltan on 17 Mar 2010 05:56
Try: =C2*VLOOKUP(B2,Historical!$A$2:$B$111,2,FALSE)

"dbl7" wrote:

> I am a total newbie with this stuff so I am hoping someone has the
> patience to assist me.
>
> I have a workbook with 2 tabs:
> Tab 1 is called Historical. Historical has 2 columns. Column A1 is
> "Date" and Column B1 is "ZAR to USD." Historical has a Date (MM/DD/YY)
> in cell A2 and a number value in B2. The data from both columns go
> down to row 111.
>
> Tab 2 is called Services. Services has 4 columns. Column A1 is "CPT"
> Column B1 is DOS, Column C1 is "ZAR" and Column D1 is "USD." The
> Services tab has a 5 digit number in A2, a Date (MM/DD/YY) in B2 and a
> currency value in C2 (i.e. 214.32).
>
> I am trying to figure out how to put a formula or function (not sure
> what it should be) on the Services Tab 2 in cell D2 that will take the
> Date from Services Tab 2 in cell B2, match it to the same Date from
> Historical Tab 1 in cell A2, then take the corresponding number value
> from Historical Tab 1 in cell B2 (that matches the dates exactly), and
> multiply it by the currency value from Services Tab 2 in Cell C2 with
> the result being displayed on the Services Tab 2 in cell D2 called
> "USD."
>
> I appreciate any help!
> Thanks!
> David
> .
>
From: dbl7 on 17 Mar 2010 23:32
On Mar 17, 4:56 am, Zoltan <Zol...(a)discussions.microsoft.com> wrote:
> Try: =C2*VLOOKUP(B2,Historical!$A$2:$B$111,2,FALSE)
>
>
>
> "dbl7" wrote:
> > I am a total newbie with this stuff so I am hoping someone has the
> > patience to assist me.
>
> > I have a workbook with 2 tabs:
> > Tab 1 is called Historical. Historical has 2 columns. Column A1 is
> > "Date" and Column B1 is "ZAR to USD." Historical has a Date (MM/DD/YY)
> > in cell A2 and a number value in B2. The data from both columns go
> > down to row 111.
>
> > Tab 2 is called Services. Services has 4 columns. Column A1 is "CPT"
> > Column B1 is DOS, Column C1 is "ZAR" and Column D1 is "USD." The
> > Services tab has a 5 digit number in A2, a Date (MM/DD/YY) in B2 and a
> > currency value in C2 (i.e. 214.32).
>
> > I am trying to figure out how to put a formula or function (not sure
> > what it should be) on the Services Tab 2 in cell D2 that will take the
> > Date from Services Tab 2 in cell B2, match it to the same Date from
> > Historical Tab 1 in cell A2, then take the corresponding number value
> > from Historical Tab 1 in cell B2 (that matches the dates exactly), and
> > multiply it by the currency value from Services Tab 2 in Cell C2 with
> > the result being displayed on the Services Tab 2 in cell D2 called
> > "USD."
>
> > I appreciate any help!
> > Thanks!
> > David
> > .- Hide quoted text -
>
> - Show quoted text -

Thanks so much! That worked perfectly. I really appreciate it.
 | 
Pages: 1
Prev: Determine number of rows with data
Next: change date in a sql statement from a cell