Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
file name in a formula
I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need to
use the workbook name in a formula,like ='C:\My Folder\[wo 1.xls]Sheet1'!$H$5 is there a way that I could drag this formula down so it change the workbook name to wo 2, then wo 3, and so on ? Note: I have workbooks name wo 1 up to wo 1000 and its going up |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
file name in a formula
MAANI;282485 Wrote: I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need to use the workbook name in a formula,like ='C:\My Folder\[wo 1.xls]Sheet1'!$H$5 is there a way that I could drag this formula down so it change the workbook name to wo 2, then wo 3, and so on ? Note: I have workbooks name wo 1 up to wo 1000 and its going up Hi, if you start in row 1 try ="'C:\My Folder\[wo "&ROW()&".xls]Sheet1'!$H$5" and pull down as needed -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=78912 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
file name in a formula
The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed. If that's a problem, then Laurent Longre has an addin (morefunc.xll) at: http://xcell05.free.fr/ or http://xcell05.free.fr/morefunc/english/index.htm That includes =indirect.ext() that may help you. ===================== In a case like this, I'd buld my formulas as strings and then convert them to values and finally convert them to formulas. I like this technique: Put this formula in C4: ="$$$='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5" Change the ## to whatever to get the right file name. If the formula is in row 325, then subtract 324 to get 1. Drag down as far as you need. Then convert these formulas to values. Then do an edit|Replace what: $$$ with: (leave blank) replace all If you're not sure you created the correct formula, you'll want to test it on just a few cells. If you've made a mistake, you'll be prompted to select the name of the workbook that contains the correct worksheet. And if you've done this 1000 times, that means 1000 dismissed dialogs or killing excel. MAANI wrote: I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need to use the workbook name in a formula,like ='C:\My Folder\[wo 1.xls]Sheet1'!$H$5 is there a way that I could drag this formula down so it change the workbook name to wo 2, then wo 3, and so on ? Note: I have workbooks name wo 1 up to wo 1000 and its going up -- Dave Peterson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
file name in a formula
Try Data Consolidate...
Function: Count Reference:'C:\My Folder\[wo *.xls]Sheet1'!$H$5 Create links to source data: Check Then click OK. After it's run, expand the outline to show all the cell values. It remembers the settings, so it's easy to delete results and rerun as needed. "MAANI" wrote in message ... I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need to use the workbook name in a formula,like ='C:\My Folder\[wo 1.xls]Sheet1'!$H$5 is there a way that I could drag this formula down so it change the workbook name to wo 2, then wo 3, and so on ? Note: I have workbooks name wo 1 up to wo 1000 and its going up |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
file name in a formula
I wouldn't do an edit-replace to convert formulas with that many links -
it's slow and as you say can stall the application with typos or missing links. Instead you can evaluate the formulas: ="='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5" by copying using the office clipboard (edit menu) and pasting using the icon that appears, then choose Paste Special Text after if necessary. This will run much faster and only evaluate links that are valid. You can use the EditLinks dialog to update links after. Put this formula in C4: ="$$$='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5" Change the ## to whatever to get the right file name. If the formula is in row 325, then subtract 324 to get 1. Drag down as far as you need. Then convert these formulas to values. Then do an edit|Replace what: $$$ with: (leave blank) replace all If you're not sure you created the correct formula, you'll want to test it on just a few cells. If you've made a mistake, you'll be prompted to select the name of the workbook that contains the correct worksheet. And if you've done this 1000 times, that means 1000 dismissed dialogs or killing excel. MAANI wrote: I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need to use the workbook name in a formula,like ='C:\My Folder\[wo 1.xls]Sheet1'!$H$5 is there a way that I could drag this formula down so it change the workbook name to wo 2, then wo 3, and so on ? Note: I have workbooks name wo 1 up to wo 1000 and its going up -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
file name in a formula
I'm not sure if this will help speed anything up.
I tried it using that formula. I didn't have that folder or any file by that name. I was still prompted for each entry that didn't have a correct reference (all 10 in my sample). And after the (correct) formula is pasted, excel still has to re-evaluate it. I didn't do any testing. It's just a gut feeling and that's been wrong lots of times. Did you try it both ways to see if there was a difference in speed? And a note to the OP. If you ever decide to move those "sending" files to a network drive, you may want to think twice. I'd bet money that your local C: drive is way faster than any network drive. Lori Miller wrote: I wouldn't do an edit-replace to convert formulas with that many links - it's slow and as you say can stall the application with typos or missing links. Instead you can evaluate the formulas: ="='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5" by copying using the office clipboard (edit menu) and pasting using the icon that appears, then choose Paste Special Text after if necessary. This will run much faster and only evaluate links that are valid. You can use the EditLinks dialog to update links after. Put this formula in C4: ="$$$='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5" Change the ## to whatever to get the right file name. If the formula is in row 325, then subtract 324 to get 1. Drag down as far as you need. Then convert these formulas to values. Then do an edit|Replace what: $$$ with: (leave blank) replace all If you're not sure you created the correct formula, you'll want to test it on just a few cells. If you've made a mistake, you'll be prompted to select the name of the workbook that contains the correct worksheet. And if you've done this 1000 times, that means 1000 dismissed dialogs or killing excel. MAANI wrote: I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need to use the workbook name in a formula,like ='C:\My Folder\[wo 1.xls]Sheet1'!$H$5 is there a way that I could drag this formula down so it change the workbook name to wo 2, then wo 3, and so on ? Note: I have workbooks name wo 1 up to wo 1000 and its going up -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
file name in a formula
Actually you're right there isn't much difference in this case
as you're only linking to one cell in each workbook - if you're linking to a range say H1:H10 its many times faster and you'll only get the prompt once for each workbook. Also I had tested the clipboard method for references that are typos eg by omitting an apostrophe which it ignores but do trip the Replace method up. However i think a better alternative is to use Text to Columns after pasting values to convert to formulas (using either tab delimited or fixed width and skip the $$$s). Then you can select all links in the EditLinks dialog and update, this allows you to cancel after the first prompt if you wish and you won't get caught up in those repeated dialogs. "Dave Peterson" wrote in message ... I'm not sure if this will help speed anything up. I tried it using that formula. I didn't have that folder or any file by that name. I was still prompted for each entry that didn't have a correct reference (all 10 in my sample). And after the (correct) formula is pasted, excel still has to re-evaluate it. I didn't do any testing. It's just a gut feeling and that's been wrong lots of times. Did you try it both ways to see if there was a difference in speed? And a note to the OP. If you ever decide to move those "sending" files to a network drive, you may want to think twice. I'd bet money that your local C: drive is way faster than any network drive. Lori Miller wrote: I wouldn't do an edit-replace to convert formulas with that many links - it's slow and as you say can stall the application with typos or missing links. Instead you can evaluate the formulas: ="='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5" by copying using the office clipboard (edit menu) and pasting using the icon that appears, then choose Paste Special Text after if necessary. This will run much faster and only evaluate links that are valid. You can use the EditLinks dialog to update links after. Put this formula in C4: ="$$$='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5" Change the ## to whatever to get the right file name. If the formula is in row 325, then subtract 324 to get 1. Drag down as far as you need. Then convert these formulas to values. Then do an edit|Replace what: $$$ with: (leave blank) replace all If you're not sure you created the correct formula, you'll want to test it on just a few cells. If you've made a mistake, you'll be prompted to select the name of the workbook that contains the correct worksheet. And if you've done this 1000 times, that means 1000 dismissed dialogs or killing excel. MAANI wrote: I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need to use the workbook name in a formula,like ='C:\My Folder\[wo 1.xls]Sheet1'!$H$5 is there a way that I could drag this formula down so it change the workbook name to wo 2, then wo 3, and so on ? Note: I have workbooks name wo 1 up to wo 1000 and its going up -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
file name in a formula
The data|text to columns did return #Ref errors without the prompt.
Thanks for the tip. Lori Miller wrote: Actually you're right there isn't much difference in this case as you're only linking to one cell in each workbook - if you're linking to a range say H1:H10 its many times faster and you'll only get the prompt once for each workbook. Also I had tested the clipboard method for references that are typos eg by omitting an apostrophe which it ignores but do trip the Replace method up. However i think a better alternative is to use Text to Columns after pasting values to convert to formulas (using either tab delimited or fixed width and skip the $$$s). Then you can select all links in the EditLinks dialog and update, this allows you to cancel after the first prompt if you wish and you won't get caught up in those repeated dialogs. "Dave Peterson" wrote in message ... I'm not sure if this will help speed anything up. I tried it using that formula. I didn't have that folder or any file by that name. I was still prompted for each entry that didn't have a correct reference (all 10 in my sample). And after the (correct) formula is pasted, excel still has to re-evaluate it. I didn't do any testing. It's just a gut feeling and that's been wrong lots of times. Did you try it both ways to see if there was a difference in speed? And a note to the OP. If you ever decide to move those "sending" files to a network drive, you may want to think twice. I'd bet money that your local C: drive is way faster than any network drive. Lori Miller wrote: I wouldn't do an edit-replace to convert formulas with that many links - it's slow and as you say can stall the application with typos or missing links. Instead you can evaluate the formulas: by copying using the office clipboard (edit menu) and pasting using the icon that appears, then choose Paste Special Text after if necessary. This will run much faster and only evaluate links that are valid. You can use the EditLinks dialog to update links after. Put this formula in C4: ="$$$='C:\My Folder\[wo "&row()-##&".xls]Sheet1'!$H$5" Change the ## to whatever to get the right file name. If the formula is in row 325, then subtract 324 to get 1. Drag down as far as you need. Then convert these formulas to values. Then do an edit|Replace what: $$$ with: (leave blank) replace all If you're not sure you created the correct formula, you'll want to test it on just a few cells. If you've made a mistake, you'll be prompted to select the name of the workbook that contains the correct worksheet. And if you've done this 1000 times, that means 1000 dismissed dialogs or killing excel. MAANI wrote: I have different workbooks with names wo 1,wo 2,wo 3,....and so on,I need to use the workbook name in a formula,like ='C:\My Folder\[wo 1.xls]Sheet1'!$H$5 is there a way that I could drag this formula down so it change the workbook name to wo 2, then wo 3, and so on ? Note: I have workbooks name wo 1 up to wo 1000 and its going up -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating % formula help for log file. | Excel Discussion (Misc queries) | |||
Formula too long - new file path is shorter than old file path - Excel 2003 | Excel Worksheet Functions | |||
File Exists Formula | Excel Worksheet Functions | |||
formula link to a file | Excel Worksheet Functions | |||
i received a file that reads powerpoint document file file exten. | Excel Discussion (Misc queries) |