Bill has a worksheet where he's building text references and then hopes to evaluate the resulting SUM function. Instead, I suggest using INDIRECT to add up the three individual cells. Episode 727 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today a question sent in by Bill.
Bill says hey I have text that represents the formula that I want, but how can I get it to evaluate that formula?
And he was on an interesting track here he has some codes here 1, 2, 3 I'm not sure where those come from, but then he built formulas here.
Let me just show you all the formulas.
So, control and the grave accent will see all the formulas at once.
Basically, he's taking equal A ampersand whatever happens to be over here in the code column.
So, you know basically we'll go back to regular mode A1, A2, A3, you know or if that gets changed 5, it says A5 and then down here he used concatenation the ampersand, to build an interesting formula that would make something that looks like a SUM function from those three things, but we can't use the SUM function in direct.
So, what I did we're going to go to another sheet here I said let's edit your formula, and basically we're going to use the INDIRECT function.
So, =INDIRECT(, of this value A1 that's going to return whatever happens to be up in cell A1.
So, you'll see that if this changes here we get a 9 the indirect works as I said because you only have three terms it's going to be easy to just refer to all three of those so the indirect of the next cell plus the indirect of the next cell and we would have the result 24.
And sure enough as things change so, we go to cell 5 or cell 3 the total will automatically update.
So, INDEIRECT is a cool function that says hey instead of telling you to you know add up with an E4, go to E4, there's going to be a cell reference in E4 and go add up whatever is there.
So, as we look at this it's basically going to go to E4 and pull whatever is in cell A1 and then whatever is in cell A3 and then whatever's in cell A5.
Interesting way to kind of dynamically build a formula based on some other results.
So, thanks to Bill for sending that question in and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today a question sent in by Bill.
Bill says hey I have text that represents the formula that I want, but how can I get it to evaluate that formula?
And he was on an interesting track here he has some codes here 1, 2, 3 I'm not sure where those come from, but then he built formulas here.
Let me just show you all the formulas.
So, control and the grave accent will see all the formulas at once.
Basically, he's taking equal A ampersand whatever happens to be over here in the code column.
So, you know basically we'll go back to regular mode A1, A2, A3, you know or if that gets changed 5, it says A5 and then down here he used concatenation the ampersand, to build an interesting formula that would make something that looks like a SUM function from those three things, but we can't use the SUM function in direct.
So, what I did we're going to go to another sheet here I said let's edit your formula, and basically we're going to use the INDIRECT function.
So, =INDIRECT(, of this value A1 that's going to return whatever happens to be up in cell A1.
So, you'll see that if this changes here we get a 9 the indirect works as I said because you only have three terms it's going to be easy to just refer to all three of those so the indirect of the next cell plus the indirect of the next cell and we would have the result 24.
And sure enough as things change so, we go to cell 5 or cell 3 the total will automatically update.
So, INDEIRECT is a cool function that says hey instead of telling you to you know add up with an E4, go to E4, there's going to be a cell reference in E4 and go add up whatever is there.
So, as we look at this it's basically going to go to E4 and pull whatever is in cell A1 and then whatever is in cell A3 and then whatever's in cell A5.
Interesting way to kind of dynamically build a formula based on some other results.
So, thanks to Bill for sending that question in and thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
