Changing Cell Value, probably simple...

[LeeFrankel]

Hello to all the experts

Here goes,
On one side I have a few rows of constantly updating Future-Contracts values, recent data comes into top line, all rows move down, lasst row vanishes.
On the other side I want to have a list of the various contracts and their most recent values.

For simplicity lets assume I am looking at information of one contract only
and want to keep showing its most recent value.

Example:
APR08 data date value data
Dec08 data date value data ...................APR08 [Recent-Value]
APR08 data date value data

The [Value] is allways in the same cell, D3.

Any Idea will be very helpfull and thank you,
Lee

{What I tried doing is: if(A3=K3,D3,....problem...)}
This is when I realized VBA is prolly needed.

 

[qafself]

Without seing your sheet is't difficult to be sure but INDIRECT and ROW may sort it out

e.g.

=INDIRECT("A"&ROW())

If the data moves down, the row number changes and the formula still returns the same result.

HTH

Ed

 

[LeeFrankel]

Hi Ed,

Thanks for your quick reply.
The data has to run through some tests:
a)is it the right contract
b)was it an action or just a quote.

I managed to test these but,
when I need to include an "If" I have a problem with the FALSE part.
If([corret data],INDIRECT("D"&row()),[problem])

Lee

 

[qafself]

can you zip the sheet and attach it - it's much easier to make sense of it.

Ed

 

[LeeFrankel]

can you zip the sheet and attach it - it's much easier to make sense of it.

Ed

This is a Tools-Pro Excel extension of Reuters 3000 Extra.
I can send you the file but without direct access to Reuters you will not see the data.

Instead I am attaching a print-screen of the sheet.

 

[qafself]

Hi Lee,

What I was after was seeing what the checks were so as to see what the problem is................

=if(correct data,INDIRECT("A"&ROW()),problem)

is the correct syntax.

can you give me the actual formula and the result that you are getting?

I don't see where the A3 and K3 you mentioned earlier fit.

Ed

 

[LeeFrankel]

Ok Ill give it a try
=IF(AND(A5=I5,ISERR(SEARCH(C5,"T")),C5,{Don't Know})

I5 = Contract name (i.e. MAR08 -APR08)
"T" in column C means there was a trade at this price, SEARCH would return an error if no "T" was found.

The FALSE part of the IF should contain the original value in J5.
i.e. if the data is not right, keep current data...

Thanks,
Lee

 

[qafself]

What cell is this formula going to go in?

ed

 

[LeeFrankel]

What cell is this formula going to go in?

ed

Cell J5 (under Cur Value)

 

[qafself]

OK,

As I understand it:

In J5

If A5 =I5 and if C5 doesn't contain "T", use C5, otherwise use something else - as yet undefined

to make this independent of the rows

=If(and(indirect("A"&Row())=Indirect("I"&Row()),ISERR(search(indirect("C"&row()),"T")),Indirect("C"&row()),What data do you want)

Hope I've got the parenthesis in the right places.....

we seem to be missing the false value - where does it come from? You seem to be suggesting that there is an existing value in J5 which should be retained.

This is a problem - can only be overcome with VBA. A cell can have either a formula or a value - not both at the same time - or you can use code to fill it.

Please - correct me if I have got it wrong

Ed

 

[LeeFrankel]

Hi Ed,

I am pretty sure VB is necesary.

The rows have no meaning. Data on the left side is streaming, not sorted by contract, rather then by time of action. Therefor there is no reason to link right to left side by row number.

On the right there is a list of available contracts, sorted by name.
Each line here is a different contract, whereas the left side can have same contract over and over again.

J5 can start as 0 (zero) as initial value, from that point it will be permanantly updating with the most recent value.

This is why I think there is a need for VB to solve this problem, I can't think of a way for Excel to do it alone.

 

[qafself]

Another problem

Lee,

One problem with Vb is that it needs a 'trigger' to make the code run - I am not sure if streamed information will do this.

My VB knowledge is pretty limited, I'm afraid, so I'm not going to be much use there!

It might be worth posting in another forum - I would suggest

http://www.excelforum.com/

Remember that if you ask the question eslewhere, it is good etiquette to refer to the original post.

Regards

Ed

 

[LeeFrankel]

Will do so,

If anyone else has an idea I would appriciate it.

Lee