Indirect formulas

**monza2** · 10-18-2012, 12:19 AM

Hi, How do I insert indirect into my formula, I tried and it came back with error
=SUM(IF(MONTH('Quote Register'!$K$13:$K$257)=MONTH(Q$4),IF(YEAR('Quote Register'!$K$13:$K$257)=YEAR(Q$4),IF('Quote Register'!$L$13:$L$257=$B5,IF('Quote Register'!$E$13:$E$257=$D$2,'Quote Register'!$O$13:$O$257,IF($D$2="(ALL)",'Quote Register'!$O$13:$O$257,0)),0),0),0))

Everytime I add a new line into my spreadsheet it goes from $K$13 to $K14$, I wiah to have my email stay always at $K$13

Thank you

**dilipandey** · 10-18-2012, 12:55 AM

Hi Monza2,

For me this appears to be an array formula else how will you evaluate " IF(MONTH('Quote Register'!$K$13:$K$257)=MONTH(Q$4)" .. also which part in this formula you want to reference using Indirect ?? thanks

Regards,
DILIPandey

<click on below 'star' if this helps>

**monza2** · 10-18-2012, 01:21 AM

Yes its an array formula, the refererence for indirect is $K$13
Thank you

**dilipandey** · 10-18-2012, 02:10 AM

I guess, you need to upload the sample workbook.. please attach. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**:) Sixthsense :)** · 10-18-2012, 02:30 AM

If you want to use the indirect for K13 alone then replace the K13 reference with the below one

Formula:

Please Login or Register  to view this content.

If you want to use the indirect for K13:K257 then replace the K13:K257 reference with the below one

Formula:

Please Login or Register  to view this content.

Hope that helps!

**monza2** · 10-18-2012, 10:56 PM

I have uploaded my spreadsheet.

The sheet is called "Forcast by %" and references back to "Quote Register"

Thank you very much for your assistance, greatly appreacited

**:) Sixthsense :)** · 10-19-2012, 12:36 AM

Try the below ARRAY Formula in D5 cell

Formula:

Please Login or Register  to view this content.

Try the below one for NON ARRAY Formula in D5 cell

Formula:

Please Login or Register  to view this content.

Refer the attached excel for details.

Hope that helps!

**monza2** · 10-25-2012, 10:09 PM

That worked perfectly Sixthsense, apologies for the delay in response, I have been away all this week.

Once again I greatly appreciated your assitance, many thanks