Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help me make a function dependent on current sheet?!?
Hi,
I've got a workbook (time log) whose base sheets are quite similar. I need a function that references the current worksheet name and/or index, similar to how row() and column() work. [See below for why] With the code below, everything is fine for the current (active) worksheet. But if I put either function in cells on multiple sheets, ALL sheets get the value of the currently highlighted (active) sheet! This will not work. How do I reference the sheet that contains the cell being calculated??? Function MySheetIndex() As Integer MySheetIndex = ActiveSheet.Index End Function Function MySheetName(Optional iIndex As Integer) As String If IsMissing(iIndex) Then MySheetName = ActiveSheet.Name Else MySheetName = ActiveWorkbook.Worksheets(iIndex).Name End If End Function Thanks MUCH! Pete PS - More explanation: The main variation between sheets is the columns of dates, etc. I want to make it possible to begin each year by copying and pasting a baseline sheet across all sheets. To do this, and to simplify a lot of other stuff, the worksheets are named for each period (e.g. "Jan 1", "Jan 16", "Feb 1" etc). Given the function above, I can generate date references, etc that are based on the current sheet name :) --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help me make a function dependent on current sheet?!?
I think you should always pass a cell on the sheet whose name you want
returned: Function ShtName(Rg As Range) As String ShtName = Rg.Parent.Name End Function In a cell: =ShtName(A1) -- Jim Rech Excel MVP |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help me make a function dependent on current sheet?!?
Yahoo!!!!!!
I think you must have a lot of experience :D ... That worked great, and it makes sense. I had to adjust our design a bit, but it is actually better this way... Thanks MUCH, Pete --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help me make a function dependent on current sheet?!?
I think you must have a lot of experience :D ...
You saying I'm old? Well,... actually you're right<g. -- Jim Rech Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function To Select the Hidden Rows & Columns in the Current Sheet | Excel Discussion (Misc queries) | |||
how to adjust formula dependent on current month | Excel Discussion (Misc queries) | |||
How do I make other cells dependent on my drop down list? | Excel Discussion (Misc queries) | |||
How can I make a cell a certain color dependent on number value? | Excel Discussion (Misc queries) | |||
how to make macro not dependent on datafile which has date as its part. | Excel Programming |