Auto-fill text - links to other sheets

[Liz]

Hi

I need to link cells from many excel sheets into one master file. Is there a
way to auto fill the cells so that

A1
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F01\[HUN_F01.xlsx]GIS Details'!A3

And then A2
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F02\[HUN_F02.xlsx]GIS Details'!A3

and then A3
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F03\[HUN_F03.xlsx]GIS Details'!A3

and so on. i.e is there a way to automatically increase the numbers mid
formula?

Would be very appreciative of any help!

 

[Dave Peterson]

What you'd want to use is =indirect() and build a formula that results in the
string that points at the folder, file, sheet, location.

But the bad thing is that =indirect() won't work if that sending file 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.

=============
If I had to do this just a single time, I'd use this technique:

I'd build a formula that would create a string that looked like your formula:

Put this in A1:

="='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\"
&"4.8. Field work\Asset Logs\Hunters Hill\Facilities\"
&"[HUN_F" & text(row(),"00") & ".xlsx]GIS Details'!A3"

Double check your typing now!

Then drag down as far as you need.

You'll end up with a string that looks like your formulas.

Then select that range and convert it to values
(Copy|paste special|values)

Now select that column.
Data|text to columns
delimited (but don't choose anything)
And plop it right back where you got it.

Excel will see this as you re-editing each formula.

And you should have your results after excel recalcs.

Hi

I need to link cells from many excel sheets into one master file. Is there a
way to auto fill the cells so that

A1
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F01\[HUN_F01.xlsx]GIS Details'!A3

And then A2
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F02\[HUN_F02.xlsx]GIS Details'!A3

and then A3
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F03\[HUN_F03.xlsx]GIS Details'!A3

and so on. i.e is there a way to automatically increase the numbers mid
formula?

Would be very appreciative of any help!

 

[Liz]

Great - that helped a lot and I know for next time too.

Thanks

Liz

What you'd want to use is =indirect() and build a formula that results in the
string that points at the folder, file, sheet, location.

But the bad thing is that =indirect() won't work if that sending file 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.

=============
If I had to do this just a single time, I'd use this technique:

I'd build a formula that would create a string that looked like your formula:

Put this in A1:

="='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\"
&"4.8. Field work\Asset Logs\Hunters Hill\Facilities\"
&"[HUN_F" & text(row(),"00") & ".xlsx]GIS Details'!A3"

Double check your typing now!

Then drag down as far as you need.

You'll end up with a string that looks like your formulas.

Then select that range and convert it to values
(Copy|paste special|values)

Now select that column.
Data|text to columns
delimited (but don't choose anything)
And plop it right back where you got it.

Excel will see this as you re-editing each formula.

And you should have your results after excel recalcs.

Hi

I need to link cells from many excel sheets into one master file. Is there a
way to auto fill the cells so that

A1
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F01\[HUN_F01.xlsx]GIS Details'!A3

And then A2
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F02\[HUN_F02.xlsx]GIS Details'!A3

and then A3
='\\Ausyd1fp002\projects\60097281_Pmatta_River\4. Tech work area\4.8. Field
work\Asset Logs\Hunters Hill\Facilities\HUN_F03\[HUN_F03.xlsx]GIS Details'!A3

and so on. i.e is there a way to automatically increase the numbers mid
formula?

Would be very appreciative of any help!