Jump to content


Start a free course now!

Most Liked Content


#25 Do you have similar courses for Word, PowerPoint and other applications?

Posted by TheSmartMethod on 02 September 2013 - 03:27 PM

The only Smart Method courses that have been published as books/videos so far are:

 

• Learn Access 2003 VBA With The Smart Method ISBN-13: 978-0955459900

• Learn Excel 2007 Essential Skills with the Smart Method ISBN-13: 978-0955459924

• Learn Excel 2007 Essential Skills with the Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2007 Expert Skills with The Smart MethodISBN-13: 978-0955459931

• Learn Excel 2007 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459979

• Learn Excel 2010 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Expert Skills with The Smart Method ISBN-13: 978-0-9554599-8-6

• Learn Excel 2010 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Essential Skills with The Smart Method ISBN:  978-1-909253-06-3

• Learn Excel 2013 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Expert Skills with The Smart Method ISBN:  978-1-909253-07-0

• Learn Excel 2016 Essential Skills with The Smart Method ISBN: 978-1-909253-08-7

• Learn Excel 2016 Expert Skills with The Smart Method ISBN: 978-1-909253-09-4

Learn Excel 2016 for Mac Essential Skills with The Smart Method ISBN: 978-1-909253-11-7

Learn Excel 2016 for Mac Expert Skills with The Smart Method ISBN: 978-1-909253-12-4

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459962

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method ISBN-13:978-0-9554599-9-3

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Essential Skills with The Smart Method ISBN-13: 978-1-909253-04-9

• Learn ASP.NET 4.5, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method ISBN-13: 978-1-909253-05-6

• Learn ASP.NET 4.5, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

 

We're currently working on an (almost completed) VBA book  The VBA book will teach the Visual Basic for Applications (VBA) programming language which is generic to Office 2013 and not specific to Excel or any other application. 

 

Eventually Mike Smart will also write up his Outlook, Access, Word, Project, Power Point… and other courses as books and videos but it will take many years to complete them all. The Smart Method are currently exploring ways of increasing productivity by employing proof-readers,  testers and video editors to free more of Mike's time for course design and recording.  




#927 Do you offer any training for Word

Posted by Prasanna on 17 April 2014 - 06:16 AM

Hi

Is there any training for Microsoft Word. This training is best in the world for Excel. If you have training for Microsoft Word such WordCentral.Com, i am very happy to get this one.

 

 

Regards

Prasnana




#26 Do you have an Excel VBA (Visual Basic for Applications) course available?

Posted by TheSmartMethod on 02 September 2013 - 03:31 PM

VBA is not an Excel skill but a programming language that can be used (amongst other things) to automate Excel and other applications. The Smart Method have run VBA classroom courses (in the context of both the Access and Excel object models) for over ten years.

 

After many years of classroom courses for some of the world’s largest companies it has become apparent that very, very few business users actually need Visual Basic programming skills. In most business scenarios Excel is more than capable of providing every corporate need by correct use of its in-built functionality.

 

We've often been asked to provide a VBA classroom training course but then changed the course to Expert Skills on the day. This has happened because when delegates describe the business problems that they think VBA will help them solve, the solution is easy to implement by correctly understanding Excel’s advanced functionality.

 

If you have a specific business problem that you think that you need VBA to solve why not post it on our support forum.  One of our Excel Experts will, in almost all cases, be able to provide a solution that does not involve bespoke programming.



#85 Forum Rules - Please read before posting

Posted by TheSmartMethod on 03 September 2013 - 01:03 PM

Forum Rules

 

What happens if you break the rules?

The moderator will remove or edit your post and e-mail you explaining why your post has been edited/removed. 

In extreme cases you may be banned (or suspended) from making further forum posts.

The interpretation and application of “the rules” is done by forum moderators.  Their decision is final.

 

The Rules

There are only four simple rules:

 

Rule #1 – Be Nice

If you are a nice person you don’t really even need to read this part!

  • All posts must remain polite and respectful. 
  • User names that have the potential to cause offence to any other user may not be used.
  • Images that have the potential to cause offence to any other user may not be used as avatars.
  • Spam is prohibited.  Spam means the promotion of products or other web sites rather than a genuine contribution towards solving the problem being discussed.  Signatures may not include links to third party websites.
  • You may not use this board to offer solutions in exchange for money.
  • Posts may not promote any illegal activity (such as by-passing copyright protection measures).

Rule #2 – Make the forums as useful as possible

  • Posts must be made in English. 
  • This forum is about using Excel rather than extending it with the VBA programming language.  Suggested solutions to problems must use standard Excel features (including recorded macros) and not be implemented using VBA program code.
  • Do not suggest that a user moves a discussion to private messages or another forum. 
  • Do not post duplicate questions.  
  • If you think another member has broken one of the forum rules use the "Report" button underneath the post to refer it to a moderator. Do not refer to the violation within the discussion thread itself. 
  • The moderators may correct your posts for typos or grammatical errors if this will make them easier for others to read. 

Rule #3 – Do not link to other sites

Your post may still be on our forum in several years’ time. The Internet is constantly changing and if you include links in your posts it is probable that the linked data will change (or disappear completely) in the future. 

 

We can only be sure that links to pages and posts on our own site will always work correctly.

For this reason you can only post hyperlinks to:

  • Any ExcelCentral.com video or e-book lesson
  • Any other thread or posting in the Excel Central forum.

Apart from these two types of links, information should always be provided within the post and not by a link to another resource.

 

Rule #4 – Stay focused

  • Keep scope as narrow as possible.  Don’t ask questions such as: How do I create an accounting system using Excel
  • Use concise and accurate thread titles.  Don’t create threads with abstract names such as Please Help, Need Advice etc.
  • Keep answers on topic.  Don’t post a new unrelated problem on a focused thread.  Start a new thread instead.
  • Do not post one-liners that add no value to the discussion such as OK, I agree etc.



#793 Length of Courses

Posted by Mike on 14 March 2014 - 12:28 AM

Hi

 

How long it takes you to compete either course is (of course) extremely variable. A lot depends upon whether you are completely new to Excel or whether there is a degree of past exposure.

 

Most users of Excel do not need Expert Skills.  To work in the average office job you will usually be hugely in advance of the average other users if you "only" have the skills described in the "Essential Skills" course. 

 

The books are based upon Smart Method classroom courses.  With well motivated and above average intelligence adult course attendees, with zero previous Excel experience, we have trained thousands of Excel users (without exception) to "Essential Skills" standard in one eight hour intensive day.  Unfortunately this is in a classroom environment (and hugely expensive compared to the cost of the book or video course).

 

Just to listen to each Essential Skills video lesson will take around eight hours.  In a (non classroom) environment, using only books or videos as the learning material, I would guess that three days (of intensive attention) would be the maximum needed by most adult learners to master every skill presented.

 

Expert Skills is a little more advanced. 

 

To truly achieve Expert status I ask my corporate clients to allocate two days of intensive classroom training (for users with existing good Excel skills).  In effect most go for one day and it is possible to "cherry pick" different skills to cover those most of relevance to the class in this time frame.  For self study it would be good to double this estimate to 2-4 days depending if you need to know "everything" or a subset of the skills presented.  But once again, you need to ask yourself if you really need Expert Skills (few users really do).

 

Best Regards

 

 

Mike Smart




#55 Do you have similar courses for Word, PowerPoint and other applications?

Posted by TheSmartMethod on 02 September 2013 - 04:34 PM

The only Smart Method courses that have been published as books/videos so far are:

 

• Learn Access 2003 VBA With The Smart Method ISBN-13: 978-0955459900

• Learn Excel 2007 Essential Skills with the Smart Method ISBN-13: 978-0955459924

• Learn Excel 2007 Essential Skills with the Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2007 Expert Skills with The Smart Method ISBN-13: 978-0955459931

• Learn Excel 2007 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459979

• Learn Excel 2010 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2010 Expert Skills with The Smart Method ISBN-13: 978-0-9554599-8-6

• Learn Excel 2010 Expert Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Essential Skills with The Smart Method ISBN:  978-1-909253-06-3

• Learn Excel 2013 Essential Skills with The Smart Method: Video Course (only available online at the ExcelCentral.com web site).

• Learn Excel 2013 Expert Skills with The Smart Method ISBN:  978-1-909253-07-0

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method ISBN-13: 978-0955459962

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Essential Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method ISBN-13:978-0-9554599-9-3

• Learn ASP.NET 4.0, C# and Visual Studio 2010 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Essential Skills with The Smart Method ISBN-13: 978-1-909253-04-9

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method ISBN-13: 978-1-909253-05-6

• Learn ASP.NET 4.5, C# and Visual Studio 2012 Expert Skills with The Smart Method Video Course (only available online at the ASPNetCentral.com web site).

 

Eventually Mike Smart will write up his Access, Word, Project, PowerPoint… and other courses as books and videos but it will take many years to complete them all. The Smart Method are currently exploring ways of increasing productivity by employing proof-readers, testers and video editors to free more of Mike’s time for course design, writing and recording.




#1742 Preparing for the Microsoft MOS Expert Exam 77-427 and 77-428 using The Smart...

Posted by Mike on 04 July 2015 - 09:56 AM

Dear Ishaq

 

The new MOS exam format (for Office 2013) doesn't seek to test any individual Excel skills but, instead, poses a real-world business problem that the student must solve.  This is a very welcome change from earlier exams for which it was impossible to "guess" which skills Microsoft actually were going to test (due to very loosely defined objectives).

 

This is from the Microsoft site:

 

"The new exam format for MOS 2013 presents a short project that the candidate must complete, using the specifications provided. This creates a real-world testing experience for candidates". 
 
Microsoft advise that Excel Skills are tested in the way that they are taught in our courses -  in the context of solving a real business problem.  Our teaching method -  with every skill taught using an example workbook from the real world of business and commerce - should prove ideal for preparation for your exam.  You'll also learn many more extremely useful skills that go well beyond those included in the MOS "Expert" certification. 
 
If you complete our Expert course I know that you will be able to address any real-world business problem for which Excel is an appropriate tool.  If you look at some of the real-world business problems posed on this forum by professional Excel users,  you'll see that many are extremely complex but that all have been solved using only the skills taught in our course. (The solutions are always cross-referenced back to video and e-book lessons that teach the skills used in the solution) .
 
I'd suggest that you proceed as follows:
 
1/ Work through the 2013 Essential Skills and Expert Skills courses until you can complete all of the exercises at the end of each session without referring back to the book or video lesson.
 
2/ Study the problems posed on this forum until you thoroughly understand the solutions we have provided.
 
If you do take (and hopefully pass) the test it would be very interesting if you could post details of the project you were asked to complete and we'll provide a worked example of the solution by referring to the relevant lessons in the Essentials and Expert course.
 
I should also mention that  you will receive our own certification of course completion at the end of our course that you can print out and e-mail directly from your control panel (available when you log-in).    
 
Best Regards

 

Mike Smart




#949 Advice for Intro College Course

Posted by Mike on 09 May 2014 - 06:31 PM

Dear John

 

Pivot Tables are most definitely an Expert skill.  I’ve taught Excel classes to corporate executives for over ten years and it is rare to find any Excel business users that understand Pivot tables.   This skill is even rare amongst professional accountants.

 

Perhaps the first step would be to establish whether your students really need Pivot Table skills.  These skills are mainly used to analyze de-normalized data extracts from relational databases rather than to analyse data that has actually been input into Excel.

 

Assuming that your students really do need pivot table skills they should also be able to understand tables.  Since Excel 2007 was released,  tables (rather than ranges) are best practice for the data source underpinning a pivot table.  Tables are covered in Session One: Tables and Ranges (Expert Skills) 

 

As a minimum it would be possible to proceed directly to Session Five: Pivot Tables of Expert Skills after digesting Session One: Tables and Ranges of Expert Skills but many of the functions taught in Session Three: Advanced Functions of Expert Skills are often needed in order to prepare the data table (when extracted from a relational source) prior to creating a pivot table from it.

 

Excel 2013 has also added a new spin to Pivot Tables with the introduction of the OLAP Pivot Table and the data model.  There’s an entirely new session in 2013 Expert Skills devoted to this subject Session Six: The Data Model, OLAP, MDX and BI that also covers relational database theory (essential to the understanding of OLAP Pivot Tables).  I’d be surprised if even one percent of Excel business users, now and in the future, will ever have any understanding of OLAP (though it is tremendously useful when working with relational database data sources).

 

When I teach my Excel classroom courses it is rare to have a student who has no prior exposure to Excel.  For this reason it is usually possible to “cherry pick” lessons from different sessions based upon existing skills and the need for certain features.   This approach only works with small classes with similar prior experience (my maximum class size is six).

 

Essential Skills is designed to give students excellent Excel Skills (skills that will put them ahead of the average office worker and an employer’s expectations for “good” Excel skills).  Expert Skills covers just about every Excel feature and also provides the skills to use any other feature that isn’t explicitly covered.  These are skills that are not needed or expected in most occupations.

 

Best Regards

 

 

Mike Smart




#843 Conditional Formatting

Posted by Jonathan on 19 March 2014 - 10:54 PM

Hi Phil,

 

It's quite easy to achieve this if you have an understanding of how dates and times work in Excel.  The following formula will get the number of hours between the current date and time and the time of the complaint (assuming this is in cell A6):

=(NOW()-A6)*24

 

To understand why this formula works, see the video lesson: Expert Skills Lesson 3-7: Understand date serial numbers.

 

Now that you have the number of hours that you need, it's a simple matter of applying conditional formatting and customizing it to display the "traffic lights" at the values you need.

Customizing conditional formatting is covered in depth in the video lesson: Essential Skills Lesson 4-15: Use simple Conditional Formatting.

 

I have attached an example spreadsheet with the formulas and conditional formatting applied:

 

Attached File  TrafficLights.xlsx   9.56KB   270 downloads

trafficlights.png

 

If you need any more assistance with this, please feel free to reply.




#719 VLOOKUP using text string?

Posted by Jonathan on 18 February 2014 - 04:53 PM

The problem is that VLOOKUP always looks in the first column of the range that you specify.

 

Your formula is:

=VLOOKUP(G2,'[Sales First Quarter 2008-3.xlsx]January'!$A:$J,9,FALSE)

 

The problem here is that you are telling your VLOOKUP function to look at columns A:J in the Sales First Quarter 2008-3 workbook.  VLOOKUP will look in the first column of the table to try to find the product name, so it is trying to find the product name in column A.

 

For a more in-depth explanation of how VLOOKUP works, I recommend viewing Expert Skills Lesson 3-22: Use a VLOOKUP function for an exact lookup.

 

To get this to work, your formula should be something like:

=VLOOKUP(G2,'[Sales First Quarter 2008-3.xlsx]January'!$G:$I,3,FALSE)

 

This formula looks in column G for the product name and then returns the unit price from column I.

 

When using VLOOKUP, it's usually better to create a table that's specifically designed for lookups.  I've attached a working example:

 

Attached File  Sales First Quarter 2008-3.xlsx   34.27KB   1264 downloads

xlookup1.png

 

Attached File  Sales First Quarter 2008-Prices.xlsx   10.02KB   1003 downloads

xlookup2.png

 

If you continue to have problems, please feel free to reply.




#2734 Readings Scores as dates or Millennial code

Posted by Jonathan on 19 July 2017 - 07:34 PM

Hi JPK,

 

If your data originates in a 1997-2003 workbook (a .xls file rather than a .xlsx file), it is likely that the scores had already been converted into dates in that workbook. If you have access to a copy of the data before this happened, you should return to that copy and re-import it into Excel using the solution above.

 

If all you have is a .xls file with some of the scores converted into dates, your only possible solution is to find a way to convert them from dates back into textual scores. This is somewhat difficult, but possible.

 

In this example, I'll take cell M11 from your original workbook, showing the date 17-Oct. This represents the score 17-10. Realizing this, you can see that the solution will require extracting the days and months as 2-digit numbers.

 

You could do this using the DAY and MONTH functions, but the TEXT function offers a convenient alternative. The following formula will extract the days and months and display them as text in a dd-mm format:

=TEXT(M11,"dd-mm")

 

dd-mm is a custom format code. You can see a complete explanation of date formatting codes in the video lesson: Essential Skills Lesson 4-1 Format dates.

For a walkthrough of the TEXT function, see the video tutorial: Expert Skills Lesson 3-19 Use the TEXT function to format numerical values as strings.

 

Surprisingly, this formula is also capable of correctly interpreting your other scores such as 33-18, even though there is of course no such thing as a month with 33 days. By filling down with this formula, you should be able to convert all of your scores back into text.

 

Before splitting your text using Text to Columns, you will first have to use Paste Values to remove the formulas and replace them with the actual values that were calculated.

 

You can see how to use Paste Values in the video lesson: Essential Skills Lesson 3-5 Use Paste Values and increase or decrease decimal places displayed.

 

I have attached a copy of your workbook with the scores fully converted and split, including the formulas:

 

Attached File  ConvertedDates.xlsx   14.42KB   2 downloads

ConvertedDates.png

 

I hope this is the solution that you needed.

 

If you are still having problems and need to attach your example, you should be able to do this by saving it in the standard Excel Workbook format. You can see how to do this in the video lesson: Essential Skills Lesson 1-7 Understand common file formats.




#2300 Leaving blank cells empty when some cells have 0.00 as a value

Posted by Jonathan on 14 April 2016 - 12:13 PM

Hi gmedley,

 

Excel usually doesn't distinguish between a blank cell and one containing a zero value, but when there's a special need to tell the difference you can use the ISBLANK function.

 

ISBLANK will return TRUE if a cell is empty, but will return FALSE if it contains a zero (or any other value).

 

Knowing this, you can create a formula-based conditional format that will check whether cells are blank and set the colors accordingly.

You can see how to create a formula-based conditional format in the video lesson: Essential Skills Lesson 4-18 Create a formula driven conditional format.

 

Note that you will have to make sure that your conditional formatting rules are processed in the correct order. If the "yellow" rule is placed before the "blank" rule it will take precedence and make the blank cells yellow (because it still considers them to have a zero value).

You can see more about setting the order of conditional formatting rules in the video tutorial: Essential Skills Lesson 4-16 Manage multiple conditional formats using the Rules Manager.

 

I have also attached an example workbook showing this in practice:

 

Attached File  ISBLANKFormat.xlsx   9.61KB   63 downloads

ISBLANKFormat.png

 

I hope you're now able to create the workbook you need, but please feel free to reply if you need any more help with this.




#2218 data from one worksheet into another worksheet, problem

Posted by Jonathan on 26 February 2016 - 07:26 PM

Hi ARSG,

 

I wasn't able to recreate this exact problem, but I think I can see what is causing it.

 

Until you press the <Enter> key, Excel remains in 'editing mode'. If it's still in 'editing mode' when you switch worksheets, it automatically changes the cell reference that's being edited to the sheet that you click.

 

The solution is to press the <Enter> key after step iv. You'll then be returned to sheet2 with the correct formula entered and no duplication.

 

If you need to do something else with the value (such as add, subtract, multiply, etc.), you just need to type the correct operator before switching tabs. As long as you have typed an operator, Excel will know that you are going to enter a new cell reference and won't modify the one you've already entered.

 

You can see more about cross-worksheet formulas in the video tutorial: Essential Skills Lesson 6-6 Create cross worksheet formulas.

 

I hope this is the solution to your problem, but please feel free to reply if you need any more help.




#2166 Is there a way to concatenate multiple cell formulas (instead of resulting va...

Posted by Jonathan on 29 January 2016 - 07:38 PM

Hi bennyb,

 

We can't offer VBA support on these forums, but there is an undocumented way to do this without using VBA.

 

There is a 'secret' EVALUATE function that can only be accessed by creating a range name. If you're not sure how to do this, you can find an explanation in the video lesson: Expert Skills Lesson 4-2 Manually create single cell range names and named constants.

 

For example, to execute a formula that is stored as text in cell A1, you could define the following range name:

 

Evaluate.png

 

The EVALUATE function will not work if you try entering it as a cell formula. It can only be accessed by creating a range name in this way.

 

After creating the range name, you can extract the result with the formula: =EvaluateResult

 

Note that, even though this doesn't appear to be a macro, it will actually cause Excel to run a macro 'behind the scenes'. This means that it will only work within a macro-enabled workbook.

 

I have attached a working example:

 

Attached File  Evaluate.xlsm   9.37KB   55 downloads

Evaluate2.png




#1812 Very Specific Macro Question

Posted by Jonathan on 14 August 2015 - 05:20 PM

Hi Shawn,

 

I have taken a look and it looks like the Table is indeed what's causing the trouble, specifically the command to insert a new table row doesn't seem compatible with relative references. This seems to be a bug in Excel rather than anything you're doing wrong.

 

You can work around this by inserting an entire worksheet row instead of just inserting cells into the Table. You can do this by right-clicking on the row number and then clicking Insert from the shortcut menu.

 

Inserting rows is also covered in the video lesson: Essential Skills Lesson 3-1 Insert and delete rows and columns.

 

I have attached another example workbook with the macro working on a Table:

 

Attached File  RelativeMacro.xlsm   16.53KB   104 downloads

RelativeMacro2.png

 

You can also see more about Tables in the video lesson: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.




#1653 Using VLOOKUP, if Column 5 is blank, get value from Column 6

Posted by Jonathan on 08 May 2015 - 11:51 AM

Hi Simon,

 

I think you can achieve this using an IF function rather than IFERROR.

 

You will need a formula that looks something like this:

=IF(VLOOKUP([@CustomerID],Customer,3)="",VLOOKUP([@CustomerID],Customer,4),VLOOKUP([@CustomerID],Customer,3))

 

This first checks whether your VLOOKUP for column number 3 returns a blank, then returns column number 4 if it does and returns column number 3 otherwise.

 

For a video walkthrough of the IF function, see: Expert Skills Lesson 3-5 Use the IF logic function.

For a video walkthrough of VLOOKUP, see: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

You'll also notice that my formulas are using structured table references like [@CustomerID]. You can use cell references in place of these, but structured table references do help to make formulas easier to understand and maintain.

 

You can see an introduction to tables and structured table references in the following video lessons:

Expert Skills Lesson 1-11 Convert a range into a table and add a total row

Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference

 

I have attached an example workbook showing this formula in action:

 

Attached File  VLOOKUPIF.xlsx   10.08KB   542 downloads

VLOOKUPIF.png

 

I hope this is what you were looking for, but please feel free to reply if you need any more assistance.




#1313 Data validation dropdown list disappeared

Posted by Jonathan on 08 December 2014 - 08:04 PM

Hi Yoges,

 

When you say "it didn't let me enter a value", what do you mean?  Are you unable to enter values anywhere in the worksheet?

 

If you're unable to edit the worksheet, it seems likely that it has been protected, and this is the reason you're not able to change it.  For instructions on how to unprotect a worksheet, see the video lesson: Expert Skills Lesson 7-11 Restrict the cells users are allowed to change.

 

If the worksheet isn't protected and you simply can't edit the cells that you have applied validation to, it sounds like your validation rules have gone wrong somehow.  You can check the validations that are in place by clicking: Data > Data Tools > Data Validation.

 

For more on how to apply and edit validation rules, see the video lesson: Expert Skills Lesson 2-7 Validate numerical data.

For a walkthrough of applying 'drop down' list validation, see the video lesson: Expert Skills Lesson 2-11 Add a table-based dynamic list validation.

 

I hope this helps and you're able to solve the problem, but if not please feel free to attach a copy of the workbook and I will take a look at it.




#113 Adding a Second Combo Box to a Combo Box

Posted by Jonathan on 04 September 2013 - 06:11 PM

Hi Owls1982,
 
I don't think comboboxes can be selectively hidden and shown using Excel's built-in features. 
 
Of course, a programmer could add this capability by writing custom VBA (Visual Basic for Applications) program code. 
 
However, you can solve this problem without using comboboxes (or program code) by using list validation on your cells instead. List validations add a control that resembles a combo box to worksheet cells.
 
You can then use the INDIRECT function within your validation to link a two list validations to produce the functionality you describe.
 
This is a complicated technique that requires knowledge of list validation, so you might want to start by reviewing Expert Skills Lesson 2-11: Add a table-based dynamic list validation.  Table-based list validation lets you make cells act like comboboxes and display a list of items from a specific area on the page.
 
To learn how to link two validation drop-down menus together, take a look at: Expert Skills Lesson 4-8: Create two linked drop-down lists using range names. The technique works by creating range names that match each entry in your first 'drop down menu' and point to some cells containing the items to be displayed in your second 'drop down menu'.
 
If all of this is completely new to you, you might want to start at the beginning of session 4 and work your way through to lesson 4-8.  The earlier lessons in session 4 will teach you about range names and the INDIRECT function, which will help a lot when trying to implement this.




#11 Forum Rules - Please read before posting

Posted by TheSmartMethod on 02 September 2013 - 02:06 PM

Forum Rules

 

What happens if you break the rules?

The moderator will remove or edit your post and e-mail you explaining why your post has been edited/removed. 

In extreme cases you may be banned (or suspended) from making further forum posts.

The interpretation and application of “the rules” is done by forum moderators.  Their decision is final.

 

The Rules

There are only four simple rules:

 

Rule #1 – Be Nice

If you are a nice person you don’t really even need to read this part!

  • All posts must remain polite and respectful. 
  • User names that have the potential to cause offence to any other user may not be used.
  • Images that have the potential to cause offence to any other user may not be used as avatars.
  • Spam is prohibited.  Spam means the promotion of products or other web sites rather than a genuine contribution towards solving the problem being discussed.  Signatures may not include links to third party websites.
  • You may not use this board to offer solutions in exchange for money.
  • Posts may not promote any illegal activity (such as by-passing copyright protection measures).

Rule #2 – Make the forums as useful as possible

  • Posts must be made in English. 
  • This forum is about using Excel rather than extending it with the VBA programming language.  Suggested solutions to problems must use standard Excel features (including recorded macros) and not be implemented using VBA program code.
  • Do not suggest that a user moves a discussion to private messages or another forum. 
  • Do not post duplicate questions.  
  • If you think another member has broken one of the forum rules use the "Report" button underneath the post to refer it to a moderator. Do not refer to the violation within the discussion thread itself. 
  • The moderators may correct your posts for typos or grammatical errors if this will make them easier for others to read. 

Rule #3 – Do not link to other sites

Your post may still be on our forum in several years’ time. The Internet is constantly changing and if you include links in your posts it is probable that the linked data will change (or disappear completely) in the future. 

 

We can only be sure that links to pages and posts on our own site will always work correctly.

For this reason you can only post hyperlinks to:

  • Any ExcelCentral.com video or e-book lesson
  • Any other thread or posting in the Excel Central forum.

Apart from these two types of links, information should always be provided within the post and not by a link to another resource.

 

Rule #4 – Stay focused

  • Keep scope as narrow as possible.  Don’t ask questions such as: How do I create an accounting system using Excel
  • Use concise and accurate thread titles.  Don’t create threads with abstract names such as Please Help, Need Advice etc.
  • Keep answers on topic.  Don’t post a new unrelated problem on a focused thread.  Start a new thread instead.
  • Do not post one-liners that add no value to the discussion such as OK, I agree etc.



#1761 End of session exercise

Posted by Judelewis64 on 15 July 2015 - 03:50 PM

I cannot see how to complete the end of session exercise, as the pdf version cannot be altered or resaved in another format.


  • KSR likes this