Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
in VBA sheet is sheet8
how can this appear in =index('sheetname'! c,(row()-1)*8+2) macro is currently selecting sheets name as appears on the tab but this changes every day. I need to use vba identity within this formula for it to work. James |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
If 'sheetname' is renamed, XL will automatically change the reference in
the formula. In the stored formula, XL keeps track of the Code Name (e.g., Sheet8), but displays the sheet's Name (e.g., 'sheetname') in the formula bar. In article , JBW wrote: in VBA sheet is sheet8 how can this appear in =index('sheetname'! c,(row()-1)*8+2) macro is currently selecting sheets name as appears on the tab but this changes every day. I need to use vba identity within this formula for it to work. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
So your saying that although my macro names the sheet with a date, tomorrow
when it changes the name (as the date is different) the formula will automaticlly change the name too as it uses the reference ? if so hurrah is ther still not a way of saying sheet8 within the formula though? "JE McGimpsey" wrote: If 'sheetname' is renamed, XL will automatically change the reference in the formula. In the stored formula, XL keeps track of the Code Name (e.g., Sheet8), but displays the sheet's Name (e.g., 'sheetname') in the formula bar. In article , JBW wrote: in VBA sheet is sheet8 how can this appear in =index('sheetname'! c,(row()-1)*8+2) macro is currently selecting sheets name as appears on the tab but this changes every day. I need to use vba identity within this formula for it to work. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
If you don't want to take my word for it, you could actually *try* it...
BTW - you *ARE* "saying sheet8" within the formula. Formulas aren't stored as the text you enter, they're tokenized. When you enter =mysheetname!A1 XL tokenizes "mysheetname" to refer to the sheet's code name (Sheet8). Then when you select the cell, whatever the value of Sheet8's Name property is will be used in the sheet reference when expanding the tokenized formula to the formula bar or cell. In article , JBW wrote: So your saying that although my macro names the sheet with a date, tomorrow when it changes the name (as the date is different) the formula will automaticlly change the name too as it uses the reference ? if so hurrah is ther still not a way of saying sheet8 within the formula though? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
I'll try it tomorrow when the date and hence the name chamges, I'm sure I
tried it before and excel went looking for the name as it had changed "JE McGimpsey" wrote: If you don't want to take my word for it, you could actually *try* it... BTW - you *ARE* "saying sheet8" within the formula. Formulas aren't stored as the text you enter, they're tokenized. When you enter =mysheetname!A1 XL tokenizes "mysheetname" to refer to the sheet's code name (Sheet8). Then when you select the cell, whatever the value of Sheet8's Name property is will be used in the sheet reference when expanding the tokenized formula to the formula bar or cell. In article , JBW wrote: So your saying that although my macro names the sheet with a date, tomorrow when it changes the name (as the date is different) the formula will automaticlly change the name too as it uses the reference ? if so hurrah is ther still not a way of saying sheet8 within the formula though? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
You know you don't have to wait for tomorrow if you are using the computers
internal clock, just change the date to tomorrow and open up a copy of this file to test it. Then change back the date -- Regards, Peo Sjoblom "JBW" wrote in message ... I'll try it tomorrow when the date and hence the name chamges, I'm sure I tried it before and excel went looking for the name as it had changed "JE McGimpsey" wrote: If you don't want to take my word for it, you could actually *try* it... BTW - you *ARE* "saying sheet8" within the formula. Formulas aren't stored as the text you enter, they're tokenized. When you enter =mysheetname!A1 XL tokenizes "mysheetname" to refer to the sheet's code name (Sheet8). Then when you select the cell, whatever the value of Sheet8's Name property is will be used in the sheet reference when expanding the tokenized formula to the formula bar or cell. In article , JBW wrote: So your saying that although my macro names the sheet with a date, tomorrow when it changes the name (as the date is different) the formula will automaticlly change the name too as it uses the reference ? if so hurrah is ther still not a way of saying sheet8 within the formula though? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
Or you could take 30 seconds to open a new workbook, enter
=Sheet2!A1 in Sheet1, cell A1. Then, in the VBE's immediate window, enter Sheet2.Name = "newname" Exit the VBE and verify that Sheet1!A1 now reads =newname!A1 In article , JBW wrote: I'll try it tomorrow when the date and hence the name chamges, I'm sure I tried it before and excel went looking for the name as it had changed |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
I knew I'd tried this, as soon as you change the sheet name the system opens
an explorer window and asks you to locate the new file. code is: Range("A1").Select ActiveCell.FormulaR1C1 = _ "=INDEX('BB SCM 22 OCTOBER 2007'!c, (ROW()-1)*8+2)" date is only thing that changes. So.... Is there a sheet reference I can use in place of the name to ensure the correct sheet is always used? James "JE McGimpsey" wrote: Or you could take 30 seconds to open a new workbook, enter =Sheet2!A1 in Sheet1, cell A1. Then, in the VBE's immediate window, enter Sheet2.Name = "newname" Exit the VBE and verify that Sheet1!A1 now reads =newname!A1 In article , JBW wrote: I'll try it tomorrow when the date and hence the name chamges, I'm sure I tried it before and excel went looking for the name as it had changed |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
OK - I get it now - you're not asking about the *formula* changing when
the sheet name changes (which it does), you're asking how to reference the sheet name within the macro. Just don't hard-code the sheet name: Range("A1").FormulaR1C1 = "=INDEX('" & Page8.Name & _ "'!C, (ROW()-1)*8+2)" Note that you almost *never* need to select anything. Working with Range objects directly makes your code faster, usually smaller, and IMO easier to maintain. In article , JBW wrote: I knew I'd tried this, as soon as you change the sheet name the system opens an explorer window and asks you to locate the new file. code is: Range("A1").Select ActiveCell.FormulaR1C1 = _ "=INDEX('BB SCM 22 OCTOBER 2007'!c, (ROW()-1)*8+2)" date is only thing that changes. So.... Is there a sheet reference I can use in place of the name to ensure the correct sheet is always used? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
I substituted the 'name' with the " & Page8.Name & _" and it wouldn't run at
all? I have now got around problem by changing sheet name to BB SCM and then adding date to name at the end of the macro thanks for your assistance James "JE McGimpsey" wrote: OK - I get it now - you're not asking about the *formula* changing when the sheet name changes (which it does), you're asking how to reference the sheet name within the macro. Just don't hard-code the sheet name: Range("A1").FormulaR1C1 = "=INDEX('" & Page8.Name & _ "'!C, (ROW()-1)*8+2)" Note that you almost *never* need to select anything. Working with Range objects directly makes your code faster, usually smaller, and IMO easier to maintain. In article , JBW wrote: I knew I'd tried this, as soon as you change the sheet name the system opens an explorer window and asks you to locate the new file. code is: Range("A1").Select ActiveCell.FormulaR1C1 = _ "=INDEX('BB SCM 22 OCTOBER 2007'!c, (ROW()-1)*8+2)" date is only thing that changes. So.... Is there a sheet reference I can use in place of the name to ensure the correct sheet is always used? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name needed for formula
" _" (space-underscore) is a line continuation marker in the VBE. It
indicates that what follows on the next line is a continuation of the current line. See "Writing Visual Basic Statements" in XL Help. You don't say what "wouldn't run at all" means (did you get a compile error?, a run-time error? a crash?), but I assume you substituted Page8 with your worksheet's Code Name. In article , JBW wrote: I substituted the 'name' with the " & Page8.Name & _" and it wouldn't run at all? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula needed to bring values to another sheet | Excel Worksheet Functions | |||
carry over data to other sheet - help needed | Excel Discussion (Misc queries) | |||
Help Creating a points tracking sheet needed | Excel Discussion (Misc queries) | |||
Help needed! - How do I save a filtered sheet? | Excel Discussion (Misc queries) | |||
Time Sheet Calculation Help Needed! | Excel Worksheet Functions |