| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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.. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sheetname formula | Excel Worksheet Functions | |||
| Using a cell to reference sheetname in vlookup function | Excel Worksheet Functions | |||
| Formula that returns the sheetname | Excel Discussion (Misc queries) | |||
| How do I have a formula lookup a sheetname? | Excel Worksheet Functions | |||
| Replace a sheetname with a cell reference to link to another work. | Excel Worksheet Functions | |||
Linear Mode
