Formula to increment indirect references

**NBVC** · 03-05-2009, 09:56 PM

so if you openb my attachment you see blanks or when you apply it to your own document?

I see that you are in compatibility mode. I can save this in XL2007 tomorrow and then you can trt to open it in 2007 and see if that affects it..

**freud1** · 03-06-2009, 04:57 AM

That would be great,

Yes the sheet you sent doesnt populate with walues, I dont know if it effects it but removing the +0 from the formula reults in the Iserr function returning a false value, however it still results in a N/A fromt he whole formula

**NBVC** · 03-06-2009, 08:56 AM

Ok. Try now.

You shouldn't remove the +0... that converts the text string date (e.g "February 1, 2008") to an actual serial number that excel understands and can compare to the dates in the other sheet....

I am not sure why you would be seeing blanks.

**freud1** · 03-06-2009, 10:48 AM

Hi,

yes im still producing blank cells,

Its definately a TRUE return on your Iserr formula because if i change the "" value to "error"

=IF(ISERR(($G$19&" "&ROWS($A$1:$A1)&", 2008")+0),"error",INDEX('0091 911'!B:B,MATCH(($G$19&" "&ROWS($A$1:$A1)&", 2008")+0,'0091 911'!$A:$A,0)))

then all cells populate with "error"

does this help?

**NBVC** · 03-06-2009, 10:53 AM

That is truly strange....

What do you get if we cut out the IF() part:

=INDEX('0091 911'!B:B,MATCH(($G$19&" "&ROWS($A$1:$A1)&", 2008")+0,'0091 911'!$A:$A,0))

Out of curiousity try this revised formula in G21 and copy down and across...

=IF(MONTH(DATE(2008,$E$17,ROWS($A$1:$A1)))<>$E$17,"",INDEX('0091 911'!B:B,MATCH(DATE(2008,$E$17,ROWS($A$1:$A1)),'0091 911'!$A:$A,0)))

Does this give results?

**freud1** · 03-06-2009, 12:13 PM

Ok the second formula populated the sheet with data, im just checking through now to make sure it is the correct data! The new formula looks truely beautiful! I just got my head around the last one but this one is even better! ill let you know if the data matches up