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

Excel Construction Kit #1: Calendar and Year Planner ISBN: 978-1-909253-10-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 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.  




#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.



#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




#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




#86 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.



#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.




#995 How to attach a workbook to a question

Posted by Jonathan on 09 June 2014 - 07:04 PM

When asking questions on this forum, it's often helpful to attach a copy of your workbook.  This makes it easier for us to understand the question and allows us to use your own work as an example.

 

Quite a few people have had issues attaching files, so here are some instructions on how to do it:

  1. If you are replying to a post, click More Reply Options when replying.

    MoreReplyOptions.png
     
  2. Click Browse.

    attachfiles2.png
     
  3. Select the file that you want to upload and click Open.
     
  4. Click Attach This FileIf you do not do this, the file will not be attached.

    attachfiles3.png

The file should then be attached and appear in a box below your post.

 

attachfiles4.png

 

You can click Add to Post if you want your attachment to appear in a specific place in your post, but otherwise it will automatically appear at the bottom.

 

If there's a very long delay after clicking Attach This File, it is probably because the file is very big and you may need to be patient.  If a small file seems to be taking a very long time, try clicking Attach This File again.

 

Important Note: Files cannot be uploaded if the file name contains an apostrophe. For example, Jonathan's.xlsx could not be uploaded. To solve this, simply remove any apostrophes from the file name. Jonathans.xlsx would upload without any problems.




#895 Displaying years in chart axis labels

Posted by Jonathan on 04 April 2014 - 02:34 PM

Hi Richard,

 

I see the problem now.  When Excel sees your years, it recognizes them as numbers instead of text and tries to include them in the chart instead of using them as labels:

 

badchart3.png

 

You can rectify this by creating your chart, then right-clicking it and clicking Select Data from the shortcut menu, then setting the Series and Axis Labels settings appropriately.

For an in-depth look at how this is done, see the PDF and video lesson: Essential Skills Lesson 5-14: Change source data using the Select Data Source dialog tools.

 

The correct settings will look something like this:

 

badchart4.png

 

If you have trouble doing this, or don't want to go to the bother of changing the settings every time you create a chart, you can work around this problem by entering the dates as text instead of numbers.  Excel will then automatically use them as labels when you create a chart.

 

Entering numbers as text is covered in the PDF and video lesson: Essential Skills Lesson 2-1: Enter text and numbers into a worksheet.

 

I have attached my example spreadsheet with the years formatted as text.

 

Attached File  expenditurebyyear.xlsx   13.07KB   343 downloads

expenditurebyyear.png

 

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




#462 Remove gaps between bars in an Excel chart?

Posted by Jonathan on 25 October 2013 - 01:27 PM

To do this, first create your chart, then right-click on one of the bars and click Format Data Series:

gaps1.png

 

You should then see a setting called Gap Width.  If you set this to zero, the gaps will disappear.

gaps2.png

 

Note that this may appear differently depending on the version of Excel that you are using, but the setting should still be available and should still work correctly.

gaps3.png

 

For more information about formatting the different parts of charts, see Essential Skills Lesson 5-7 Manually format a chart element.

 

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




#2889 drop down lists and Indirect function right direction?

Posted by Jonathan on 28 December 2017 - 02:47 PM

Hi diyrednef,

 

As is often the case, there are a few different ways that you could do this. The simplest is very similar to what you already have - splitting each wind farm into a separate table and then using the INDIRECT function to retrieve the appropriate table.

 

I have attached a working example:

 

Attached File  Turbines.xlsx   16.19KB   39 downloads

Turbines.png

 

This example uses structured table references and the INDIRECT function.

You can see more about creating tables in: Expert Skills Lesson 1-11 Convert a range into a table and add a total row.

Structured table references are covered in: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

Using the INDIRECT function to link dropdown list validations together is shown in: Expert Skills Lesson 4-8 Create two linked drop-down lists using range names.

 

An alternative way to achieve this would be to use a single table with a complex OFFSET function that retrieves records for the selected wind farm. You can see an example of this technique in: Expert Skills Lesson 4-6 Create dynamic formula-based range names using the OFFSET function.

 

Finally, in the latest version of Excel 2016 this could be achieved using a single table and the Get & Transform tool, which now allows you to retrieve records from tables according to parameters. The Get & Transform tool is covered in great depth in Session 11: Create Get & Transform queries.

 

I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.




#2776 Absolute Reference Cells - S3Q5

Posted by Jonathan on 22 August 2017 - 10:19 AM

Hi ajbozdar,

 

The reason it's important to use an absolute reference is so that the formula can be properly filled down in Question 6.

 

You're right that using $E$3 or E$3 would both be correct answers in this case, since the formula only needs to be filled down and is never filled across. However, since there is no possibility that you would ever want Excel to adjust references to cell E3 it's still better to use $E$3 as it eliminates any possibility of a mistake.

 

Another advantage of the $E$3 approach is that you can apply it with a single keystroke by simply pressing <F4> after selecting the cell while building your formula.

 

You can find a complete explanation of absolute and relative cell references in the video lesson: Essential Skills Lesson 3-12 Understand absolute and relative cell references.

 

I hope this is the answer you were looking for, but please feel free to reply if you have any more questions about this.




#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   118 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.




#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   154 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.




#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


#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   949 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.




#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.