Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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)
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,311
Default 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)



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,440
Default 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)


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default 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)



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,939
Default 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)



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Use text in formula as a cell reference

Thanks for the direction! I'll look into applying a macro.
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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?
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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?




  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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?



  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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?




  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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


  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Making cell reference absolute makes cell format text Excel Worksheet Functions 2 September 22nd 06 04:47 PM
Cell only shows link in text, not contents of reference cell Jay Mac New Users to Excel 4 August 23rd 05 08:36 PM
reference the result of a formula in a text formatted cell jpwinston Excel Discussion (Misc queries) 1 February 7th 05 06:33 PM
How can I reference data or text within a text box to a cell in Ex BB Excel Discussion (Misc queries) 2 January 25th 05 08:55 PM
Formula with text and reference to a date cell [email protected] Excel Discussion (Misc queries) 1 January 11th 05 09:15 AM


All times are GMT +1. The time now is 05:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"