Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Formula
Hello,
I need to perform calculations using mutiple cell references on a long spreadsheet. I am using a CONCATENATE function to generate the text of the long formula. But, I am not able to get excel to think it a formula and not as text. I can always click on the cell, add "=" as the first character and hit "return", it will show the value. But, if add "=" as part of the concatenate function, excel doesnt think it to be formula, thus the text with the "=" is displayed. Any idea how to address this issue? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Formula
Try the alternative &
=A1&B1&C1 for =CONCATENATE(a1,b1,c1) If this post helps click Yes --------------- Jacob Skaria "VK" wrote: Hello, I need to perform calculations using mutiple cell references on a long spreadsheet. I am using a CONCATENATE function to generate the text of the long formula. But, I am not able to get excel to think it a formula and not as text. I can always click on the cell, add "=" as the first character and hit "return", it will show the value. But, if add "=" as part of the concatenate function, excel doesnt think it to be formula, thus the text with the "=" is displayed. Any idea how to address this issue? Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Formula
YOu don't need to use CONCATENATE, as Jacob said. However, if you're trying
to build a formula, you need to place it inside the INDIRECT function. Example: =SUM(INDIRECT("A"&B1&":B"&B1)) Assuming cell B1 has the value of 3, this would give the sum of A3:B3. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "VK" wrote: Hello, I need to perform calculations using mutiple cell references on a long spreadsheet. I am using a CONCATENATE function to generate the text of the long formula. But, I am not able to get excel to think it a formula and not as text. I can always click on the cell, add "=" as the first character and hit "return", it will show the value. But, if add "=" as part of the concatenate function, excel doesnt think it to be formula, thus the text with the "=" is displayed. Any idea how to address this issue? Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Formula
Excel will never see the results of your formula as a formula without you doing
something special. One way is to include that equal sign, convert the formula to a value (edit|copy, edit|paste special|values and then converting those cells to real formulas. (Maybe edit|Replace equal sign with equal sign.) Another option is to use a user defined function. Option Explicit Function Eval(myStr As String) As Variant Application.Volatile True Eval = Application.Caller.Parent.Evaluate(myStr) 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.) Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel. Into a test cell and type: =eval(a1) VK wrote: Hello, I need to perform calculations using mutiple cell references on a long spreadsheet. I am using a CONCATENATE function to generate the text of the long formula. But, I am not able to get excel to think it a formula and not as text. I can always click on the cell, add "=" as the first character and hit "return", it will show the value. But, if add "=" as part of the concatenate function, excel doesnt think it to be formula, thus the text with the "=" is displayed. Any idea how to address this issue? Thanks in advance -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
Excel:Get concatenated text to be recognised as formula not text? | Excel Discussion (Misc queries) | |||
Formula to count text and alert me if a text appears more than twi | Excel Discussion (Misc queries) | |||
match cell text with text in formula | Excel Worksheet Functions |