Reference Sheetname in Formula
 

at present this formula is sucessful:

in cell G7 I have:
=INDEX('[012007 Cash.xls]Day4'!$T$150:$T$354,$C7+1-150,1)

In Cell G4 I have text Day4

How can I alter my formula to read the G4 value in place of the hard-coded
Value?
something like ,,,,

=INDEX('[012007 Cash.xls] Indirect($G$4) '!$T$150:$T$354,$C7+1-150,1)
??
Any help appreciated..

Reference Sheetname in Formula
 

Try =INDEX(INDIRECT("'[012007 Cash.xls]"&$G$4&"'!$T$150:$T$354",$C7+1-150,1)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...

at present this formula is sucessful:

in cell G7 I have:
=INDEX('[012007 Cash.xls]Day4'!$T$150:$T$354,$C7+1-150,1)

In Cell G4 I have text Day4

How can I alter my formula to read the G4 value in place of the hard-coded
Value?
something like ,,,,

=INDEX('[012007 Cash.xls] Indirect($G$4) '!$T$150:$T$354,$C7+1-150,1)
??
Any help appreciated..

Reference Sheetname in Formula
 

Thanks Bernard -- I've since changed my formula -- but still tried to use
your logic all the same;

I now have:

=INDEX(INDIRECT(" '[012007 Cash.xls]"&$B8&" '!$T$150:$T$354",C$7+1-150,1)

But it is not working for me..
The message reads: You have entered too many arguments for this formula...



"Bernard Liengme" wrote:

Try =INDEX(INDIRECT("'[012007 Cash.xls]"&$G$4&"'!$T$150:$T$354",$C7+1-150,1)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...

at present this formula is sucessful:

in cell G7 I have:
=INDEX('[012007 Cash.xls]Day4'!$T$150:$T$354,$C7+1-150,1)

In Cell G4 I have text Day4

How can I alter my formula to read the G4 value in place of the hard-coded
Value?
something like ,,,,

=INDEX('[012007 Cash.xls] Indirect($G$4) '!$T$150:$T$354,$C7+1-150,1)
??
Any help appreciated..

Reference Sheetname in Formula
 

Maybe...

=INDEX(INDIRECT("'[012007 Cash.xls]"&$B8&"'!$T$150:$T$354"),C$7+1-150,1)

And watch your spaces, too.

Keep in mind that =indirect() won't work when the sending workbook is closed.

JMay wrote:

Thanks Bernard -- I've since changed my formula -- but still tried to use
your logic all the same;

I now have:

=INDEX(INDIRECT(" '[012007 Cash.xls]"&$B8&" '!$T$150:$T$354",C$7+1-150,1)

But it is not working for me..
The message reads: You have entered too many arguments for this formula...

"Bernard Liengme" wrote:

Try =INDEX(INDIRECT("'[012007 Cash.xls]"&$G$4&"'!$T$150:$T$354",$C7+1-150,1)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...

at present this formula is sucessful:

in cell G7 I have:
=INDEX('[012007 Cash.xls]Day4'!$T$150:$T$354,$C7+1-150,1)

In Cell G4 I have text Day4

How can I alter my formula to read the G4 value in place of the hard-coded
Value?
something like ,,,,

=INDEX('[012007 Cash.xls] Indirect($G$4) '!$T$150:$T$354,$C7+1-150,1)
??
Any help appreciated..







--

Dave Peterson

Reference Sheetname in Formula
 

I missed closing parenthesis
INDIRECT need to be =INDIRECT( a_string_that_looks_like_ a reference)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...
Thanks Bernard -- I've since changed my formula -- but still tried to use
your logic all the same;

I now have:

=INDEX(INDIRECT(" '[012007 Cash.xls]"&$B8&" '!$T$150:$T$354",C$7+1-150,1)

But it is not working for me..
The message reads: You have entered too many arguments for this
formula...



"Bernard Liengme" wrote:

Try =INDEX(INDIRECT("'[012007
Cash.xls]"&$G$4&"'!$T$150:$T$354",$C7+1-150,1)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...

at present this formula is sucessful:

in cell G7 I have:
=INDEX('[012007 Cash.xls]Day4'!$T$150:$T$354,$C7+1-150,1)

In Cell G4 I have text Day4

How can I alter my formula to read the G4 value in place of the
hard-coded
Value?
something like ,,,,

=INDEX('[012007 Cash.xls] Indirect($G$4)
'!$T$150:$T$354,$C7+1-150,1)
??
Any help appreciated..

Reference Sheetname in Formula
 

"I missed closing parenthesis" -- No prob, I caught that later on..
but the syntax of these type references always confuses me.

Actually, I have to copy anyones formula and then paste it as text,
then increase the font size to 20 so I can distinquish the " from the '
very annoying. I still don't understand the reasoning as to where and
why the breaks take place.. using Dave's example

=INDEX(INDIRECT("'[012007 Cash.xls]"&$B8&"'!$T$150:$T$354"),C$7+1-150,1)

why is the ' just after the first " and before the ! ? Is there a
reasonable
explanation?

Thanks for your input..


Jim

"Bernard Liengme" wrote:

I missed closing parenthesis
INDIRECT need to be =INDIRECT( a_string_that_looks_like_ a reference)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...
Thanks Bernard -- I've since changed my formula -- but still tried to use
your logic all the same;

I now have:

=INDEX(INDIRECT(" '[012007 Cash.xls]"&$B8&" '!$T$150:$T$354",C$7+1-150,1)

But it is not working for me..
The message reads: You have entered too many arguments for this
formula...



"Bernard Liengme" wrote:

Try =INDEX(INDIRECT("'[012007
Cash.xls]"&$G$4&"'!$T$150:$T$354",$C7+1-150,1)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...

at present this formula is sucessful:

in cell G7 I have:
=INDEX('[012007 Cash.xls]Day4'!$T$150:$T$354,$C7+1-150,1)

In Cell G4 I have text Day4

How can I alter my formula to read the G4 value in place of the
hard-coded
Value?
something like ,,,,

=INDEX('[012007 Cash.xls] Indirect($G$4)
'!$T$150:$T$354,$C7+1-150,1)
??
Any help appreciated..

Reference Sheetname in Formula
 

I hate building this kind of reference from memory.

I'll open up the other workbook and then create a formula that uses this other
workbook (one way: edit|copy, edit paste special|paste link):

It would look something like:
='[book 3.xls]Sheet1'!$A$1

Then I match the syntax that I see in that formula.

(I don't need no stinkin' explanation if excel does the real work for me. <vbg)

JMay wrote:

"I missed closing parenthesis" -- No prob, I caught that later on..
but the syntax of these type references always confuses me.

Actually, I have to copy anyones formula and then paste it as text,
then increase the font size to 20 so I can distinquish the " from the '
very annoying. I still don't understand the reasoning as to where and
why the breaks take place.. using Dave's example

=INDEX(INDIRECT("'[012007 Cash.xls]"&$B8&"'!$T$150:$T$354"),C$7+1-150,1)

why is the ' just after the first " and before the ! ? Is there a
reasonable
explanation?

Thanks for your input..

Jim

"Bernard Liengme" wrote:

I missed closing parenthesis
INDIRECT need to be =INDIRECT( a_string_that_looks_like_ a reference)
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...
Thanks Bernard -- I've since changed my formula -- but still tried to use
your logic all the same;

I now have:

=INDEX(INDIRECT(" '[012007 Cash.xls]"&$B8&" '!$T$150:$T$354",C$7+1-150,1)

But it is not working for me..
The message reads: You have entered too many arguments for this
formula...



"Bernard Liengme" wrote:

Try =INDEX(INDIRECT("'[012007
Cash.xls]"&$G$4&"'!$T$150:$T$354",$C7+1-150,1)


--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JMay" wrote in message
...

at present this formula is sucessful:

in cell G7 I have:
=INDEX('[012007 Cash.xls]Day4'!$T$150:$T$354,$C7+1-150,1)

In Cell G4 I have text Day4

How can I alter my formula to read the G4 value in place of the
hard-coded
Value?
something like ,,,,

=INDEX('[012007 Cash.xls] Indirect($G$4)
'!$T$150:$T$354,$C7+1-150,1)
??
Any help appreciated..










--

Dave Peterson