Use a function as the starting cell of a range

Hello all:

I'm trying to use a range in a function, but the starting cell of the range should be changed under different circumstances. How can I do it?

For example:
I have: =-Nth_Occurrence('JULY BW Summary'!$A171:$H1000,"Result",1,0,4) in the cell. I want to replace the number 171 with a function that returns this number.

I've been trying by putting quotes and parentheses at different places but it didn't work.

BTW, Nth_Occurrence is a macro that I have in my worksheet.


I appreciate any idea on this.

Thanks.

Originally Posted by daddylonglegs

You could use INDEX, e.g.

INDEX('JULY BW Summary'!$A1:$A1000,FUNC):$H1000

where FUNC is your function that return 171

daddylonglegs, are you suggesting to plug the index formula into my function like this:
=-Nth_Occurrence(INDEX('JULY BW Summary'!$A1:$A1000,FUNC):$H1000,"Result",1,0,4)

I just tried it but it turns an error value message back to me.

Hi, daddylonglegs.

Yes I did put the function in the INDEX formula but it didn't work.
It's working when I put: =-Nth_Occurrence('JULY BW Summary'!$A171:$H1000,"Result",1,0,4)

Will it have anything to do with the format of the reference?

Thank you.

WOW!!

It's working great now. Thank you so much for your help.

Hi daddylonglegs.

Would you please do me another favor to take a look at this problem? I'm using a function as following:
=MATCH(C11,'JULY BW Summary'!$B:$B,0)

I want to replace JULY with a function I have that gives me different months. How shall I do it? I have tried several ways to put quote and & at different places but they all didn't work.