I have the Formula

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

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

the next line should look like

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

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

I appreciate the help

Hi,

Have a look for the =INDIRECT() function.
It's one way of doing this, although it would require you to have a list of 1, 2, 3, 4 etc. somewhere in the workbook you can refer to.

There is possibly a VBA way around this too, but it's by no means my strong point so I won't even bog you down with a lame attempt at it.

Without sight of your workbook, I cannot advise further than that.

S.

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?

on 6/28/2012, G_off supposed :
'GS[_2_ Wrote:
;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

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

On Thu, 28 Jun 2012 17:33:30 +0000, G_off
wrote:

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?