some kind of meta-way to refer to sheet and file names in Excel?
 

Is there a way to refer to use a variable as the sheet or file name in an
excel formula?

For example, let's say that in columns A and B, I've got the names of a set
of workbooks and sheets that I want to refer to...

Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3

And let's say I want to know what's in cell A1 in each of those sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1

Is there anything I can do short of writing a macro (which is what I'm doing
now) to just get Excel to copy a similar formula into all the rows, where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1

some kind of meta-way to refer to sheet and file names in Excel?
 

The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that other workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

GoBobbyGo wrote:

Is there a way to refer to use a variable as the sheet or file name in an
excel formula?

For example, let's say that in columns A and B, I've got the names of a set
of workbooks and sheets that I want to refer to...

Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3

And let's say I want to know what's in cell A1 in each of those sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1

Is there anything I can do short of writing a macro (which is what I'm doing
now) to just get Excel to copy a similar formula into all the rows, where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1

--

Dave Peterson

some kind of meta-way to refer to sheet and file names in Excel?
 

Last time I looked Harlan's site did not contain the latest version. I
posted that version here http://tinyurl.com/769ya

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" wrote in message
...
The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that other
workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

GoBobbyGo wrote:

Is there a way to refer to use a variable as the sheet or file name in
an
excel formula?

For example, let's say that in columns A and B, I've got the names of a
set
of workbooks and sheets that I want to refer to...

Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3

And let's say I want to know what's in cell A1 in each of those
sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1

Is there anything I can do short of writing a macro (which is what I'm
doing
now) to just get Excel to copy a similar formula into all the rows,
where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1

--

Dave Peterson

some kind of meta-way to refer to sheet and file names in Excel?
 

I looked before I posted (just to double check).

It was updated Oct 5, 2005. I'm not sure if it's the latest, but it's close to
this post's date:

http://groups.google.co.uk/group/mic...3e78ad7891e009

or

http://snipurl.com/iquv


Bob Phillips wrote:

Last time I looked Harlan's site did not contain the latest version. I
posted that version here http://tinyurl.com/769ya

--

HTH

RP
(remove nothere from the email address if mailing direct)

"Dave Peterson" wrote in message
...
The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that other
workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

GoBobbyGo wrote:

Is there a way to refer to use a variable as the sheet or file name in
an
excel formula?

For example, let's say that in columns A and B, I've got the names of a
set
of workbooks and sheets that I want to refer to...

Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3

And let's say I want to know what's in cell A1 in each of those
sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1

Is there anything I can do short of writing a macro (which is what I'm
doing
now) to just get Excel to copy a similar formula into all the rows,
where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1

--

Dave Peterson

--

Dave Peterson

some kind of meta-way to refer to sheet and file names in Exce
 

Thank you both, but I'm utterly unfamiliar with UDFs. The code seems to make
sense, but where do I put it?

"Bob Phillips" wrote:

Last time I looked Harlan's site did not contain the latest version. I
posted that version here http://tinyurl.com/769ya

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" wrote in message
...
The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that other
workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

GoBobbyGo wrote:

Is there a way to refer to use a variable as the sheet or file name in
an
excel formula?

For example, let's say that in columns A and B, I've got the names of a
set
of workbooks and sheets that I want to refer to...

Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3

And let's say I want to know what's in cell A1 in each of those
sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1

Is there anything I can do short of writing a macro (which is what I'm
doing
now) to just get Excel to copy a similar formula into all the rows,
where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1

--

Dave Peterson

some kind of meta-way to refer to sheet and file names in Exce
 

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

GoBobbyGo wrote:

Thank you both, but I'm utterly unfamiliar with UDFs. The code seems to make
sense, but where do I put it?

"Bob Phillips" wrote:

Last time I looked Harlan's site did not contain the latest version. I
posted that version here http://tinyurl.com/769ya

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dave Peterson" wrote in message
...
The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that other
workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a
closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

GoBobbyGo wrote:

Is there a way to refer to use a variable as the sheet or file name in
an
excel formula?

For example, let's say that in columns A and B, I've got the names of a
set
of workbooks and sheets that I want to refer to...

Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3

And let's say I want to know what's in cell A1 in each of those
sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1

Is there anything I can do short of writing a macro (which is what I'm
doing
now) to just get Excel to copy a similar formula into all the rows,
where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1

--

Dave Peterson




--

Dave Peterson

some kind of meta-way to refer to sheet and file names in Exce
 

This must be a stupid question... everyone is refering to pull... where do I
get Harlan function... I tried typing "ftp://members.aol.com/hrlngrv/" in
just every place posibly..

hope someone can help


"Dave Peterson" wrote:

The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that other workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

GoBobbyGo wrote:

Is there a way to refer to use a variable as the sheet or file name in an
excel formula?

For example, let's say that in columns A and B, I've got the names of a set
of workbooks and sheets that I want to refer to...

Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3

And let's say I want to know what's in cell A1 in each of those sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1

Is there anything I can do short of writing a macro (which is what I'm doing
now) to just get Excel to copy a similar formula into all the rows, where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1

--

Dave Peterson

some kind of meta-way to refer to sheet and file names in Exce
 

Did you try pasting that link into the address bar in Microsoft Internet
Explorer?

Gerrit wrote:

This must be a stupid question... everyone is refering to pull... where do I
get Harlan function... I tried typing "ftp://members.aol.com/hrlngrv/" in
just every place posibly..

hope someone can help

"Dave Peterson" wrote:

The function you want to use is =indirect().

The bad news is that =indirect() will return an error if that other workbook
isn't open.

Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed
workbook.

You can find the function at Harlan's FTP site:
ftp://members.aol.com/hrlngrv/
Look for pull.zip

GoBobbyGo wrote:

Is there a way to refer to use a variable as the sheet or file name in an
excel formula?

For example, let's say that in columns A and B, I've got the names of a set
of workbooks and sheets that I want to refer to...

Column A Column B
Book1 Sheet1
Book1 Sheet2
Book1 Sheet3
Book2 Sheet1
Book2 Sheet2
Book2 Sheet3
Book3 Sheet1
Book3 Sheet2
Book3 Sheet3

And let's say I want to know what's in cell A1 in each of those sheets...
So in cell C1, I've got
=[Book1.xls]Sheet1!$A$1

Is there anything I can do short of writing a macro (which is what I'm doing
now) to just get Excel to copy a similar formula into all the rows, where
it's interpreted as
=[{workbook name in column A}]{Sheet name in column B}!$A$1

--

Dave Peterson


--

Dave Peterson