Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use text in formula as a cell reference
Thanks for the direction! I'll look into applying a macro.
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making cell reference absolute makes cell format text | Excel Worksheet Functions | |||
Cell only shows link in text, not contents of reference cell | New Users to Excel | |||
reference the result of a formula in a text formatted cell | Excel Discussion (Misc queries) | |||
How can I reference data or text within a text box to a cell in Ex | Excel Discussion (Misc queries) | |||
Formula with text and reference to a date cell | Excel Discussion (Misc queries) |