Home Archives for Cell Link in Excel Forms
September 13, 2012 2 Comments

How to Use a Check Box to “Toggle” Conditional Formatting On or Off in Excel

Sort by Color in Excel

Sort by Color in Excel

In my previous Excel Tutorial – “Conditional Formatting to Dynamically Format Dates” – I demonstrated how to  apply Conditional  Formatting based upon the number of days have passed since last contact with a customer. Now I will extend what you just learned.

Three Techniques in this Video:

  1. How to extend  Conditional Formatting Rules to additional cell ranges.
  2. How to use the “Sort by Colors” feature introduced in Excel 2007.
  3. How to add a Check Box Form Control that will “toggle” the Conditional Formatting On or Off.

Paste Special “Live Preview”

I demonstrate a great new feature introduced in Excel 2010, “Live Preview” for Paste Special. You will use Paste Special – Formats to extend the rules for Conditional Formatting to a new range of Cells

Sort by Color

Beginning with Excel 2007, you can now sort fields based upon the color of a font or a cell background. I show how this works.

The Check Box Form Control

The “key concept” to understand when using Excel’s Form Controls is the “Cell Link.” With a Check Box Control, the value in the Cell Link is TRUE when the box is “checked” while the value is FALSE when it is Unchecked. We can use this information to create a “Conditional Format based upon a Formula.”

Conditional Formatting Rules

When you base Conditional Formatting upon a Formula, the result of that formula must return TRUE in order apply this formatting. Setting the background cell color to “White” effectively “hides” the previous background cell colors.

Shop for The 50 Best Tips for Excel 2007

Shop for The 50 Best Tips for Excel 2007

Resources Offered for Sale

I encourage you to visit my secure online shopping website – http://shop.thecompanyrocks.com – to preview the many training resources that I offer you. Check out my latest Excel Training Videos:

Watch this Video in High Definition

Click on this link to watch this video in High Definition on my YouTube Channel – DannyRocksExcels

 

How to Use a Check Box to "Toggle" Conditional Formatting On or Off in Excel [ 8:29 ] Play Now | Play in Popup | Download (3175)
Link to this post!
Filed Under: Conditional Formatting, Excel Forms, Excel Training, New in Excel 2007, New in Excel 2010, Podcast Tips & Timesavers Tagged: , , , , , , , , , ,
November 15, 2010 Leave a Comment

Use a Combo Box to Dynamically Change Your Excel Chart Data

You can “drive” your Excel Charts – dynamically change the data behind the chart. Focus your audience’s attention on the information that you are discussing. Let your audience see a chart that illustrates the scenario that they select.

To do this – add a Combo Box that lists the choices for each chart display that you offer!

(Click here to view my free Excel Training lesson on Combo Box Controls for more information.)

Key Steps to Take:

  • Use the INDEX() Function to look-up the values for your chart data.
  • For the 2nd argument in the INDEX() Function select the cell that is the “cell link” for your Combo Box. Use the F4 Keyboard Shortcut to make this part of the formula “Absolute.”
  • Insert the Chart Type that best represents your data. Position your chart adjacent to the Combo Box.

Click here to watch this video on my YouTube Channel – DannyRocksExcels –  in High Definition and Full Screen Mode.

I have several lessons that cover Charting in Excel 2007 on my DVD, “The 50 Best Tips for Excel 2007.” Buy it now!

Learn to “Master Excel in Minutes – Not Months!”

Use a Combo Box to Dynamically Change Your Excel Chart Data [ 6:53 ] Play Now | Play in Popup | Download (1407)
Link to this post!
Filed Under: Excel Charts, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , , , ,
October 30, 2010 1 Comment

How to Add a Data Validation Drop Down List to an Excel Form

Several viewers wrote to me after I posted my previous video – “How to Add a Combo Box Control to an Excel Invoice Form” to suggest that there is another way to create a “drop down list” for a form. Yes, you can use Data Validation in Excel and use “Allow from List” as your setting. Use the “Stop Style” to prevent a user from typing in a value that is not in the list.

In this Excel Training Video I compare both approaches: Using a Combo Box Control and Adding a Data Validation Drop Down List. Both work well on Invoice Forms. However, you will use a different function to “lookup” other values – e.g. Unit Price – depending upon your choice:

  • With a Combo Box Control, use the =INDEX() function to find the “Unit Price” for the product selected in the Combo Box
  • With a Data Validation Drop Down List, use the =VLOOKUP() function to find the matching “Unit Price.”

Let me know which approach that you prefer. Try both – expand your Excel Skill Set. Add your comments below or send me an email with your thoughts and suggestions: danny@thecompanyrocks.com

You can start to shop for my DVD, “The 50 Best Tips for Excel 2007” by clicking this link.

Learn how you can “Master Excel in Minutes”

How to Add a Data Valication Drop Down List to an Excel Form [ 6:55 ] Play Now | Play in Popup | Download (3114)
Link to this post!
Filed Under: Data Validation, Excel Forms, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , , , , ,
October 28, 2010 13 Comments

How to Add a Combo Box Control to an Invoice in Excel

If you are creating or modifying an Invoice Form in Excel, you will want to add a Combo Box Control to allow users to select products from a listing. The key in formatting your Combo Box Control is to choose the correct cell to contain the “Cell Link.”

Cell Link in Form Control

I think that you will benefit from “seeing how this is done” in this video lesson. I know that I always struggled with “reading about” Excel Form Controls. Once you see how important the “Cell Link” placement is, you will better understand how Combo Box Controls work.

Use INDEX Function

Once we have formatted the Combo Box, we need to be able to look up other values to place on our Invoice. In this example I demonstrate how to use the INDEX() Function to lookup the “Unit Price” for each product selected from the Combo Box list on the Invoice. As a best practice, I recommend that you use “Named Ranges” for the “array” that you Index. The INDEX() Function has three arguments:

  1. The ARRAY to Index – In this case our “named range” with three fields (Product Name, Unit Price, Cell Link)
  2. The ROW reference – In this case the cell in the ARRAY that contains our CELL LINK for the Combo Box
  3. Optionally, the COLUMN reference – in this case “2” for the 2nd Column in the ARRAY (Unit Price)

Let me know if my videos in this series have helped you to understand how to use Form Controls in Excel. It took me some time to figure out how they worked; I hope that I can save you some time and ease your frustration in apply them to your forms. Add your comments below or send me an email – danny@thecompanyrocks.com

Find Additional Videos for Form Controls in Excel

Form Controls include Option Buttons, Spinners, List Boxes and more. Here is a link to the other videos in my series on Form Controls in Excel.

Watch Tutorial on YouTube

If you prefer, follow this link so that you can watch this video on my YouTube Channel – DannyRocksExcels

Resources Available at My Online Shopping Site

I invite you to visit my new, secure online shopping site  where you can Learn how you to “Master Excel in Minutes – Not Months!

How to Add a Combo Box Control to an Invoice Form in Excel [ 8:01 ] Play Now | Play in Popup | Download (1645)
Link to this post!
Filed Under: Excel Forms, Excel Training, Lookup Functions in Excel, Podcast Tips & Timesavers Tagged: , , , , , , ,
October 23, 2010 Leave a Comment

How to Add Scroll Bar Controls to Excel Forms

This is the fourth in my continuing series of Excel training video lessons on Adding Form Controls to your worksheets. Here, I demonstrate how to Add Scroll Bar Controls to a Loan Scenario. Form Controls help to make your Excel worksheets “interactive,” and they are great tools to add when you distribute spreadsheets to users who may not be proficient in Excel.

Understand the Cell Link

The key concept to understand with Form Controls is the “Cell Link.” The form controls are merely “drawing shapes” until you actually link them to a cell that controls their output. The “got’cha” step for Scroll Bars – as with Spin Buttons – is that the Maximum Value is 30,000. So, if you want a control to show a loan amount of, for example, $300,000.00 you make the Maximum value 300 and then create a formula that references the “cell link” and multiplies it by 1,000.

In my research on Form Controls, I rarely find authors who mention this point – and, in my experience, this is how most Excel users get “tripped up” when they want to add Form Controls to workbooks to make them interactive. Watch as I walk you through each step in this process.

Please, let me know if this video lesson has helped you to better understand how Excel Form Controls work. Add your comment below – I promise to respond to your comments!

Danny

You can click on this link to view the other videos in my series on Form Controls in Excel.

Learn how to “Master Excel in Minutes – Not Months!”

How to Add Scroll Bar Controls to Excel Forms [ 5:32 ] Play Now | Play in Popup | Download (1981)
Link to this post!
Filed Under: Excel Forms, Excel Formulas, Excel Training, Podcast Tips & Timesavers, Video Training Courses Tagged: , , , , , , ,
October 20, 2010 1 Comment

Create Interactive Excel Forms Using Option Button Controls

This is the third in my series of video lessons that show you how to add Form Controls to an Excel worksheet. Option Buttons, inside a Group Box, are a great way to make your forms “interactive” – to give your end-user multiple options. For example, a choice of shipping methods for their order. Or, to see how different “down payment” options effect their mortgage payments.

The key to understanding how Excel Form Controls operate is to see how the value in the “cell link” changes when you choose an option. The cell link for an Option Button translates a “text expression” into a numeric value. For example, “Surface” translates to “1” for a shipping option.

In this Excel Training Video, I introduce the =CHOOSE() Function. It is rare that I find a client who has ever used this function. Once you see how useful it is, you will start to use it instead of struggling through multiple =IF() Functions. It is really a great “hidden gem” of an Excel Function!

Click on this link to go to my Archive of Excel Video Lessons on Forms. 

You can also find additional groups of related videos by selecting a “Category” from the drop-down menu on the right side bar of any page on my website.

Learn how to “Master Excel in Minutes – Not Months!”

Create Interactive Excel Forms Using Option Button Controls [ 8:33 ] Play Now | Play in Popup | Download (1641)
Link to this post!
Filed Under: Excel Forms, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , , ,
October 18, 2010 Leave a Comment

Create User Friendly Excel Forms Using Spin Button Controls

An Excel Form is great way to distribute an Excel worksheet to your customers or field representatives. Most of the time, you do not want them to type values into a cell. You want to make this Form as “user friendly” as possible! You want to add “Spin Buttons” to your form, so that the user can simply click to change cell values up or down in the increments that you want to display.

A perfect example: A Mortgage Loan Scenario where – by clicking the Spin Buttons – your client can see what their monthly payment will be. This is my second lesson covering Excel’s Form Control Tools. Watch as I demonstrate how to “work around” the Form Controls limits of Maximum Amount for a Spin Button (30,000) and also how to increment interest rates by 1/4 of a Percent.

From personal observation, I know that many people start to create an Excel Form and then just give up in frustration. I did too – in the beginning! Watch me demonstrate how to avoid the traps of the “got’cha” steps in this short Excel Training Video.

Check out the great tips that I offer on my DVD, “The 50 Best Tips for Excel 2007.” Click here to start shopping.

Learn how you can “Master Excel in Minutes – Not Months!”

Create User Friendly Excel Forms with Spin Button Controls [ 8:54 ] Play Now | Play in Popup | Download (1298)
Link to this post!
Filed Under: Excel Financial Tools, Excel Forms, Excel Functions, Excel Training, Podcast Tips & Timesavers Tagged: , , , , ,
October 16, 2010 Leave a Comment

How to Add a Check Box Control to an Excel Form

Judging by the comments that I get from many clients, adding a “Check Box Control” to and Excel Form is not as easy as it appears at first glance.

And, I agree! I, too, struggled with adding Form Controls in Excel. Most of the explanations that I read seemed to add more confusion than clarification!

Spin Buttons in Excel

Spin Button Controls

That is why I created this Excel Training Video: to  lead you through the process; to save you some time and to ease your frustration.

Key Points to Remember:

  1. In Excel 2007, be sure to display the “Developer Tab” on the Ribbon in order to to Insert any Form Control.
  2. Choose the Controls in the “Forms Controls” and NOT in the Active X Controls.
  3. After you “draw” the Control Box on your Form, be sure to select a “Cell Link” to enable the Check Box Control.
  4. Remember that a Check Box Control can only be applied when the result of the Formula or Option is a Logical Value – either a result of  “True or False.”

Don’t be scared off! I find that this is an Excel concept that is better presented “visually.” I struggled for some time when I “read” how to add form controls.

So … I welcome your thoughts – “True or False.” Did I make this easy for you to understand?

Please feel free to add your comments below!

Watch My Video on YouTube

Follow this link to watch my tutorial on my YouTube Channel – DannyRocksExcels

Watch Video Now

 

Master Excel in Minutes Training Resources

I have created a series of extended length Excel Training Videos. I invite you to visit my secure online shopping site to:

Learn how to “Master Excel in Minutes – Not Months!”

How to Add a Check Box Control to an Excel Form [ 10:15 ] Play Now | Play in Popup | Download (1315)
Link to this post!
Filed Under: COUNTIF and SUMIF Functions, Excel Forms, Excel Training, Most Viewed Excel Videos, Podcast Tips & Timesavers, YouTube Videos by Danny Rocks Tagged: , , , , , , , ,