Use the LEN and REPT functions for specialized cell testing and display options

- [Instructor] If you have a Workbook with linked formulas, that is formulas that get data from a different worksheet, sometimes you encounter the need to keep an eye on the data that's changing on another worksheet based on activity in the current one. And there is a feature found on the formulas tab in the ribbon. It's called Watch Window, and that's certainly valuable if you're trying to keep an eye on a cell or two, the changes that occur. But on this worksheet here called First Quarter, we've got something else going on and it looks a little strange at first. In columns A through E we've got some numbers and nothing too unusual going on over there, some formulas, some raw numbers, but off to the right things look a little different.

And actually, what this is is a picture. It's a picture that does something special in the sense that it actually has live data in it. Let's take a look at the data off to the left here. In column B is a sales number of 95,000. If I make a change here the formula in E3 will change. Suppose I change that to 100,000. This will go over 300,000. But surprisingly when we make this change we're also going to see the change off to the right. Watch what happens.

B3, 100,000. Enter. What's happened this has gone over 300,000. We also see that over here, first quarter total sales, 302,406, just like what we're seeing here. I didn't point it out, but that grand total over there to the right has changed, too. Went over 1.2 million. I'll press control z for the moment. Watch it change back. There we go. So what we're doing here is actually not just linking cells but linking a picture.

This can be moved around. We can shrink it. We can give it a different background and so on. The same thing occurs on the second quarter sheet, the third quarter sheet, and the fourth quarter sheet. There was another sheet out here called summary. And this is actually what we're seeing. So let's set up the steps for doing this. First of all, on the summary sheet, and I'll zoom in on this by holding down the control key, rolling the mouse wheel, these yellow cells here all have formulas looking something like this. I'll double click.

This is getting data from cell E3 in the first quarter sheet. And as you might guess already, second quarter sheet and so on, third quarter, all the way across. Right here, also, first quarter. And that's happening here and here and here and all these other yellow cells. So we've got formulas on the summary sheet that are gathering data from the other ones. Now, zooming back, control with the mouse wheel. What we're going to be doing here is selecting this data and copying it. But then we're going to be pasting it back on the other sheets in a special way.

So, even though these are viable, let's get rid of them and show you how it's done. We need only do this on one sheet you can do it later on your own on the other sheets. We don't have any picture here at all. Let's go to that summary sheet. This is all highlighted. We copy it, control C, right click, copy, however you prefer. Then we go over to the first quarter sheet. Click somewhat arbitrarily out here. On the home tab the drop arrow under Paste.

Now there is an option here for pasting a picture. We can do that. Let's look at that momentarily. That's an accurate depiction of what the other worksheet looks like right now. But it's not a picture link. It's as if we took a photograph and pasted it right here. If I change this 95,000 here to 100,000, it certainly will change this worksheet, and it will change the summary worksheet, but it won't change this image. There's the 100,000. This goes over 300,000.

Over in the summary sheet there's that 300,000. That's all happening. But what happened on the image here? Nothing at all. Because we simply pasted it as a picture. So let's delete it. Back to summary again. Highlight these cells. Once again, control c to copy. Back to first quarter. Off to the right here. Click a cell, paste, home tab. The final button here. Linked picture, there it is.

Like we saw it the first time around. Now I did change it to 100,000. I kept that there and that caused this to be over 300,000. And for the moment this is accurate. But now it will change. If I make this go to 110,000, the 302,406 here will accurately reflect the change that's caused in cell E3. I'll make this be 110,000. Enter. 312,000, we see it over here, too. And that grand total has gone up as well. And we would do the same kind of copy, paste on the other three sheets as well.

So this means, as we're working with any of these sheets and we make a change this causes a change on the summary sheet, and since the summary sheet is being reflected in this picture that we have on each of the sheets we know what's going on all the time on that other sheet. To make this a little bit more visible or if you want to play with the visuals a bit, what you can do, pretty easily, is on the home tab with the picture being selected, click the drop arrow next to the paint bucket. Maybe apply background color and probably want to experiment a little bit with which one is most readable. Maybe that one's okay.

So, it's a great tool for allowing us to view, somewhat like a TV camera, view images on another sheet. In this case lots of cells we're viewing at the same time.