Copying formula

[MikeNZ]

I need to copy the below formula and have the cell value stay constant but have the sheet number increase by 7. Is there a way to do this automatically or do I have to change it manually?

='S \11\October\[09.xlsm]Figures'!$D$6

Would copy to become:

='S \11\October\[16.xlsm]Figures'!$D$6

[6StringJazzer]

The usual way to do this is by using the INDIRECT function and using a formula based on row number to increment by 7. However, be aware that references to external workbooks as this appears to be, rather than worksheets within the same workbook, requires each external workbook to be open, which may not be convenient for you.

For example, if your formula starts in row 1, use this

=INDIRECT("'S \11\October\[" & TEXT(ROW()*7+2,"00") & ".xlsm]Figures'!$D$6")

If that is tantalizing close but you don't want the workbooks to be open you have two choices (that I can think of):

1. Use a handy macro called INDIRECT.EXT
2. Write a macro that writes the formulas for you with the correct sheet reference

[GeneralDisarray]

hm.... maybe try building the string in one column for the workbook part:

="[" & counta($A2:$A$2)*7+9 &".xlsm]Figures'!$D$6"

or maybe

="[" & (row()-1)*7+9 &".xlsm]Figures'!$D$6"

that would give you the increment in the workbook name (assuming you were pasting the 09.xlms file call in the A1 cell and the others below it...but w/o a workbook i have to assume some things

Then, use indirect to finish it off (say in the B column??)

='S\11\october\INDIRECT(A2)

pasted down?


EDIT: dang...too slow... ah ha...indirect the whole dang thing nice didn't know that "TEXT()" trick there.
Thanks 6SJ....your marklars are wise and true

[MikeNZ]

1. Use a handy macro called INDIRECT.EXT

I can't find this in the excel function list. Do I need to download an add-on?

[MarvinP]

Hi MikeNZ,

6StringJazzer gave you a link in his #1 above that you need to click on to get to what he is suggesting.

Or click on http://xcell05.free.fr/morefunc/engl...direct.ext.htm to go to the same place.

[MikeNZ]

I clicked on that, read the info and inserted the formula but I get a #NAME error. In the list of functions I have in excel there is no indirect.ext so I presuming I need to download from the morefunc site?