Today's question: a formula is showing an answer in B2. Can you somehow repeat the input values in C2 so the person reading the spreadsheet can see the answer as well as what went into the answer. Bill and Mike duel it out with the & operator and the CONCATENATE function. Mike throws in a little ADDRESS function, and then Shift+F3 or Ctrl+A.
Transcript of the video:
Bill: Hey welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from ExcelIsFun, this is our episode 130 - Show How the Answer is Calculated!
Alright, so today's question sent in from Elissa Elissa at YouTube.
"How can I return the cells composing a formula?" So we have just a simple formula, B2=A2+A3, 5+3=8, but over in C2, she wants to display that 5 and the 3 are the source.
That's interesting.
Alright, so I think what we'll do, we'll say ="B2 is "&A2&, I'll probably do, " + "& and then come over and point to B3.
So "B2 is 5 + 3", and then if we change these numbers 6 and 2, it will document what is going on.
Now, you know what, my first thought was FORMULATEXT in Excel 2013, but that's going to show just A2+A3, it's not going to show the values.
I also thought of doing something here with the N function, plus you know, the N of something.
I used that trick recently for a client, where we were getting a circular reference.
So in the macro, we had to calculate an answer, change the values, and then continue.
But when I change the values, I had the macro document inside the N function, something like this, + N("This was 5 + 3") , of course, that was a macro, kind of thing.
But we could go back later and see, you know, what had been added.
So we never did(?) the circular reference.
But I think here, this one is pretty straightforward, just using the &, concatenate the values, should be good enough to document what we have.
Alright Mike, let's send it over to you!
Mike: Thanks MrExcel!
Hey, I can't quite beat that method.
In fact, you know what, I'm doing concatenating, I tend to use the &, the join symbol.
I suppose we could however use the CONCATENATE function, which is the same thing, the ampersand is just how to manually concatenate, which means to join separate things into one.
I'm going to use the CONCATENATE function, but check this out, I could just type it out here, argument argument argument.
But if we go to the function argument dialog box, we don't have to put in those double quotes.
Now I'm going to click on this right here, and this gives me the help, that's not what I meant to do.
I meant to go Shift+F3, that opens up the function argument dialog box.
By the way Esc, oops, =CON...
This right here is the hot link to get to help, Shift+F3 is the keyboard for INSERT function.
And if you already have the function typed out, it will jump right to the function argument dialog box.
So the first thing is "B2", and then " is ". Now what's so cool about the dialog box is, I don't have to put in those double quotes, right?
And notice that it actually does, so it will put those quotes in for us.
So it's kind of convenient.
And then I think we had 5, TAB, and I'm going to do " + ", TAB, and notice- whoa, look at that, it puts the double quotes in for you, and then that right there.
Now again, I basically never use this dialog box, I do what MrExcel does and type it out, but that's pretty convenient, especially knowing that it'll put in the double quotes for you, and you can put a space, so I click OK.
B2, whoops, F2.
Now I'm going to come back and edit it by putting a plus here.
Alright, and I guess we could do one step further.
Let's see, Shift+F3, I could come here, TAB, and then type " = ", TAB, and then how about "SUM(", or no, not SUM, just this cell, + symbol, this cell.
So I put a formula there too, and so now we can see the result there.
So now it says "B2 is 5 + 3 = 8".
Wow, that's pretty.
Now we could take this one step further.
If we moved this down here, it would still say B2 even though it's not B2.
So we could actually, since the information about what cell this is, it has come from that cell, we could use the ADDRESS function.
There's actually a few functions that you can get, a few functions like CELL is another one, but this is the one I usually use, it wants row_number column_number.
So I'm going to put ROW of, and click on that cell, comma, column of this cell.
So right now we have the row number and the column number coming from these other functions.
And then comma, this, which in the old days you had to go to help to figure what 1-2-3-4 meant, but this drop-down, I love it, it tells you what kind of cell reference to display.
For example, if you use the CELL function, it automatically shows absolute.
So I like the ADDRESS function because it gives me that 4 option for relative.
So now I could take that, and no way, copy, Esc, and I could come right here, and instead of- Actually, I'm going to have to go up to the dialog box, or how do I do this?
Instead of this B2, alright, so there is currently Text1.
So I'm going to type a comma, and then right now in Text1, Ctrl+V, that didn't work.
I'll temporarily Ctrl+Enter, see if I can copy this here, Esc, and then right there in Text1 I'll Ctrl+V. And so now, move this down here, now if I change this, move it down here, it'll say B6 or wherever.
It is "B6 is 5 + 3 = 8" Alright, throw back to MrExcel!
Bill: Hey, alright Mike.
Well this was an interesting podcast, it was a really simple question, and we kept going on and on and on with other ways to do it.
=CON- TAB, you press Shift+F3 here, I do Ctrl+A to open that dialog box, Ctrl+A. Once you have the open parentheses there, will get you to the same dialog box.
I liked your trick there with ADDRESS, very, very cool.
And hey, this was the first Dueling Excel podcast now in 4.7 months that we have not used an array formula!
Alright well hey, I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Hey, "Ctrl+Shift+Enter - Mastering Excel Array Formulas" is actually with a brand new title, or subtitle, that will be shipping to the printer today.
So three weeks, we'll have that book, woohoo!
See you next week!
I'm Bill Jelen from MrExcel, I'll be joined by Mike Girvin from ExcelIsFun, this is our episode 130 - Show How the Answer is Calculated!
Alright, so today's question sent in from Elissa Elissa at YouTube.
"How can I return the cells composing a formula?" So we have just a simple formula, B2=A2+A3, 5+3=8, but over in C2, she wants to display that 5 and the 3 are the source.
That's interesting.
Alright, so I think what we'll do, we'll say ="B2 is "&A2&, I'll probably do, " + "& and then come over and point to B3.
So "B2 is 5 + 3", and then if we change these numbers 6 and 2, it will document what is going on.
Now, you know what, my first thought was FORMULATEXT in Excel 2013, but that's going to show just A2+A3, it's not going to show the values.
I also thought of doing something here with the N function, plus you know, the N of something.
I used that trick recently for a client, where we were getting a circular reference.
So in the macro, we had to calculate an answer, change the values, and then continue.
But when I change the values, I had the macro document inside the N function, something like this, + N("This was 5 + 3") , of course, that was a macro, kind of thing.
But we could go back later and see, you know, what had been added.
So we never did(?) the circular reference.
But I think here, this one is pretty straightforward, just using the &, concatenate the values, should be good enough to document what we have.
Alright Mike, let's send it over to you!
Mike: Thanks MrExcel!
Hey, I can't quite beat that method.
In fact, you know what, I'm doing concatenating, I tend to use the &, the join symbol.
I suppose we could however use the CONCATENATE function, which is the same thing, the ampersand is just how to manually concatenate, which means to join separate things into one.
I'm going to use the CONCATENATE function, but check this out, I could just type it out here, argument argument argument.
But if we go to the function argument dialog box, we don't have to put in those double quotes.
Now I'm going to click on this right here, and this gives me the help, that's not what I meant to do.
I meant to go Shift+F3, that opens up the function argument dialog box.
By the way Esc, oops, =CON...
This right here is the hot link to get to help, Shift+F3 is the keyboard for INSERT function.
And if you already have the function typed out, it will jump right to the function argument dialog box.
So the first thing is "B2", and then " is ". Now what's so cool about the dialog box is, I don't have to put in those double quotes, right?
And notice that it actually does, so it will put those quotes in for us.
So it's kind of convenient.
And then I think we had 5, TAB, and I'm going to do " + ", TAB, and notice- whoa, look at that, it puts the double quotes in for you, and then that right there.
Now again, I basically never use this dialog box, I do what MrExcel does and type it out, but that's pretty convenient, especially knowing that it'll put in the double quotes for you, and you can put a space, so I click OK.
B2, whoops, F2.
Now I'm going to come back and edit it by putting a plus here.
Alright, and I guess we could do one step further.
Let's see, Shift+F3, I could come here, TAB, and then type " = ", TAB, and then how about "SUM(", or no, not SUM, just this cell, + symbol, this cell.
So I put a formula there too, and so now we can see the result there.
So now it says "B2 is 5 + 3 = 8".
Wow, that's pretty.
Now we could take this one step further.
If we moved this down here, it would still say B2 even though it's not B2.
So we could actually, since the information about what cell this is, it has come from that cell, we could use the ADDRESS function.
There's actually a few functions that you can get, a few functions like CELL is another one, but this is the one I usually use, it wants row_number column_number.
So I'm going to put ROW of, and click on that cell, comma, column of this cell.
So right now we have the row number and the column number coming from these other functions.
And then comma, this, which in the old days you had to go to help to figure what 1-2-3-4 meant, but this drop-down, I love it, it tells you what kind of cell reference to display.
For example, if you use the CELL function, it automatically shows absolute.
So I like the ADDRESS function because it gives me that 4 option for relative.
So now I could take that, and no way, copy, Esc, and I could come right here, and instead of- Actually, I'm going to have to go up to the dialog box, or how do I do this?
Instead of this B2, alright, so there is currently Text1.
So I'm going to type a comma, and then right now in Text1, Ctrl+V, that didn't work.
I'll temporarily Ctrl+Enter, see if I can copy this here, Esc, and then right there in Text1 I'll Ctrl+V. And so now, move this down here, now if I change this, move it down here, it'll say B6 or wherever.
It is "B6 is 5 + 3 = 8" Alright, throw back to MrExcel!
Bill: Hey, alright Mike.
Well this was an interesting podcast, it was a really simple question, and we kept going on and on and on with other ways to do it.
=CON- TAB, you press Shift+F3 here, I do Ctrl+A to open that dialog box, Ctrl+A. Once you have the open parentheses there, will get you to the same dialog box.
I liked your trick there with ADDRESS, very, very cool.
And hey, this was the first Dueling Excel podcast now in 4.7 months that we have not used an array formula!
Alright well hey, I want to thank everyone for stopping by, we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Hey, "Ctrl+Shift+Enter - Mastering Excel Array Formulas" is actually with a brand new title, or subtitle, that will be shipping to the printer today.
So three weeks, we'll have that book, woohoo!
See you next week!
