K
karl gauss
Is there a "sheet" function or something like it which returns the
current sheet number?
current sheet number?
F
Frank Kabel
Hi
you'll have to use VBA for this. e.g. try
public function get_sheet_number(rng as range)
get_sheet_number = rng.parent.index
end function
and use the following formula on your sheet:
=get_sheet_number(A1)
you'll have to use VBA for this. e.g. try
public function get_sheet_number(rng as range)
get_sheet_number = rng.parent.index
end function
and use the following formula on your sheet:
=get_sheet_number(A1)
J
Jack Schitt
You can get the current worksheet name by
=MID(CELL("filename",A1),1+FIND("]",CELL("filename",A1)),255)
This only works if the file has first been saved, otherwise it returns
#VALUE!
You might want to substitute for A1 the cell reference of the calling
formula, to protect against row or column deletion having an effect.
To get the worksheet index number is not quite so simple, I think. Perhaps
one way is to include the worksheet index number within the worksheet name,
at a determined position within the name string, and then use MID() on the
above to get the sheet number.
Another way, rather unstable is:
Define a named formula
AllSheets
refers to
=GET.WORKBOOK(1+0*NOW())
and define another named formula
ThisSheet
refers to
=GET.CELL(32+0*NOW(),INDIRECT("rc",FALSE))
Then
=MATCH(ThisSheet,AllSheets,0)
should return the index number of the worksheet.
Don't try copying the formula to another sheet using an early version of
Excel, or it will hang.
=MID(CELL("filename",A1),1+FIND("]",CELL("filename",A1)),255)
This only works if the file has first been saved, otherwise it returns
#VALUE!
You might want to substitute for A1 the cell reference of the calling
formula, to protect against row or column deletion having an effect.
To get the worksheet index number is not quite so simple, I think. Perhaps
one way is to include the worksheet index number within the worksheet name,
at a determined position within the name string, and then use MID() on the
above to get the sheet number.
Another way, rather unstable is:
Define a named formula
AllSheets
refers to
=GET.WORKBOOK(1+0*NOW())
and define another named formula
ThisSheet
refers to
=GET.CELL(32+0*NOW(),INDIRECT("rc",FALSE))
Then
=MATCH(ThisSheet,AllSheets,0)
should return the index number of the worksheet.
Don't try copying the formula to another sheet using an early version of
Excel, or it will hang.
K
karl gauss
Wow, I can't manage this.
I'm trying to migrate from Quattro Pro to Excel.
Here is what I'm trying to do, and here is how easily I do it in
Quattro Pro:
I have a sequence of numbers in a column in the first sheet.
On the second sheet, I wish to call the first number from the first
sheet.
On the third sheet, I wish to call the second number from the first
sheet.
On the fourth sheet, I wish to call the third number from the first
sheet.
And so on.
This command posted on each sheet from the second on down does it in
Quattro Pro:
@INDEX($A:$A$5..$A:$A$37,0,@CELL("Sheet",A2..A2)-2)
where after @index are block, column-1 and row. The row is gained
merely by recognizing a simple bijection between row of entry on the
first sheet and the sheet numbers themselves. This is built into the
@CELL command parameters.
I can see from reading the help file in Excel all I need regarding the
INDEX and CELL commands except how to gain the sheet number. Notice
that in Quattro Pro, all that is needed is "sheet".
That last bit is what troubles me - how to gain the sheet number in
Excel.
Maybe I'm overlooking something really trivial.
Any ideas from you experts pls?
Thx!!
I'm trying to migrate from Quattro Pro to Excel.
Here is what I'm trying to do, and here is how easily I do it in
Quattro Pro:
I have a sequence of numbers in a column in the first sheet.
On the second sheet, I wish to call the first number from the first
sheet.
On the third sheet, I wish to call the second number from the first
sheet.
On the fourth sheet, I wish to call the third number from the first
sheet.
And so on.
This command posted on each sheet from the second on down does it in
Quattro Pro:
@INDEX($A:$A$5..$A:$A$37,0,@CELL("Sheet",A2..A2)-2)
where after @index are block, column-1 and row. The row is gained
merely by recognizing a simple bijection between row of entry on the
first sheet and the sheet numbers themselves. This is built into the
@CELL command parameters.
I can see from reading the help file in Excel all I need regarding the
INDEX and CELL commands except how to gain the sheet number. Notice
that in Quattro Pro, all that is needed is "sheet".
That last bit is what troubles me - how to gain the sheet number in
Excel.
Maybe I'm overlooking something really trivial.
Any ideas from you experts pls?
Thx!!
J
Jack Schitt
I have a little problem with this.
If I move the sheets around after entering the formula, it does not update
the sheet index. Even if I make it application.volatile, then
control+shift+F9 to do a full recalculation.
It DOES recalculate it if I F2 the cell to go into edit mode, then re-enter,
but that is the only way I can get it to recalculate (Excel 2002 SP3)
If I move the sheets around after entering the formula, it does not update
the sheet index. Even if I make it application.volatile, then
control+shift+F9 to do a full recalculation.
It DOES recalculate it if I F2 the cell to go into edit mode, then re-enter,
but that is the only way I can get it to recalculate (Excel 2002 SP3)
F
Frank Kabel
Hi
probably not really a way around this as there's AFAIK no event which
is triggered by just moving sheets around
)
probably not really a way around this as there's AFAIK no event which
is triggered by just moving sheets around
)
J
Jack Schitt
Forget that, it is working now. Don't know why it wasn't working
temporarily
temporarily
J
Jack Schitt
Frank's solution seems to be the simplest.
I created several sheets, called Sheet1, Sheet2 etc
In Sheet1 I created a column of data and named it Sheet1!MyRange
Then in Sheet2 I entered
=INDEX(Sheet1!MyRange,get_sheet_number(B1)-get_sheet_number(Sheet1!MyRange))
and it returned the first element of MyRange.
Copied the formula to Sheet3 and it returned the second element.
etc
By the way, you have my sympathies. When I migrated to Excel from Lotus I
missed the 3-dimensional aspects.
--
Return email address is not as DEEP as it appears
I created several sheets, called Sheet1, Sheet2 etc
In Sheet1 I created a column of data and named it Sheet1!MyRange
Then in Sheet2 I entered
=INDEX(Sheet1!MyRange,get_sheet_number(B1)-get_sheet_number(Sheet1!MyRange))
and it returned the first element of MyRange.
Copied the formula to Sheet3 and it returned the second element.
etc
By the way, you have my sympathies. When I migrated to Excel from Lotus I
missed the 3-dimensional aspects.
--
Return email address is not as DEEP as it appears
karl gauss said:
M
Myrna Larson
Application.Volatile means that whenever Excel determines that a recalc is
needed, it should also recalculate this formula, even though it would not
normally do so (because none of the arguments changed). Changing the position
of a sheet doesn't trigger a recalculation, so Application.Volatile won't
help.
And, as others have mentioned, moving a sheet doesn't trigger an event that
you can tie into.
I was able to get it to recalculate by modifying the function as below, and
writing the formula as =Get_Sheet_Number(A1,NOW())
Function Get_Sheet_Number(sText As String, x As Double) As Long
Get_Sheet_Number = Worksheets(sText).Index
End Function
As long as calcuation is set to automatic, this recalculates when I drag the
sheet tab to a new position.
needed, it should also recalculate this formula, even though it would not
normally do so (because none of the arguments changed). Changing the position
of a sheet doesn't trigger a recalculation, so Application.Volatile won't
help.
And, as others have mentioned, moving a sheet doesn't trigger an event that
you can tie into.
I was able to get it to recalculate by modifying the function as below, and
writing the formula as =Get_Sheet_Number(A1,NOW())
Function Get_Sheet_Number(sText As String, x As Double) As Long
Get_Sheet_Number = Worksheets(sText).Index
End Function
As long as calcuation is set to automatic, this recalculates when I drag the
sheet tab to a new position.
F
Frank Kabel
Hi Myrna
nice
nice
H
Harlan Grove
....karl gauss said:
QP's SHEET function won't be the only bit of true 3D functionality you'll
miss.
If the worksheet names aren't expected to change, you'd be MUCH better off
in Excel creating a table in which the first column were the worksheet names
and the second column contained the numbers you want on those worksheets,
then use VLOOKUP to access the numbers using the worksheet name.
As for the worksheet name, use the MID(CELL("Filename",..),..) formula
already suggested. However, if you define a workbook-level name like wsn
referring to
=MID(CELL("Filename",INDIRECT("A1")),
FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
you'd be able to use wsn on any worksheet in the workbook, and it'd return
the name of its own worksheet. Also, using INDIRECT("A1") means it'll be
unaffected by row/column insertions/deletions and cut-and-paste.
The sad fact is that Excel's CELL function is stuck at the functionality of
Lotus 123 Release 2.01 (circa 1986). Since it hasn't yet been augmented for
added 3D properties which were introduced in 123 Release 3.0 in 1989 (15
years ago), it'd be unwise in the extreme to hope that Microsoft will ever
add them. You'll find that thinking in 2D will make Excel development
easier.