Use text in formula as a cell reference

[steveylevi]

I want to reference the results of a user-input cell number into a formula.

For example, in cell A1, user manually types in "B2".

I need the formula to use that variable (i.e., B2) =sum(b2:b10)

 

[PCLIVE]

How is B10 determined? If it is always the same, then:

=SUM(INDIRECT(A1&":B10"))

HTH,
Paul

 

[Niek Otten]

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I want to reference the results of a user-input cell number into a formula.
|
| For example, in cell A1, user manually types in "B2".
|
| I need the formula to use that variable (i.e., B2) =sum(b2:b10)

 

[Pete_UK]

So, if the user enters C2 do you want the formula to evaluate to
SUM(C2:C10)? If so, try this:

=SUM(INDIRECT(A1&":"&LEFT(A1,1)&"10"))

Hope this helps.

Pete

 

[Jim Thomlinson]

You want to look at the indirect function. It turns text into a cell
reference...

=SUM(INDIRECT(A1&":B10"))

where b2 has been placed in cell A1

 

[N. Cheever]

Here's a crazy variation...

Is it possible to reference a cell for the function call? For example, I
have:

=B1(F3,G7)

SUM or AVERAGE or MIN or MAX could be entered into as text into B1. If B1 =
MIN, then the formula becomes:

=MIN(F3,G7)

Thoughts?
-N

 

[Dave Peterson]

Depends on how much variation there is in the expressions you want to
evaluate--and how much time you want to put in to make it work.

This is a User Defined Function (UDF) that has no validity checks. It accepts
exactly 3 cells and evaluates something that looks like =xxx(yy,zz):

Option Explicit
Function myFunct(rng1 As Range, rng2 As Range, rng3 As Range) As Variant
Dim myVal As Variant
myVal = Application.Caller.Parent.Evaluate _
(rng1.Value & "(" & rng2.Value & "," & rng3.Value & ")")

myFunct = myVal
End Function

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[N. Cheever]

Thanks for the direction! I'll look into applying a macro.

 

[N. Cheever]

Hmm, I'm feeling a bit dumb -- in picking the macro for the cell, the
"User-Defined" option never appears in the drop down box. I've looked around
web for why this doesn't appear, but I'm a bit baffled. Could it be some
global setting my Excel isn't set for? Or how I saved the macro?

 

[Gord Dibben]

Functions do not appear in the Tools>Macro>Macros dialog.

Click on the Fx button to get a list of Functions Categories

User Defined will be one of those categories.


Gord Dibben MS Excel MVP

 

[N. Cheever]

Weird... still no dice.

Options I have:

- Most Recently Used
- All
- Financial
- Date & Time
- Math & Trig
- Statistical
- Lookup & Reference
- Database
- Text
- Logical
- Information


Ghost in the Machine?

 

[Gord Dibben]

Where did you paste the Function Code?

It must be placed into a General Module before Excel will recognize it as a
UDF

If placed in a sheet or workbook module Excel won't give you a UDF category.


Gord

 

[N. Cheever]

A-HA! That did it -- I was bumbling around the Sheet and Workbook. I now
see the UDF option.

Thanks for all the direction

--N

 

[Gord Dibben]

Good to hear.

Thanks for the feedback.


Gord

A-HA! That did it -- I was bumbling around the Sheet and Workbook. I now
see the UDF option.

Thanks for all the direction

--N