i think a minor question

  • Thread starter Thread starter jim sturtz
  • Start date Start date
J

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
 
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
 
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
 
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
 
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)
 
Back
Top