Lookup or?
 

Dear friends,

I have a worksheet with 4 columns, i.e. in Column1 I record the date,
Column2-4 I record the exchange rates in JPY, GBP and USD.

I need, in a different worksheet to enter the date and Currency and to:
1. Show me in a nearby cell the exchange rate of that date and currency and
2. To highlight the corresponding date and exchange rate of that currency in
the first worksheet.

Shall I need some coding or?

Any ideas will be highly appreciated.

Thanking you in advance,

GeorgeCY

Lookup or?
 

--If the data is arranged in this format in Sheet1

Dates JPY GBP USD
8/5/2009 x x x
9/5/2009 x x x
10/5/2009 x x x

In Sheet2 C1
A1 = Query Date
B1 = exch rate (example USD)
=VLOOKUP(A1,Sheet1!A:D,MATCH(B1,A1:D1,0),0)

--For highlighting you can try Conditional Formatting

Create named ranges (menu InsertNameDefine)
qDate = Query Date = Sheet2 A1
qExc = Exch Rate = Sheet2 A2

1. Select the cell/Range (Sheet1 A:D).
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=AND(INDEX($A:$D,1,COLUMN())=qExc,INDEX($A:$D,ROW( ),1)=qDate)

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

Dear friends,

I have a worksheet with 4 columns, i.e. in Column1 I record the date,
Column2-4 I record the exchange rates in JPY, GBP and USD.

I need, in a different worksheet to enter the date and Currency and to:
1. Show me in a nearby cell the exchange rate of that date and currency and
2. To highlight the corresponding date and exchange rate of that currency in
the first worksheet.

Shall I need some coding or?

Any ideas will be highly appreciated.

Thanking you in advance,

GeorgeCY

Lookup or?
 

Correction for VLOOKUP:

In Sheet2 C1
A1 = Query Date
B1 = exch rate (example USD)
=VLOOKUP(A1,Sheet1!A:D,MATCH(B1,Sheet1!A1:D1,0),0)

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

--If the data is arranged in this format in Sheet1

Dates JPY GBP USD
8/5/2009 x x x
9/5/2009 x x x
10/5/2009 x x x

In Sheet2 C1
A1 = Query Date
B1 = exch rate (example USD)
=VLOOKUP(A1,Sheet1!A:D,MATCH(B1,A1:D1,0),0)

--For highlighting you can try Conditional Formatting

Create named ranges (menu InsertNameDefine)
qDate = Query Date = Sheet2 A1
qExc = Exch Rate = Sheet2 A2

1. Select the cell/Range (Sheet1 A:D).
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=AND(INDEX($A:$D,1,COLUMN())=qExc,INDEX($A:$D,ROW( ),1)=qDate)

4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK

If this post helps click Yes
---------------
Jacob Skaria


"George" wrote:

Dear friends,

I have a worksheet with 4 columns, i.e. in Column1 I record the date,
Column2-4 I record the exchange rates in JPY, GBP and USD.

I need, in a different worksheet to enter the date and Currency and to:
1. Show me in a nearby cell the exchange rate of that date and currency and
2. To highlight the corresponding date and exchange rate of that currency in
the first worksheet.

Shall I need some coding or?

Any ideas will be highly appreciated.

Thanking you in advance,

GeorgeCY