Excel Reference 2nd sheet without the name

Microsoft Office excel 2003
February 8, 2011 at 14:38:02
Specs: Windows 7
What I need to know is how to reference the second sheet in a (2003, .xls) workbook when using an Excel Formula without using the sheets name.

I need be able to write the following formula
=Average('Sheet 2'!A1:C3)
Except The Sheet name will not be "Sheet 2" but will change

I know you can do this when creating a Macro. You can refer to the second sheet of any document with by using Sheets(2) no matter what the sheet name is.

Thanks



See More: Excel Reference 2nd sheet without the name

Report •

#1
February 8, 2011 at 14:41:48
Use the name feature - this will work across the sheets if in the same workbook of course. At least, if memory serves it will.

So, with that said ...

Go to the cell and click one time - go to the box where the name of the cell is located, i.e. B12 - Type a name in the box. You can use this as the reference versus the sheet name and the cell. I caution you not to name the cell until you are sure of the sheet structure. :)

I'm learning.


Report •

#2
February 8, 2011 at 17:27:12
Here's a way to reference any sheet by it's location in the workbook:

Open the VBA editor
Insert a module
Paste this User Defined Function (UDF) into the pane:

Function MySht(ByVal sht As Integer)
  Application.Volatile
  MySht = Sheets(sht).Name
End Function

In any cell use a formula similar to this:

=INDIRECT(MySht(2) & "!B12")

MySht() will return the name of the sheet referenced by the argument and INDIRECT will use it to build the reference.

You can put the sheet number directly in the formula, or use a cell reference so that you can change the sheet number without touching the formula:

=AVERAGE(INDIRECT(MySht(B1) &"!A1:A5"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
February 9, 2011 at 12:11:09
Sorry I should of said that I am working within a macro and i am unable to reference different sheets inside the workbook.
I am still working with your suggestions with but have had no luck.

This is what I am trying to do

ActiveCell.FormulaR1C1 = "=AVERAGE(Sheets(2).Range(C2:C499))"

But =AVERAGE(Sheets(2).Range(C2:C499)) is not a valid formula in Excel
Sheets(2).Range(C2:C499) is how to reference sheets in VB


Report •

Related Solutions

#4
February 9, 2011 at 12:20:35
"The Sheet name will not be "Sheet 2" but will change

The only way I can think to do something with a cell value that will not have a constant reference in any manner is to set a variable.

I have not been in the VBA for excel much, if at all - as necessity demanded only - but perhaps this a need you have. You could set the value of a variable by the cell with code.

I am sorry I cannot explain more ... I am hoping you can code to some extent. :)

Google Search: vba code excel, create a variable, assign a value to variable from cell

Something like this should do what you want. If you need more help, post specfics as to why the sheet will change, where the variable value will originate, and the use of the value. We can probably guide you more for writing the code.

I'm learning.


Report •

#5
February 9, 2011 at 12:39:09
re: "=AVERAGE(Sheets(2).Range(C2:C499))"

In this case you need to "mix your metaphors" so to speak.

Anything you put inside the quotes will be treated by VBA as a text string and, as you have seen, will be placed in the cell letter for letter.

If you want VBA to evaluate something and then build an Excel formula with the result, you need to remove the "evaluation" from inside the quotes so that VBA can work on it. You have to keep flipping your brain back and forth between the syntax that Excel is going to need and the syntax that VBA needs to perform its work.

In addition, since you are supplying a specific range (C2:C499) you need to use .Formula not .FormulaR1C1 (Try my solution both ways and see what happens)

Finally, rarely do you have to Select an object within VBA to perform an operation on it. Doing that slows the code down and makes it very inefficient. VBA can typically work with an object without it being Selected or Active.

Try this:

Sheets(1).Range("C5").Formula = "=AVERAGE(" & Sheets(2).Name & "!C2:C499)"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 9, 2011 at 13:14:46
Thank you Both
I was able to use your Formula DerbyDad03

Function MySht(ByVal sht As Integer)
Application.Volatile
MySht = Sheets(sht).Name
End Function

Here is what I am using (this is just part of the macro I am building)

Sheets(1).Select
Range("C3").Select
ActiveCell.FormulaR1C1 = "=AVERAGE('MySht(2)'!(R[1]C[-7]:R[499]C[-7]))"
Thanks again


Report •

#7
February 9, 2011 at 13:29:35
Did you read my Response #5?

I'm not sure it makes sense to use a UDF when VBA can build the formula directly.

Why force the UDF to be evaluated after VBA puts the formula in the cell?

That seems like a roundabout way to get to your goal.

"I'll use VBA to put a formula in a cell that contains a UDF so that VBA can evaluate the UDF in order to complete the formula."

Everytime the sheet calculates it will have to take the extra steps to evalulate the UDF. UDF's are much more inefficient than built-in Excel functions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 9, 2011 at 14:02:04
Your code works Great! Way better then the Solution I had.
Thank you

Sheets(1).Range("C5").Formula = "=AVERAGE(" & Sheets(2).Name & "!C2:C499)"



Report •

Ask Question