Auto fill with worksheet references

[Moose]

I've got a doozy here. I want to auto fill a reference to anothe
workbook and have the sheet numbers change consecutively.

eg. the cells in a given column should end up like this:

='C:\[payroll.xls]1'!$C$5
='C:\[payroll.xls]2'!$C$5
='C:\[payroll.xls]3'!$C$5

How do I get auto fill to make those sheet reference numbers change
Right now when I drag the reference down it auto fills the same shee
number.
I tried it as you would with cell references: I manually changed th
numbers in the first few columns, and then selected all and tried t
auto fill, but it still didn't work. Is this possible or are shee
references just static?

thanks!
Moose

 

[Frank Kabel]

Hi Moosey
1. One way - though this will work only if your other workbook
(payroll.xls) is open: Assumption you start in row 1:
=INDIRECT("'[payroll-xls]" & ROW() & "'!$C$5")
and copy down. Note the multiple apostrophes (" and ') at the beginning
and in the middle. I have skipped the path information as INDIRECT
requires the workbook to be opened. Therefore the path information is
not required

2. If your other workbook is open you may have a look a the free Add-in
MOREFUNC.XLL (http://longre.free.fr/english). Using the function
INDIRECT.EXT change the above formula to:
=INDIRECT.EXT("'C:\[payroll-xls]" & ROW() & "'!$C$5")
and copy down

3. There are other alternatives to access closed workbooks in
combination with created strings as cell reference. Have a look at the
following thread for more information: http://tinyurl.com/2c62u

Frank

 

[RagDyer]

I do this every month on a large WB that I use as a database for dyeorders.
Since I have about 18,000 dyeorders in this dB, you can bet that it works
with closed WBs as well as open ones.

It does involve *quite a few steps*, but at least I don't have to learn
Access.

Since I use it to increment WBs, and you need it to work for WSs, I ran a
quick test using your data, and it did work.

If you're going to use Columns at the beginning of your WB, enter this in
G1:

="='c:\[payroll.xls]"&ROW()&"'!c5"

NOW, when you hit <Enter>, and go back and select G1,you will see exactly
what you typed in the formula bar, but *not* in the cell itself.

Drag down to copy as needed. I usually do 300 or 400 at a time, since I'm
using WBs, but I doubt if you're using that many WSs.

Anyway, while they're still selected from the copying down, right click in
the selection and choose "Copy".

Now, right click in A1, and choose "PasteSpecial".
Choose "Values" and then <OK>.

*NOW AGAIN*, while they're still selected from the paste,
<Edit> <Replace>
In Find What, enter " *= " (no quotes),
In Replace With, enter " = " (no quotes)

Then click "Replace", or "ReplaceAll".
And you should now see the data from your WSs.

This can be a PITA, but for me with 1,000's of WBs, it pays.

Whether you wish to go through this or not, I guess, would depend on how
many sheets and how often you need to do this.

If you decide to give it a try, you can adjust the Row() argument to
compensate for which row you're entering your formula on, in order to have
it return the correct sheet number.
For example, if you're going to start on row 4, and you need sheet1, then:

="='c:\[payroll.xls]"&ROW()-3&"'!c5"
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I've got a doozy here. I want to auto fill a reference to another
workbook and have the sheet numbers change consecutively.

eg. the cells in a given column should end up like this:

='C:\[payroll.xls]1'!$C$5
='C:\[payroll.xls]2'!$C$5
='C:\[payroll.xls]3'!$C$5

How do I get auto fill to make those sheet reference numbers change?
Right now when I drag the reference down it auto fills the same sheet
number.
I tried it as you would with cell references: I manually changed the
numbers in the first few columns, and then selected all and tried to
auto fill, but it still didn't work. Is this possible or are sheet
references just static?

thanks!
Moosey

 

[Moose]

Thank you RD!! It works beautifully. The real worksheets I need this fo
have like 6 or 7 columns each all referencing different wbooks i
multiple locations with 31 sheets each. So this is going to save m
literally -hours- of going through and changing references each month.
Thank you, thank you, thank you!!

-Moose

 

[Ragdyer]

You're very welcome.

Thanks for the feed back !