i think a minor question

[jim sturtz]

i have a spreadsheet that i would like to do the following with:
...have a spreadsheet with say 30 pages, each page labeled with a month/dom,
ie May1,May2,...May30. Each page has 30,31,28,29 days as rows and then
several columns days but of course the rows vary depending on the month.

Now lets say I know there will be a june, july, etc sheet. I want a formula
to put on each page that is the sum of column j on each page. In the unique
case I can refer to mayx-cellx by a link like "='May 9'!$M$26", but I would
like a generic statement that doesn't care if it is May9, or Jun9, or xyz,
just the 9th page of the sheet. Is there a dual-refernce to a page within a
sheet so you don't have to use the 'name', but you can reference to the
page_position within the whole spreadsheet. Just like a cell can be named as
a var, but it is also j55.

thanks.

jim

microsoft.public.excel.worksheet.functions

 

[Bernie Deitrick]

Jim,

A User-Defined-Function can do it. The example code below take an integer
for the sheet number, and a string for the cell address, and returns that
value.

Use it like

=GetSheet(9, "M26")

The 9 and the M26 can each be the result of a formula or reference, so

=GetSheet(A1,B1)

would also work, if A1 has an integer, and B1 has a string cell address.

HTH,
Bernie
MS Excel MVP

Function GetSheet(shNum As Integer, cellAdd As String) As Variant
GetSheet = Worksheets(shNum).Range(cellAdd).Value
End Function

 

[jim sturtz]

bernie,

drat, i thot maybe you had it (or i'd got it) but that didnt work. what did
i do wrong"

when i place that in my sheet

=getsheet(3,"k26")

i get a cell with a small green triangle in the upper left corner of it, and
it says "#NAME?, as an error i guess.

what now?

thanks.

jim

 

[Bernie Deitrick]

Jim,

If you put the code into the window when you right-clicked on the sheet tab
and selected "view Code" then it is in the wrong place.

Use Alt-F11 to open the VBEditor, then Ctrl-R to open the project explorer,
then select your workbook, and use Insert Module. That's where you need to
paste your function code.

HTH,
Bernie
MS Excel MVP

 

[jim sturtz]

there is something called 3d functions that handles the job.

for example

=SUM('May 1:May 31'!E23)

or

=SUM('May 1:May 31'!E23:j23)