file name in a formula

[MAANI]

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]

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.

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

 

[Lori Miller]

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.

 

[Lori Miller]

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 Edit>Links 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.

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]

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.

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 Edit>Links 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.

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

 

[Lori Miller]

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 Edit>Links 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.

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.

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 Edit>Links 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]

The data|text to columns did return #Ref errors without the prompt.

Thanks for the tip.

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 Edit>Links 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.

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.

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 Edit>Links 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