Autofill file name into formula when dragging formula down.

Ive finally had time to sit down and start to learn excel and write a couple of workbooks i have needed forevever. Ive learned a lot by reading thru threads and let me say thanks, u really have cut my learning curve,
Im am stuck though. I cant figure out how to get autofill to advance to the next folder #. Ive posted the way i need it to work below. No matter what options i choose on autofill it doesnt work right.

='C:\Users\Andy\Desktop\Office # 1\[0001.xlsm]Information'!$T$14
='C:\Users\Andy\Desktop\Office # 1\[0002.xlsm]Information'!$T$14
='C:\Users\Andy\Desktop\Office # 1\[0003.xlsm]Information'!$T$14
='C:\Users\Andy\Desktop\Office # 1\[0004.xlsm]Information'!$T$14

Another option i couldnt figure out last nite is as follows. The folder name(number) that needs to advance is located in cell a of the same row. Can I get it to use the value in the cell in the path name.

i appreciate any help.
Andy.

If you intend to have those workbooks closed you will need to use and addin called Morefunc

and then apply the INDIRECT.EXT function like so:


=INDIRECT.EXT("'C:\Users\Andy\Desktop\Office # 1\["&A1&".xlsm]Information'!$T$14")

where A1 contains text string 0001 and A2 contains 0002 and so on.

If A1 contains the number 1 and you want to convert to 0001 then:

=INDIRECT.EXT("'C:\Users\Andy\Desktop\Office # 1\["&TEXT(A1,"0000")&".xlsm]Information'!$T$14")