Reference Sheetname in Formula

G

Guest

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..
 
B

Bernard Liengme

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

Guest

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 said:
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 said:
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..
Click to expand...
Click to expand...
 
D

Dave Peterson

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.
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 said:
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 said:
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..
Click to expand...
Click to expand...
Click to expand...
 
B

Bernard Liengme

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 said:
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 said:
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 said:
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..
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

"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 said:
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 said:
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 said:
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


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..
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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>)
"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 said:
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 said:
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...



:

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


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..
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula in a cell depending on the value of another cell 1
Cell reference in formula as range 4
SumIFS Cell 1
Formula that sums/counts across worksheets with variable reference 1
Lkup formula is returning last value in result column, not lkup va 3
Sheet name determined by cell value in INDEX/MATCH 2
trying to use Indirect function 5
Variable column reference in formula 2

Top