Pulling cell reference into a formula

[kimi01]

How do I pull a cell reference into a formula?

In the attached, I'm returning the cell reference into cell D4.

I have a formula in C10 that I want to drop this reference into. It needs to dynamically drop into the section at the end that I've highlighted.

=IF(A10<(TODAY()),"",IFERROR(IF(A10<(TODAY()),"",IF(A9>(TODAY()),C9,(FORECAST.ETS(A9,$B$9:B10,$A$9:A10)))),""))

What syntax do I need for that?

[Pete_UK]

You can use this in C10:

=IF(A10<(TODAY()),"",IFERROR(IF(A10<(TODAY()),"",IF(A9>(TODAY()),C9,(FORECAST.ETS(A9,$B$9:B10,INDIRECT(D4&":A10"))))),""))

Hope this helps.

Pete

[kimi01]

Thank you. That gets me part of the way there.

Is there a way to have the "A10" part of the formula remain as a dynamic cell reference, so that if I drag that formula down the column A10 becomes A11, A12, A13 etc.?

=IF(A10<(TODAY()),"",IFERROR(IF(A10<(TODAY()),"",IF(A9>(TODAY()),C9,(FORECAST.ETS(A9,$B$9:B10,INDIRECT(D4&":A10"))))),""))

[Pete_UK]

As it stands, it is inside quotes and that means it is treated as a text string when it is copied down. You need to put the row part outside the quotes and to have it change when copied down - here's one way to do that:

=IF(A10<(TODAY()),"",IFERROR(IF(A10<(TODAY()),"",IF(A9>(TODAY()),C9,(FORECAST.ETS(A9,$B$9:B10,INDIRECT(D4&":A"&ROWS($1:10)))))),""))

Hope this helps.

Pete