| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Increasing date in link
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Increasing date in link
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 EditCopy. Then, EditPaste SpecialValuesOK With the range of formulas still selected goto EditReplace Find what: type an equal sign: = Replace with: type an equal sign: = Replace All Close out the user form -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Increasing date in link
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? "T. Valko" wrote: 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 EditCopy. Then, EditPaste SpecialValuesOK With the range of formulas still selected goto EditReplace Find what: type an equal sign: = Replace with: type an equal sign: = Replace All Close out the user form -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Increasing date in link
Hmmm...
Did you include the full path in the original formula? It works just fine for me. -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? "T. Valko" wrote: 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 EditCopy. Then, EditPaste SpecialValuesOK With the range of formulas still selected goto EditReplace Find what: type an equal sign: = Replace with: type an equal sign: = Replace All Close out the user form -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Increasing date in link
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. "T. Valko" wrote: Hmmm... Did you include the full path in the original formula? It works just fine for me. -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? "T. Valko" wrote: 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 EditCopy. Then, EditPaste SpecialValuesOK With the range of formulas still selected goto EditReplace Find what: type an equal sign: = Replace with: type an equal sign: = Replace All Close out the user form -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Increasing date in link
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 "Rhett C" wrote in message ... 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. "T. Valko" wrote: Hmmm... Did you include the full path in the original formula? It works just fine for me. -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? "T. Valko" wrote: 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 EditCopy. Then, EditPaste SpecialValuesOK With the range of formulas still selected goto EditReplace Find what: type an equal sign: = Replace with: type an equal sign: = Replace All Close out the user form -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Increasing date in link
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! :) "T. Valko" wrote: 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 "Rhett C" wrote in message ... 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. "T. Valko" wrote: Hmmm... Did you include the full path in the original formula? It works just fine for me. -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? "T. Valko" wrote: 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 EditCopy. Then, EditPaste SpecialValuesOK With the range of formulas still selected goto EditReplace Find what: type an equal sign: = Replace with: type an equal sign: = Replace All Close out the user form -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Increasing date in link
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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! :) "T. Valko" wrote: 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 "Rhett C" wrote in message ... 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. "T. Valko" wrote: Hmmm... Did you include the full path in the original formula? It works just fine for me. -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? "T. Valko" wrote: 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 EditCopy. Then, EditPaste SpecialValuesOK With the range of formulas still selected goto EditReplace Find what: type an equal sign: = Replace with: type an equal sign: = Replace All Close out the user form -- Biff Microsoft Excel MVP "Rhett C" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Increasing link values | Links and Linking in Excel | |||
| How to link an Excel file due date to Outlook calendar date? | New Users to Excel | |||
| link a date between two worksheets. | Excel Discussion (Misc queries) | |||
| Using date, link to a cell | Excel Discussion (Misc queries) | |||
| Increasing the date/time in cells on a worksheet with a button | Excel Worksheet Functions | |||
![Rhett C[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
