Use text in formula as a cell reference
 

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)

Use text in formula as a cell reference
 

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

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

HTH,
Paul

--

"steveylevi" wrote in message
...
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)

Use text in formula as a cell reference
 

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"steveylevi" wrote in message ...
|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)

Use text in formula as a cell reference
 

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

"steveylevi" wrote in message
...
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)

Use text in formula as a cell reference
 

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
--
HTH...

Jim Thomlinson


"steveylevi" wrote:

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)

Use text in formula as a cell reference
 

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

Use text in formula as a cell reference
 

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 he
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 wrote:

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

Use text in formula as a cell reference
 

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

Use text in formula as a cell reference
 

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?

Use text in formula as a cell reference
 

Functions do not appear in the ToolsMacroMacros 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

On Fri, 25 Jul 2008 07:14:01 -0700, N. Cheever
wrote:

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?

Use text in formula as a cell reference
 

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" wrote:

Functions do not appear in the ToolsMacroMacros 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

On Fri, 25 Jul 2008 07:14:01 -0700, N. Cheever
wrote:

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?

Use text in formula as a cell reference
 

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

On Tue, 29 Jul 2008 07:34:04 -0700, N. Cheever
wrote:

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" wrote:

Functions do not appear in the ToolsMacroMacros 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

On Fri, 25 Jul 2008 07:14:01 -0700, N. Cheever
wrote:

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?

Use text in formula as a cell reference
 

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" wrote:

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

Use text in formula as a cell reference
 

Good to hear.

Thanks for the feedback.


Gord

On Tue, 29 Jul 2008 13:22:05 -0700, N. Cheever
wrote:

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" wrote:

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