Increasing date in link

[Rhett C]

Please help..trying to autofill a link, ='[Timesheet 11-1.xls]Summary'!N$27,
into a column in a worksheet for a year's worth of timesheets. I need the
date to count for each day of the year that matches the link to that
timesheet? For instance... ='[Timesheet 11-1.xls]Summary'!N$27, ='[Timesheet
11-2.xls]Summary'!N$27, ='[Timesheet 11-3.xls]Summary'!N$27..I have just been
copying the first day and then draging it down and going through and changing
the day each month. Any ideas?

 

[T. Valko]

You *could* do this with the INDIRECT function however, it would require
that all the files you're linking to be open in order to work. That's
probably not practical.

Here's something you can do that is marginally better than what you're doing
now.

Enter this formula in your first cell. Include the full path to the files.
I'm using a dummy path in this example.

="='C:\TV\[Timesheet 11-"&ROW(A1)&".xls]Summary'!N27"

Copy down as needed. As you copy down the 11-1 will increment by 1 for each
row copied to. You will end up with TEXT strings that look like formulas:

='C:\TV\[Timesheet 11-1.xls]Summary'!N27
='C:\TV\[Timesheet 11-2.xls]Summary'!N27
='C:\TV\[Timesheet 11-3.xls]Summary'!N27
='C:\TV\[Timesheet 11-4.xls]Summary'!N27

Now, select the range of formulas and goto Edit>Copy. Then, Edit>Paste
Special>Values>OK

With the range of formulas still selected goto Edit>Replace
Find what: type an equal sign: =
Replace with: type an equal sign: =
Replace All
Close out the user form

 

[Rhett C]

That's almost it. Thanks for your time on my problem Biff. Whenever I go
into replace with = after I click on replace all it brings up an Update
Values: Timesheet 11-1.xls window then I have to go and click on the file it
is linked to in order for it to link to that sheet?

You *could* do this with the INDIRECT function however, it would require
that all the files you're linking to be open in order to work. That's
probably not practical.

Here's something you can do that is marginally better than what you're doing
now.

Enter this formula in your first cell. Include the full path to the files.
I'm using a dummy path in this example.

="='C:\TV\[Timesheet 11-"&ROW(A1)&".xls]Summary'!N27"

Copy down as needed. As you copy down the 11-1 will increment by 1 for each
row copied to. You will end up with TEXT strings that look like formulas:

='C:\TV\[Timesheet 11-1.xls]Summary'!N27
='C:\TV\[Timesheet 11-2.xls]Summary'!N27
='C:\TV\[Timesheet 11-3.xls]Summary'!N27
='C:\TV\[Timesheet 11-4.xls]Summary'!N27

Now, select the range of formulas and goto Edit>Copy. Then, Edit>Paste
Special>Values>OK

With the range of formulas still selected goto Edit>Replace
Find what: type an equal sign: =
Replace with: type an equal sign: =
Replace All
Close out the user form


--
Biff
Microsoft Excel MVP

Please help..trying to autofill a link, ='[Timesheet
11-1.xls]Summary'!N$27,
into a column in a worksheet for a year's worth of timesheets. I need the
date to count for each day of the year that matches the link to that
timesheet? For instance... ='[Timesheet 11-1.xls]Summary'!N$27,
='[Timesheet
11-2.xls]Summary'!N$27, ='[Timesheet 11-3.xls]Summary'!N$27..I have just
been
copying the first day and then draging it down and going through and
changing
the day each month. Any ideas?

 

[T. Valko]

Hmmm...

Did you include the full path in the original formula?

It works just fine for me.

--
Biff
Microsoft Excel MVP

That's almost it. Thanks for your time on my problem Biff. Whenever I go
into replace with = after I click on replace all it brings up an Update
Values: Timesheet 11-1.xls window then I have to go and click on the file
it
is linked to in order for it to link to that sheet?

You *could* do this with the INDIRECT function however, it would require
that all the files you're linking to be open in order to work. That's
probably not practical.

Here's something you can do that is marginally better than what you're
doing
now.

Enter this formula in your first cell. Include the full path to the
files.
I'm using a dummy path in this example.

="='C:\TV\[Timesheet 11-"&ROW(A1)&".xls]Summary'!N27"

Copy down as needed. As you copy down the 11-1 will increment by 1 for
each
row copied to. You will end up with TEXT strings that look like formulas:

='C:\TV\[Timesheet 11-1.xls]Summary'!N27
='C:\TV\[Timesheet 11-2.xls]Summary'!N27
='C:\TV\[Timesheet 11-3.xls]Summary'!N27
='C:\TV\[Timesheet 11-4.xls]Summary'!N27

Now, select the range of formulas and goto Edit>Copy. Then, Edit>Paste
Special>Values>OK

With the range of formulas still selected goto Edit>Replace
Find what: type an equal sign: =
Replace with: type an equal sign: =
Replace All
Close out the user form


--
Biff
Microsoft Excel MVP

Please help..trying to autofill a link, ='[Timesheet
11-1.xls]Summary'!N$27,
into a column in a worksheet for a year's worth of timesheets. I need
the
date to count for each day of the year that matches the link to that
timesheet? For instance... ='[Timesheet 11-1.xls]Summary'!N$27,
='[Timesheet
11-2.xls]Summary'!N$27, ='[Timesheet 11-3.xls]Summary'!N$27..I have
just
been
copying the first day and then draging it down and going through and
changing
the day each month. Any ideas?

 

[Rhett C]

I had both worksheets open and in the cell that I want the link in I entered
= then went to the other sheet, clicked on the cell to link to and pressed
enter, that was the orig formula, the path I provided? Thanks for your time.

Hmmm...

Did you include the full path in the original formula?

It works just fine for me.

--
Biff
Microsoft Excel MVP

That's almost it. Thanks for your time on my problem Biff. Whenever I go
into replace with = after I click on replace all it brings up an Update
Values: Timesheet 11-1.xls window then I have to go and click on the file
it
is linked to in order for it to link to that sheet?

You *could* do this with the INDIRECT function however, it would require
that all the files you're linking to be open in order to work. That's
probably not practical.

Here's something you can do that is marginally better than what you're
doing
now.

Enter this formula in your first cell. Include the full path to the
files.
I'm using a dummy path in this example.

="='C:\TV\[Timesheet 11-"&ROW(A1)&".xls]Summary'!N27"

Copy down as needed. As you copy down the 11-1 will increment by 1 for
each
row copied to. You will end up with TEXT strings that look like formulas:

='C:\TV\[Timesheet 11-1.xls]Summary'!N27
='C:\TV\[Timesheet 11-2.xls]Summary'!N27
='C:\TV\[Timesheet 11-3.xls]Summary'!N27
='C:\TV\[Timesheet 11-4.xls]Summary'!N27

Now, select the range of formulas and goto Edit>Copy. Then, Edit>Paste
Special>Values>OK

With the range of formulas still selected goto Edit>Replace
Find what: type an equal sign: =
Replace with: type an equal sign: =
Replace All
Close out the user form


--
Biff
Microsoft Excel MVP


Please help..trying to autofill a link, ='[Timesheet
11-1.xls]Summary'!N$27,
into a column in a worksheet for a year's worth of timesheets. I need
the
date to count for each day of the year that matches the link to that
timesheet? For instance... ='[Timesheet 11-1.xls]Summary'!N$27,
='[Timesheet
11-2.xls]Summary'!N$27, ='[Timesheet 11-3.xls]Summary'!N$27..I have
just
been
copying the first day and then draging it down and going through and
changing
the day each month. Any ideas?

 

[T. Valko]

Ok, after you do that close the linked file and Excel will automatically add
the *full path* to the formula. The full path includes all drives and
directories/subdirectories.

You don't want the actual link formula. You want the formula that returns a
TEXT string that looks like a formula. So, you have to convet that actual
link formula to return a TEXT string that looks like a formula.

--
Biff
Microsoft Excel MVP

I had both worksheets open and in the cell that I want the link in I
entered
= then went to the other sheet, clicked on the cell to link to and pressed
enter, that was the orig formula, the path I provided? Thanks for your
time.

Hmmm...

Did you include the full path in the original formula?

It works just fine for me.

--
Biff
Microsoft Excel MVP

That's almost it. Thanks for your time on my problem Biff. Whenever I
go
into replace with = after I click on replace all it brings up an Update
Values: Timesheet 11-1.xls window then I have to go and click on the
file
it
is linked to in order for it to link to that sheet?

:

You *could* do this with the INDIRECT function however, it would
require
that all the files you're linking to be open in order to work. That's
probably not practical.

Here's something you can do that is marginally better than what you're
doing
now.

Enter this formula in your first cell. Include the full path to the
files.
I'm using a dummy path in this example.

="='C:\TV\[Timesheet 11-"&ROW(A1)&".xls]Summary'!N27"

Copy down as needed. As you copy down the 11-1 will increment by 1 for
each
row copied to. You will end up with TEXT strings that look like
formulas:

='C:\TV\[Timesheet 11-1.xls]Summary'!N27
='C:\TV\[Timesheet 11-2.xls]Summary'!N27
='C:\TV\[Timesheet 11-3.xls]Summary'!N27
='C:\TV\[Timesheet 11-4.xls]Summary'!N27

Now, select the range of formulas and goto Edit>Copy. Then, Edit>Paste
Special>Values>OK

With the range of formulas still selected goto Edit>Replace
Find what: type an equal sign: =
Replace with: type an equal sign: =
Replace All
Close out the user form


--
Biff
Microsoft Excel MVP


Please help..trying to autofill a link, ='[Timesheet
11-1.xls]Summary'!N$27,
into a column in a worksheet for a year's worth of timesheets. I
need
the
date to count for each day of the year that matches the link to that
timesheet? For instance... ='[Timesheet 11-1.xls]Summary'!N$27,
='[Timesheet
11-2.xls]Summary'!N$27, ='[Timesheet 11-3.xls]Summary'!N$27..I have
just
been
copying the first day and then draging it down and going through and
changing
the day each month. Any ideas?

 

[Rhett C]

Thanks so much for your time Biff, that took care of it. Worked beutiful.
I'm sure my supervisor will change the spreadsheet now that I have it setup
for all of next year!

Ok, after you do that close the linked file and Excel will automatically add
the *full path* to the formula. The full path includes all drives and
directories/subdirectories.

You don't want the actual link formula. You want the formula that returns a
TEXT string that looks like a formula. So, you have to convet that actual
link formula to return a TEXT string that looks like a formula.

--
Biff
Microsoft Excel MVP

I had both worksheets open and in the cell that I want the link in I
entered
= then went to the other sheet, clicked on the cell to link to and pressed
enter, that was the orig formula, the path I provided? Thanks for your
time.

Hmmm...

Did you include the full path in the original formula?

It works just fine for me.

--
Biff
Microsoft Excel MVP


That's almost it. Thanks for your time on my problem Biff. Whenever I
go
into replace with = after I click on replace all it brings up an Update
Values: Timesheet 11-1.xls window then I have to go and click on the
file
it
is linked to in order for it to link to that sheet?

:

You *could* do this with the INDIRECT function however, it would
require
that all the files you're linking to be open in order to work. That's
probably not practical.

Here's something you can do that is marginally better than what you're
doing
now.

Enter this formula in your first cell. Include the full path to the
files.
I'm using a dummy path in this example.

="='C:\TV\[Timesheet 11-"&ROW(A1)&".xls]Summary'!N27"

Copy down as needed. As you copy down the 11-1 will increment by 1 for
each
row copied to. You will end up with TEXT strings that look like
formulas:

='C:\TV\[Timesheet 11-1.xls]Summary'!N27
='C:\TV\[Timesheet 11-2.xls]Summary'!N27
='C:\TV\[Timesheet 11-3.xls]Summary'!N27
='C:\TV\[Timesheet 11-4.xls]Summary'!N27

Now, select the range of formulas and goto Edit>Copy. Then, Edit>Paste
Special>Values>OK

With the range of formulas still selected goto Edit>Replace
Find what: type an equal sign: =
Replace with: type an equal sign: =
Replace All
Close out the user form


--
Biff
Microsoft Excel MVP


Please help..trying to autofill a link, ='[Timesheet
11-1.xls]Summary'!N$27,
into a column in a worksheet for a year's worth of timesheets. I
need
the
date to count for each day of the year that matches the link to that
timesheet? For instance... ='[Timesheet 11-1.xls]Summary'!N$27,
='[Timesheet
11-2.xls]Summary'!N$27, ='[Timesheet 11-3.xls]Summary'!N$27..I have
just
been
copying the first day and then draging it down and going through and
changing
the day each month. Any ideas?

 

[T. Valko]

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Thanks so much for your time Biff, that took care of it. Worked beutiful.
I'm sure my supervisor will change the spreadsheet now that I have it
setup
for all of next year!

Ok, after you do that close the linked file and Excel will automatically
add
the *full path* to the formula. The full path includes all drives and
directories/subdirectories.

You don't want the actual link formula. You want the formula that returns
a
TEXT string that looks like a formula. So, you have to convet that actual
link formula to return a TEXT string that looks like a formula.

--
Biff
Microsoft Excel MVP

I had both worksheets open and in the cell that I want the link in I
entered
= then went to the other sheet, clicked on the cell to link to and
pressed
enter, that was the orig formula, the path I provided? Thanks for your
time.

:

Hmmm...

Did you include the full path in the original formula?

It works just fine for me.

--
Biff
Microsoft Excel MVP


That's almost it. Thanks for your time on my problem Biff.
Whenever I
go
into replace with = after I click on replace all it brings up an
Update
Values: Timesheet 11-1.xls window then I have to go and click on the
file
it
is linked to in order for it to link to that sheet?

:

You *could* do this with the INDIRECT function however, it would
require
that all the files you're linking to be open in order to work.
That's
probably not practical.

Here's something you can do that is marginally better than what
you're
doing
now.

Enter this formula in your first cell. Include the full path to the
files.
I'm using a dummy path in this example.

="='C:\TV\[Timesheet 11-"&ROW(A1)&".xls]Summary'!N27"

Copy down as needed. As you copy down the 11-1 will increment by 1
for
each
row copied to. You will end up with TEXT strings that look like
formulas:

='C:\TV\[Timesheet 11-1.xls]Summary'!N27
='C:\TV\[Timesheet 11-2.xls]Summary'!N27
='C:\TV\[Timesheet 11-3.xls]Summary'!N27
='C:\TV\[Timesheet 11-4.xls]Summary'!N27

Now, select the range of formulas and goto Edit>Copy. Then,
Edit>Paste
Special>Values>OK

With the range of formulas still selected goto Edit>Replace
Find what: type an equal sign: =
Replace with: type an equal sign: =
Replace All
Close out the user form


--
Biff
Microsoft Excel MVP


Please help..trying to autofill a link, ='[Timesheet
11-1.xls]Summary'!N$27,
into a column in a worksheet for a year's worth of timesheets. I
need
the
date to count for each day of the year that matches the link to
that
timesheet? For instance... ='[Timesheet 11-1.xls]Summary'!N$27,
='[Timesheet
11-2.xls]Summary'!N$27, ='[Timesheet 11-3.xls]Summary'!N$27..I
have
just
been
copying the first day and then draging it down and going through
and
changing
the day each month. Any ideas?