Automatic Number Increase

[G_off]

I have the Formul

=IF('[Power Forward 2013.xlsx]1'!$D$1>"",'[Power Forwar
2013.xlsx]1'!$D$1,""

I would like to increase the number 1 to 2.3.4...and so o

the next line should look lik

=IF('[Power Forward 2013.xlsx]2'!$D$1>"",'[Power Forwar
2013.xlsx]2'!$D$1,""

each number is a different tab in the Power Forward Excel File. An
ideas on how to do this without copy - paste and manually changing

I appreciate the hel

 

[GS]

Try...

=IF(LEN('[Power Forward 2013.xlsx]1'!$D1),'[Power Forward
2013.xlsx]1'!$D1,"")

...where the row refs are now relative. Copy down to have it work for
other rows.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[G_off]

;1603180']Try...

=IF(LEN('[Power Forward 2013.xlsx]1'!$D1),'[Power Forward
2013.xlsx]1'!$D1,"")

...where the row refs are now relative. Copy down to have it work for
other rows.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Garry,

I need the Rows to remain the same
I have a master spreadsheet. - each line refers to another spreadshee
('[Power Forward 2013.xlsx]. I am good thus far. the problem is th
following number 1' - the 1' refers to a tab in the power forward 201
spreadsheet. I have 30 tabs labeled 1 2 3 4 5..... exact replicas o
one another. what I would like to do is make that 1' automaticall
change to 2' 3 4 5.
any thoughts

 

[GS]

;1603180']Try...

=IF(LEN('[Power Forward 2013.xlsx]1'!$D1),'[Power Forward
2013.xlsx]1'!$D1,"")

...where the row refs are now relative. Copy down to have it work for
other rows.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Garry,

I need the Rows to remain the same
I have a master spreadsheet. - each line refers to another spreadsheet
('[Power Forward 2013.xlsx]. I am good thus far. the problem is the
following number 1' - the 1' refers to a tab in the power forward 2013
spreadsheet. I have 30 tabs labeled 1 2 3 4 5..... exact replicas of
one another. what I would like to do is make that 1' automatically
change to 2' 3 4 5.
any thoughts?

Ok, I get it! The numbers you want to change are worksheet names, NOT
row positions. In this case you will need to use VBA to construct the
formula and insert it where you want it put. You will have to specify
the start row/col where the first formula goes (on which sheet) so VBA
knows where to put them. This assumes the sheet getting the formulas is
not one of the sheets you're pulling data from.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Gord Dibben]

To increment the sheet numbers 1 to 30 you must use INDIRECT function

example....

=IF(INDIRECT("[Book6.xls]" & ROW(1:1) & "!D1")<>"","yes","no")

Copied down to row 30


Gord