Index, Match, and other problems

[Sjorsvd]

Dear all,

Last week I have already posted part I of the problems, which I got some great help with.
http://www.excelforum.com/excel-form...-and-data.html

I have applied this solution to the document, but there are still some related problems.

These two problems are:

1) For some reason, while the formula (I think) defines that expired contracts should not be included, the overview still includes the wrong contracts.
I refer to the first sheet where for Cotton the contract for October (already expired, since today>expiry date). What is wrong in the formula, since I don't get it?

2) The next thing I want to do is include a price graph of the 2nd next contract that expires. This data is sorted at the bottom of the raw data sheets, where I link them to the overview pages (since these sort the expiry dates with the index,match formula). When I look up the ticker related to these expiry dates the next problem arises. For some reason the formula only works for the 1st ticker and for the rest of these it produces: #REF!.
The formula is (as far as I can see) exactly copied and I don't get why it only works for 1 ticker.

If these two problems are solved the document automatically updates every day until 2019, which as you can imagine saves tons of work!

Thank you very much for the help!

[Sjorsvd]

Anyone? Please?

[Sjorsvd]

Dear all,

Is there anyone that could help me out?

Right now I have:

=IF(ISERROR(SMALL(IF('Raw data agri'!$DE$3:$DE$27>=B$2;'Raw data agri'!$DE$3:$DE$27);ROWS(P$6:P6)));"";SMALL(IF('Raw data agri'!$DE$3:$DE$27>=B$2;'Raw data agri'!$DE$3:$DE$27);ROWS(P$6:P6)))

Where the B$2 is : =TODAY()
I don't know why it is still that contracts are included that have an expiry date that is in the past.

Thanks

[Punnam]

Sjorsvd

1) RData
2) RHistory

What are these function ? , i think they are UDF , User Defined Function .do you have any code for them with you ?

Punnam

[Sjorsvd]

Dear Punnam,

The sheets "Raw data agri", "Raw data grain" and "Raw data metal" import data from a reuters machine. This data is extracted with an excel plugin, but I don't think this should matter right?
The tickers on the left define the type of contract. Name for the name, etc.

I transfer all this data into a comprehensive overview on the sheets: "Agri desk", "grain", metal, etc.

The problem is that out of the raw data I need to extract contracts that Are not expired (today > expiry date of the contract (seen in row D)).

[griruy]

Hello Sjor,
I've taken a look at your workbook:
1. For your first question, the problem seems to be you are doing an array operation in the formula without using Ctrl+Shift+enter, do that and it should be ok.
2. The second error seems to be caused because of the column number in the match function. it should be '1' for all the rows because it's the data you request is always in the first column of the range you chose in the index function.

Regards,
Eran.

[Sjorsvd]

Eran, you are a legend!

All excel related problems are now fixed! I only have to call reuters for a minor problem, but it seems everything is linked and automatically updates untill 2019.

Thanks!!