Is it possible to change a Sheet reference in a formula?

I have the following reference to another sheet:
='[CSTS 38878 432.xls]TIME REPORT'!$U7

The Date serial number and the following three digit number will change and
are values on my sheet. What I would like to do is something like this:
="'[CSTS " & Q1 & " " & S2 & ".xls]TIME REPORT"& "'!$U7"
Where the value of Q1 is 38878 and S2 is 432

Date Serial number (38878) changes week to week, the three digit number
changes from individual employees.

Can this be done, or am I wasting my time?

Thanks In Advance,

Stuart

This can be done with the INDIRECT function, however both workbooks will need
to be open for it to work.

=INDIRECT("'[CSTS "&$Q$1&" "&$S$2&".xls]TIME REPORT'!$U7")

Note that if copied, the $U7 portion of the formula will not increment. But
if needed, this can be accomplished with use of the ROW() funciton.

Otherwise, you'll need to look into a VB solution.

HTH,
Elkar


"Stuart Peters" wrote:

> I have the following reference to another sheet:
> ='[CSTS 38878 432.xls]TIME REPORT'!$U7
>
> The Date serial number and the following three digit number will change and
> are values on my sheet. What I would like to do is something like this:
> ="'[CSTS " & Q1 & " " & S2 & ".xls]TIME REPORT"& "'!$U7"
> Where the value of Q1 is 38878 and S2 is 432
>
> Date Serial number (38878) changes week to week, the three digit number
> changes from individual employees.
>
> Can this be done, or am I wasting my time?
>
> Thanks In Advance,
>
> Stuart