Using calculated cell references in a formula

[g_train]

Hi all,

I'm having a bit of trouble working out whether this is even possible, but hoping a guru here can help me out.

Basically, I have a date based spreadsheet that records the number of letters sent out per day. For each day of the month, it goes down the page for the day generated and across the page for the day and amount sent. It is possible that if there are too many, it will be spread out over a number of days.

So, I have calculated going across the first and last day, but I want to know how many Sundays are in the dispatch range. I have used a formula to work out the dispatch day range which returns "D7:J7", "E8:E10" for example, but now I want to use that range to count the number of Sundays.

This is my formula to get that (and it works perfectly):

CONCATENATE(VLOOKUP(($AS24+4),letters,2),"2",":",VLOOKUP(($AU24+4),letters,2),"2")

So, if I was using a formula, it would be:

COUNTIF(CONCATENATE(VLOOKUP(($AS24+4),letters,2),"2",":",VLOOKUP(($AU24+4),letters,2),"2"),"Sun")

I've tried this one too:

COUNTIF(CONCATENATE(VLOOKUP(($AS24+4),letters,2),"2"):CONCATENATE(VLOOKUP(($AU24+4),letters,2),"2"),"Sun")

But, Excel doesn't seem to be liking that I am specifying the range with a formula.

Has anybody come across a similar problem and is there any way around this?

I am assuming I need to tell Excel these are cell references by wrapping the VLOOKUPs in something, but I'm not sure what.



Thanks in advance!!

g_train

[NBVC]

Try:

Please Login or Register  to view this content.

[g_train]

Thanks NBVC - I'll have to look into the INDIRECT function as it is one I've never used, but that does exactly what I want it to. Thanks heaps!!