Steve asks if he can build an Excel worksheet where people can change numbers by dragging a graphical element. Episode 648 shows you how you can use a chart in previous versions of Excel to solve this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen and we have a question that was sent in today by Steve.
I'm Gonna take two days to answer this question.
One method today and another method tomorrow.
Steve said I'm building at a worksheet that someone needs to fill in their budget numbers and basically I want to give them a graphical way to change these numbers rather than have them type in numbers.
They can just be able to you know be nice so they could just grab something on the chart and slide it.
Well if you're, up to excel 2003. They took this feature out of excel 2007.
There's an amazing bit of functionality here.
Watch what happens when I click on for example, the department D.
I get all of the columns in the chart selected, but if I do a second single click, I will now select just column D and I can go to the top edge of that.
My mouse pointer changes to an up and down arrow.
You'll see over here in cell B5. 37410.
If I would drag this to a new spot 45200, it actually changes the number on the spreadsheet.
So we could basically teach the people, Hey, you just have to click and you can drag each department to the new spot and you don't have to type any numbers at all.
Now the big problem with this is basically doing the training.
You'll have to, you know, teach them that the first click doesn't count and it's the second click that actually puts it in edit mode.
This is also really, if you think about it, a dangerous thing, if someone would accidentally click.
It would take a couple of clicks but a couple of accidental clicks on the chart would really have possibly create havoc. So I think that's why microsoft took it out of excel 2007.
That's certainly one option.
Now tomorrow, we'll take a look at using activex scroll bars to solve this problem as well.
Thanks for stopping by. I will see you tomorrow for another netcast from MrExcel.
you
I'm Gonna take two days to answer this question.
One method today and another method tomorrow.
Steve said I'm building at a worksheet that someone needs to fill in their budget numbers and basically I want to give them a graphical way to change these numbers rather than have them type in numbers.
They can just be able to you know be nice so they could just grab something on the chart and slide it.
Well if you're, up to excel 2003. They took this feature out of excel 2007.
There's an amazing bit of functionality here.
Watch what happens when I click on for example, the department D.
I get all of the columns in the chart selected, but if I do a second single click, I will now select just column D and I can go to the top edge of that.
My mouse pointer changes to an up and down arrow.
You'll see over here in cell B5. 37410.
If I would drag this to a new spot 45200, it actually changes the number on the spreadsheet.
So we could basically teach the people, Hey, you just have to click and you can drag each department to the new spot and you don't have to type any numbers at all.
Now the big problem with this is basically doing the training.
You'll have to, you know, teach them that the first click doesn't count and it's the second click that actually puts it in edit mode.
This is also really, if you think about it, a dangerous thing, if someone would accidentally click.
It would take a couple of clicks but a couple of accidental clicks on the chart would really have possibly create havoc. So I think that's why microsoft took it out of excel 2007.
That's certainly one option.
Now tomorrow, we'll take a look at using activex scroll bars to solve this problem as well.
Thanks for stopping by. I will see you tomorrow for another netcast from MrExcel.
you
