Home/ Questions/Q 14610
Next
Answered
computing
  • 1
Asked: 2021-12-15T11:51:38+00:00 In:

Excel Reference 2nd Sheet Without The Name

  • 1

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

Share

    1 Answer

    1. Best Answer
      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.

      • 0