If formlua with two conditions and a raference data

[Kris K]

Hello,


I need help creating a function that will allow me to do the following:


IF B1 shows true then I want the cells B2 to B21 show values from data range in another worksheet (i.e. B2 will show value from sheet5 C1, B3 will show value from sheet5C2, etc.. till the 20th value) Also, this is critical, the value range will start from the date that A1 shows. So if B1 says True and A1 shows date of 2007.07.15 then I want the B2 to B21 show values from data range in sheet5 starting from that date till 20 days later(Column A in worksheet5 has a list of dates, Column C has the value)


I hope the above is clear. It is only half of what I need to accomplish but if I have the above figured out then I can do the other half without a problem. I know this is part If and Part vlookup or choose but the from specific date gets me lost.


Help is very much appreciated. And thank you for all the help I already received in previous posts from which I have learned much!

 

[Luke M]

=IF($B$1,INDEX('Sheet5'!C:C,MATCH($A$1,'Sheet5'!A:A,0)+ROW(A1)-1),"B1 is false")


The match function tells the Index function which row to look at. The "+ROW(A1)-1" bit is there to increment the cells as you copy the formula down.

 

[Kris K]

Hi Luke thanks for looking into my problem. I think I got you a bit confused as to what it is i'm looking for. Sorry! I'm going to try to explain it again.


Sheet1:


__A, B, C, D, E, F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V

1_2006.09.24,True,1.4561, 1.4457, 1.4513, ...,_,_,_,_,_,_,_,_,_,_,_,_,_,_,1.4915

2_2006.09.25,True,1.4457, 1.4513, ...

3_2006.09.26,False,0 , 0 , 0 , ...


Sheet5:


__A, B, C,

1_2006.09.24,__,1.4561

2_2006.09.25,__,1.4457

3_2006.09.26,__,1.4513

4_ ... ,__, ...

20_2006.10.13,__, 1.4915


So if sheet1 B1 shows True I want cells in row 1 from column C to V shows values from Sheet5 column C respectivily starting from the date the Sheet1 A1 is showing. Esentially what I need in Sheet1 are values for the next 20 days starting with the date in Sheet1 A1, otherwsie show value of 0.


I hope the above ilustration also helps clerify things. Let me know if you have any questions. I really appreciate your help as I'm stuck!

 

[Hui]

Kris


On Sheet1!C1:

=IFERROR(IF($B1,OFFSET(INDIRECT("Sheet5!a"&MATCH($A1+COLUMN()-COLUMN($C1),Sheet5!$A:$A,0)),,2,),"-"),"-")

Copy across and down

 

[Kris K]

Hi Hui,


Thanks for taking look at this. I did what you wrote but when I copy down and across all I get is - in each cell. Am I missing something i.e. am I supose to replace the "-" with cell references?...still new to excel but I'm learning.


Thanks for taking your time helping me.

 

[Hui]

Can you post your file somewhere ?

or email to me, my email is at the botto of the page Excel Ninja

Click on Excel Ninja to the left

 

[Hui]

Your Dates are text and not Dates


To convert them to Dates Select the dates in Column A

Data, Text to Columns, Next, Next, Date YMD and accept

Change both sheets

 

[Kris K]

Hi Hui,


Thank you for the feed back. I did what you wrote and fixed that problem but now once I have done everything above I get a Circular Reference Warning. And when I copy across and down I get - in most cells and 0 in some. I emailed the updated file to you for reference. This formula is beyond me so I'm not sure where to go from here.

 

[Hui]

You changed the formula I supplied to reference the Sheet you are on and not Sheet 2

Hence the Circular Ref error


Change Sheet1:C1 to

=IFERROR(IF($B1,OFFSET(INDIRECT("Sheet2!a"&MATCH($A1+COLUMN()-COLUMN($C1),Sheet2!$A:$A,0)),,2,),"-"),"-")


Copy down and across

 

[Kris K]

Hi Hui,


I feel dumb. Sorry about that, totally missed it. I was thinking one thing and did another. I have been lately spending too much time in front of the screen inputting data and I’m starting to miss the obvious.


I made the correction and it works. Thank you so much for your help, I would not be able to get it done without it!


If I could only bother you for a little more help with this formula I would be very grateful.


I was wondering if there is a way for the – to be replaced with the value of the next calendar date. i.e. If the reference date data skips a calendar date (because it happens to be a weekend or a holiday; data dates represent the days the trading market is open) the target cell would then show a value of the next calendar date from the date data reference range. This way when I copy across in my target sheet I don’t have any – but back to back values of the next 20 calendar dates so I then can use it to build cash flow model.