Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Changing sheet reference to cell reference

On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a
different sheet to the one I'm working on but in the same workbook.

Is it possible to change the formula so that it refers to a cell on the
current sheet that has a reference to the other sheet?

If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the
current sheet, would it be possible to get a formula that works =K1!$L$40

If I try this at the minute, I get a dialog box to update values. How should
I be writing this formula?

I'm using Excel 2000 and would prefer not to use VBA (some of the computers
this will have to be viewed on have macros disabled).

Many thanks for any help
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Changing sheet reference to cell reference

Use the INDIRECT() function
--
Gary''s Student - gsnu200750


"TeeJay" wrote:

On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a
different sheet to the one I'm working on but in the same workbook.

Is it possible to change the formula so that it refers to a cell on the
current sheet that has a reference to the other sheet?

If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the
current sheet, would it be possible to get a formula that works =K1!$L$40

If I try this at the minute, I get a dialog box to update values. How should
I be writing this formula?

I'm using Excel 2000 and would prefer not to use VBA (some of the computers
this will have to be viewed on have macros disabled).

Many thanks for any help

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default Changing sheet reference to cell reference

check out the indirect() function in help.
If I am reading your input right
=indirect(K1&"!$L$40")
but put only TeeJay in K1

depending on your sheet names you may have to add the ' to the front and
back on the sheet name
"TeeJay" wrote:

On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a
different sheet to the one I'm working on but in the same workbook.

Is it possible to change the formula so that it refers to a cell on the
current sheet that has a reference to the other sheet?

If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the
current sheet, would it be possible to get a formula that works =K1!$L$40

If I try this at the minute, I get a dialog box to update values. How should
I be writing this formula?

I'm using Excel 2000 and would prefer not to use VBA (some of the computers
this will have to be viewed on have macros disabled).

Many thanks for any help

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 6
Default Changing sheet reference to cell reference

Hi bj,

thanks for the response.
However, this keeps giving me a =REF! error when I enter it.

Stepping through the function helps shows that the interim step right before
the =REF! value has the equation =INDIRECT( "TeeJay!$L$40") (the italised
writing is making it hard to determine if those are quotation marks or two '
marks together)
The ' marks to reference the TeeJay sheet seem to disappear on the step
before.

I have looked at Excel help and to be honest I'm not finding it helpful for
INDIRECT formulas.

One thing I realise now I forgot to mention was that cell A1 on the TeeJay
worksheet (the one that cell K1 on the current sheet is referencing) has this
formula in:
=MID(CELL("filename",A1),(FIND("]",CELL("filename",A1)))+1,50)

I don't think this is affecting the INDIRECT formula but I haven't used the
MID command before picking it up elsewhere in the discussion group

I can tell that I'm not the only person to have had problems with INDIRECT
formula but I haven't found yet a reply in these groups that seems to address
this particular issue.

Thanks in advance to any help
"bj" wrote:

check out the indirect() function in help.
If I am reading your input right
=indirect(K1&"!$L$40")
but put only TeeJay in K1

depending on your sheet names you may have to add the ' to the front and
back on the sheet name
"TeeJay" wrote:

On one sheet I have the formula ='TeeJay'!$L$40 where TeeJay is the name of a
different sheet to the one I'm working on but in the same workbook.

Is it possible to change the formula so that it refers to a cell on the
current sheet that has a reference to the other sheet?

If I had ='TeeJay'!A1 (where A1 has the name of the sheet) in cell K1 on the
current sheet, would it be possible to get a formula that works =K1!$L$40

If I try this at the minute, I get a dialog box to update values. How should
I be writing this formula?

I'm using Excel 2000 and would prefer not to use VBA (some of the computers
this will have to be viewed on have macros disabled).

Many thanks for any help

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting a sheet name reference within a cell reference??? Broyston Excel Discussion (Misc queries) 9 July 8th 08 08:35 PM
stop insert from changing cell reference Peaceful Wisdom Excel Worksheet Functions 1 January 9th 06 07:32 PM
Problem with formulas changing cell reference janicesweet Excel Discussion (Misc queries) 1 August 2nd 05 06:23 PM
A cell reference in a formula changing knemitz Excel Worksheet Functions 1 February 28th 05 07:10 PM
HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE anantth Excel Discussion (Misc queries) 4 February 6th 05 01:25 PM


All times are GMT +1. The time now is 10:37 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"