Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
On one particular sheet I'm having problem getting indirect to work
=indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
What's in AD65? Is it a number?
What's in the cell (G<11+whateverisinad65) And it's probably best to post the formulas you actually used in the worksheet instead of retyping them in the message. Maybe you made a mistake in the formulas you posted--for instance, the second one isn't correct. Maybe the same thing happened in the worksheet??? Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
What is the value stored in AD65? Try, in the formula bar, to select
the INDIRECT argument i.e. "G"&11+ad65 and press F9. What does the expression evaluate to? The strange thing is you are getting #VALUE! and not #REF!. This is likely if INDIRECT interprets the argument as a range rather than a single cell, in which case you would need to array-enter it to avoid #VALUE!. HTH Kostis Vezerides On Dec 11, 6:16 pm, Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
In your first expression you have a text string "G11", formed from the
concatenation of "G" with 11, and then you are trying to conduct an arithmetic add (with the + symbol) between that text string and the content of AD65. You can't do an arithmetic add on a text string like "G11". In your second expression you are conducting an arithmetic operation to add the number 11 to what is presumably a number in AD65. The result of this arithmetic operation is a number, which you are then converting to text with the TEXT function, then concatenating that text to the text "G", and the resulting text string is the input to the INDIRECT function. You say that in the same workbook the first method works just fine. Exactly what is the formula that you think works, and what are the values in the cells which are inputs to that formula? -- David Biddulph "Brad" wrote in message ... On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
What is in cell AD65 it is just a number in this example it is 65 (it could
also be 55 or 60) What is in cell ("G"&11+Whateverisinad65) - just a sum of other numbers - not an array. example =SUM(F96:F$130) This is not a mistake in typing Just a bit of clarification - there are four tabs that I can't get the indirect to work without using the text command. - going through Evaluate formula - it doesn't attach the two components (the letters and the numbers together). However, if I add a new worksheet - the conventional indirect works just fine. "Dave Peterson" wrote: What's in AD65? Is it a number? What's in the cell (G<11+whateverisinad65) And it's probably best to post the formulas you actually used in the worksheet instead of retyping them in the message. Maybe you made a mistake in the formulas you posted--for instance, the second one isn't correct. Maybe the same thing happened in the worksheet??? Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
I would agree that trying to add 65 to "G11" would not work - however that is
not what the formula is doing - I'm taking "G" and add it 11+65 or 76 to end up with "G76". This is not the first time that I have used this feature of Excel - however this is the first time that it hasn't worked. What is weird is that I can add a worksheet and the fomula will work okay on that sheet, but not the sheets that I need. There are no macro's that are running in the background.... "David Biddulph" wrote: In your first expression you have a text string "G11", formed from the concatenation of "G" with 11, and then you are trying to conduct an arithmetic add (with the + symbol) between that text string and the content of AD65. You can't do an arithmetic add on a text string like "G11". In your second expression you are conducting an arithmetic operation to add the number 11 to what is presumably a number in AD65. The result of this arithmetic operation is a number, which you are then converting to text with the TEXT function, then concatenating that text to the text "G", and the resulting text string is the input to the INDIRECT function. You say that in the same workbook the first method works just fine. Exactly what is the formula that you think works, and what are the values in the cells which are inputs to that formula? -- David Biddulph "Brad" wrote in message ... On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
I agree that getting the value error is odd. When I'm in four certain tabs -
the indirect does not "link" the letters and numbers together. In going through the formula evaluator - =Indirect("G"&11+ad65) =Indirect("G"&11+65) =Indirect("G"&76) #value Rather than giving me Indirect("G76") Don't know why.... "vezerid" wrote: What is the value stored in AD65? Try, in the formula bar, to select the INDIRECT argument i.e. "G"&11+ad65 and press F9. What does the expression evaluate to? The strange thing is you are getting #VALUE! and not #REF!. This is likely if INDIRECT interprets the argument as a range rather than a single cell, in which case you would need to array-enter it to avoid #VALUE!. HTH Kostis Vezerides On Dec 11, 6:16 pm, Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
What is even more weird is that I can take an indirect formula that is
working and put it in one of the sheets and it will not work. "Brad" wrote: I agree that getting the value error is odd. When I'm in four certain tabs - the indirect does not "link" the letters and numbers together. In going through the formula evaluator - =Indirect("G"&11+ad65) =Indirect("G"&11+65) =Indirect("G"&76) #value Rather than giving me Indirect("G76") Don't know why.... "vezerid" wrote: What is the value stored in AD65? Try, in the formula bar, to select the INDIRECT argument i.e. "G"&11+ad65 and press F9. What does the expression evaluate to? The strange thing is you are getting #VALUE! and not #REF!. This is likely if INDIRECT interprets the argument as a range rather than a single cell, in which case you would need to array-enter it to avoid #VALUE!. HTH Kostis Vezerides On Dec 11, 6:16 pm, Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
SYMPTOMS
In Microsoft Excel, when you concatenate cells, you receive a #VALUE! error if the Transition Formula Evaluation (TFE) option in version 5.0 or the Alternate Expression Evaluation (AEE) option in version 4.0 is enabled and one of the concatenated cells is blank or contains a number. This behavior also occurs with the INDIRECT function when you use concatenated cells. For example, the formula =INDIRECT("A"&B1) Turning this off solved the problem "Brad" wrote: What is even more weird is that I can take an indirect formula that is working and put it in one of the sheets and it will not work. "Brad" wrote: I agree that getting the value error is odd. When I'm in four certain tabs - the indirect does not "link" the letters and numbers together. In going through the formula evaluator - =Indirect("G"&11+ad65) =Indirect("G"&11+65) =Indirect("G"&76) #value Rather than giving me Indirect("G76") Don't know why.... "vezerid" wrote: What is the value stored in AD65? Try, in the formula bar, to select the INDIRECT argument i.e. "G"&11+ad65 and press F9. What does the expression evaluate to? The strange thing is you are getting #VALUE! and not #REF!. This is likely if INDIRECT interprets the argument as a range rather than a single cell, in which case you would need to array-enter it to avoid #VALUE!. HTH Kostis Vezerides On Dec 11, 6:16 pm, Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
Thanks for posting back.
Brad wrote: SYMPTOMS In Microsoft Excel, when you concatenate cells, you receive a #VALUE! error if the Transition Formula Evaluation (TFE) option in version 5.0 or the Alternate Expression Evaluation (AEE) option in version 4.0 is enabled and one of the concatenated cells is blank or contains a number. This behavior also occurs with the INDIRECT function when you use concatenated cells. For example, the formula =INDIRECT("A"&B1) Turning this off solved the problem "Brad" wrote: What is even more weird is that I can take an indirect formula that is working and put it in one of the sheets and it will not work. "Brad" wrote: I agree that getting the value error is odd. When I'm in four certain tabs - the indirect does not "link" the letters and numbers together. In going through the formula evaluator - =Indirect("G"&11+ad65) =Indirect("G"&11+65) =Indirect("G"&76) #value Rather than giving me Indirect("G76") Don't know why.... "vezerid" wrote: What is the value stored in AD65? Try, in the formula bar, to select the INDIRECT argument i.e. "G"&11+ad65 and press F9. What does the expression evaluate to? The strange thing is you are getting #VALUE! and not #REF!. This is likely if INDIRECT interprets the argument as a range rather than a single cell, in which case you would need to array-enter it to avoid #VALUE!. HTH Kostis Vezerides On Dec 11, 6:16 pm, Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
Congratulations of finding the failure mode.
I guess that it might be worth seeing whether it works if you change =Indirect("G"&11+ad65) to =Indirect("G"&(11+ad65)) , but the way you describe the problem I fear that it might not. Your formula =Indirect("G"&11+ad65) provokes an interesting question on precedence of operations. We know that multiplication and division take precedence over addition or subtraction in expressions like =A1+B1*C1, but where does string concatenation fit compared with these arithmetic precences? -- David Biddulph "Brad" wrote in message ... SYMPTOMS In Microsoft Excel, when you concatenate cells, you receive a #VALUE! error if the Transition Formula Evaluation (TFE) option in version 5.0 or the Alternate Expression Evaluation (AEE) option in version 4.0 is enabled and one of the concatenated cells is blank or contains a number. This behavior also occurs with the INDIRECT function when you use concatenated cells. For example, the formula =INDIRECT("A"&B1) Turning this off solved the problem "Brad" wrote: What is even more weird is that I can take an indirect formula that is working and put it in one of the sheets and it will not work. "Brad" wrote: I agree that getting the value error is odd. When I'm in four certain tabs - the indirect does not "link" the letters and numbers together. In going through the formula evaluator - =Indirect("G"&11+ad65) =Indirect("G"&11+65) =Indirect("G"&76) #value Rather than giving me Indirect("G76") Don't know why.... "vezerid" wrote: What is the value stored in AD65? Try, in the formula bar, to select the INDIRECT argument i.e. "G"&11+ad65 and press F9. What does the expression evaluate to? The strange thing is you are getting #VALUE! and not #REF!. This is likely if INDIRECT interprets the argument as a range rather than a single cell, in which case you would need to array-enter it to avoid #VALUE!. HTH Kostis Vezerides On Dec 11, 6:16 pm, Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problems with indirect
David
The way that indirect works (I believe) is that it uses AOS between the "&"'s Having the additional paren's doesn't make a difference here. "David Biddulph" wrote: Congratulations of finding the failure mode. I guess that it might be worth seeing whether it works if you change =Indirect("G"&11+ad65) to =Indirect("G"&(11+ad65)) , but the way you describe the problem I fear that it might not. Your formula =Indirect("G"&11+ad65) provokes an interesting question on precedence of operations. We know that multiplication and division take precedence over addition or subtraction in expressions like =A1+B1*C1, but where does string concatenation fit compared with these arithmetic precences? -- David Biddulph "Brad" wrote in message ... SYMPTOMS In Microsoft Excel, when you concatenate cells, you receive a #VALUE! error if the Transition Formula Evaluation (TFE) option in version 5.0 or the Alternate Expression Evaluation (AEE) option in version 4.0 is enabled and one of the concatenated cells is blank or contains a number. This behavior also occurs with the INDIRECT function when you use concatenated cells. For example, the formula =INDIRECT("A"&B1) Turning this off solved the problem "Brad" wrote: What is even more weird is that I can take an indirect formula that is working and put it in one of the sheets and it will not work. "Brad" wrote: I agree that getting the value error is odd. When I'm in four certain tabs - the indirect does not "link" the letters and numbers together. In going through the formula evaluator - =Indirect("G"&11+ad65) =Indirect("G"&11+65) =Indirect("G"&76) #value Rather than giving me Indirect("G76") Don't know why.... "vezerid" wrote: What is the value stored in AD65? Try, in the formula bar, to select the INDIRECT argument i.e. "G"&11+ad65 and press F9. What does the expression evaluate to? The strange thing is you are getting #VALUE! and not #REF!. This is likely if INDIRECT interprets the argument as a range rather than a single cell, in which case you would need to array-enter it to avoid #VALUE!. HTH Kostis Vezerides On Dec 11, 6:16 pm, Brad wrote: On one particular sheet I'm having problem getting indirect to work =indirect("G"&11+ad65) will give me a value error - However =indirect("G"&text(11+ad65,"##)) - will give me the correct answer In the same workbook - the first method of indirect works just fine. Does anyone know what is going on with this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30,4) | Excel Discussion (Misc queries) | |||
Indirect and Sumif Syntax Problems | Excel Discussion (Misc queries) | |||
Problems with PULL function, INDIRECT.EXT and so forth | Excel Worksheet Functions | |||
Is "INDIRECT" the cause of these problems? | Excel Discussion (Misc queries) |