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.  




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




#920 "slidepage" doesn't work

Posted by Guest on 15 April 2014 - 07:54 PM

In the exercises (quizzes), I am told to slide page to the left. There is a right arrow shown. There is no way of causing the page to slide over. Excel 2007.




#787 Formula for date error

Posted by Jonathan on 12 March 2014 - 10:20 PM

Hi Paula,

 

Let me first clarify what I think you want to do.

 

monthname.png

 

The month of the date of birth is in cell C2 and is currently 8.

 

For each of the cells F11:Q11, you want to add 2 to the value if the month is the same as the value in C2 (in this case, August), and only 1 to the value if it is not.

 

You can do this by using the IF function, but you will find that the month names in cells F10:Q10 don't match the number in cell C2.  You could get around this by changing cell C2 to Aug instead of 8, or you could use the MONTH function to extract the numbers from each heading.

 

I'll use the MONTH function in my example.  For a full explanation of the MONTH function, see the PDF and video lesson: Expert Skills Lesson 3-8: Understand common date functions.

 

Here's how the IF formula in cell F11 should look:

=E11+IF(MONTH(1&F10)=$C$2,2,1)

 

If you're having trouble understanding the IF function, you can find more information in the PDF and video lesson: Expert Skills Lesson 3-5: Use the IF logic function.

 

You'll also notice that I have used the absolute reference $C$2 so that this formula can be easily filled across the rest of the months.  For a full explanation of absolute references, see the PDF and video lesson: Essential Skills Lesson 3-12: Understand absolute and relative cell references.

 

The MONTH function looks a little strange, as I've used 1&F10.  This is necessary in order for the MONTH function to recognize (for example) Jan as a date and return the number 1.

 

I have attached a copy of your spreadsheet with the formulas applied:

 

Attached File  Numerology.xlsx   10.68KB   268 downloads

numerology.png

 

Please feel free to reply if you need any more assistance with this.




#637 Slicer is missing

Posted by Jonathan on 13 January 2014 - 05:57 PM

Hi Mr Pointer,

 

The most likely explanation is that the workbook was created in an older version of Excel that does not support the Slicers feature (Excel 2003 or earlier).

 

Fortunately, you can very easily convert your workbook to the latest version of Excel by simply clicking File > Save As and making sure to select the Excel Workbook (*.xlsx) format.

 

For instructions on how to save a workbook in a different format, see Essential Skills Lesson 1-6 Save a workbook to a local file .

For a more detailed look at the different formats supported by Excel, see Essential Skills Lesson 1-7 Understand common file formats .

 

If this doesn't solve your problem, please reply to this post and I will investigate further.




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




#2584 How to transfer Invoices into summary sheet automatically

Posted by Jonathan on 27 December 2016 - 04:38 PM

Hi Masud,

 

From your description, it sounds like you might find it easier to store all of your invoices in a single workbook and use a pivot table to create the summaries you need. You can then refresh the pivot table whenever you add more invoices.

 

You can see how to create a pivot table in the video lesson: Expert Skills Lesson 5-1 Create a pivot table and see a lot more about pivot tables in the rest of Expert Skills Session 5.

 

Regarding invoice numbers, if your numbers are numeric you should be able to do this by using a simple formula like =A1+1 (where A1 is the location of the previous invoice number). If there is a combination of letters and numbers you should be able to achieve this by using the concatenation operator, and possible the LEFT, RIGHT and MID functions. You can see how to use all of these in the following video tutorials:

Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator

Expert Skills Lesson 3-20 Extract text from fixed width strings using the LEFT, RIGHT and MID functions

 

I hope this is helpful information, but please feel free to reply if you have any further questions.




#2458 Retrieving the month from a European Style week no

Posted by Jonathan on 09 August 2016 - 01:09 PM

Hi Karcy,

 

Your formula is returning the correct dates for each week number according to the WEEKNUM function, but you're likely finding that it is returning December for the first week of 2015 because the Monday of that week occurred on the 29th of December 2014. This is technically correct, so you may need to work around this with an IF function if you want it to always return January for the first week.

 

You can see an in-depth explanation of how to use the IF function in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

 

I have attached an example workbook using your formula, along with an IF function applied to return the desired month. It also shows your formula applied to all weeks in 2015 and 2016, proving that the result always matches the result of the WEEKNUM function:

 

Attached File  ReverseWEEKNUM.xlsx   14.37KB   72 downloads

ReverseWEEKNUM.png

 

You might notice that I've also removed the CHOOSE function from your formula and replaced it with a custom format (mmm) that simply displays the Month part of the date. You can see an explanation of custom formats and how to apply them in the video tutorial: Essential Skills Lesson 4-4 Create custom number formats.

 

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




#2186 Validate negative values

Posted by LT27 on 08 February 2016 - 12:39 PM

Good day!

 

Hope all is well with you! I want to create a formula when the values are negative to put 0 and change the spend type from Cost Savings to Cost Avoidances.

 

Any help will be greatly appreciated.

 

Thank you so much!

Attached Files




#2131 Compare 2 values in order to get one result

Posted by Jonathan on 15 January 2016 - 08:28 PM

Hi Luz,

 

I have taken a look at the Lot Pricing tab, and things are a lot clearer to me now.

 

I can see that you have created columns named Supplier1, Supplier2, Supplier3 etc. to account for each supplier.

 

Quotes1.png

 

When you see this kind of layout, you know that you're working with 'relational' data. In a true relational database, you'd have a table of Suppliers, a table of Parts, a table of Auctions and a table of Quotes, all linked together with relationships.

 

To properly store this kind of data, you need to restructure your spreadsheet to add a Supplier column instead of having separate columns for each supplier.

You can see an in-depth explanation of relational data and why this is needed in the video lesson: Expert Skills Lesson 6-1 Understand primary and foreign keys.

 

I have re-structured the worksheet to move the supplier names into a Supplier column:

 

Quotes2.png

 

Now that the data is restructured, the next challenge lies in working out which bid is the 'winning' bid for each part number. You can now achieve this quite easily by using a Pivot Table, like this:

 

Quotes3.png

 

For instructions on how to create a pivot table like this one, see the video tutorial: Expert Skills Lesson 5-1 Create a pivot table.

 

This may be the solution in itself, but I've noticed that on your other workbooks you want to see the names of the suppliers for the winning bids and other information. Extracting this information as well is a greater challenge, as Pivot Tables are only intended to display summaries rather than complete sets of data.

 

The only way to achieve this will be to find a way to determine the winning bid for each product within the Lot Pricing table itself, and this is only possible with a very complex formula:

=AGGREGATE(15,6,1/([Part Number]=[@[Part Number]])*[Bid],1)

 

Please feel free to reply if you'd like an explanation of why this formula works, but for the moment I'll just say that it will work out the minimum bid for each part number.

 

Armed with that information, you can quickly determine which of the bids is the winner by checking whether it is the same as the minimum bid.

 

This can be done as simply as:

=[@Bid]=[@[Minimum Bid]]

 

The items in square brackets are 'structured references'. If you're not familiar with these, you can see an explanation in: Expert Skills Lesson 1-17 Name a table and create an automatic structured table reference.

 

Finally, you need to create a key value that will allow you to use VLOOKUP to search for the winning bid for each product code. To do this, I'm going to add a column called 'Key' that concatenates the Part Number and the result of the 'winner' formula above:

=[@[Part Number]]&[@Winner]

 

For more on concatenating with the & operator, see the video lesson: Expert Skills Lesson 3-18 Concatenate strings using the concatenation operator.

 

Here's how the Lot Pricing table looks after restructuring:

 

Quotes4.png

 

Now that you have the Key that you need, you can use VLOOKUP on the Award Values sheet to extract all of the values for each winning bid:

=VLOOKUP(B7&TRUE,Table1,4,FALSE)

 

Notice how I've concatenated TRUE onto the product code to find the winning bid instead of the losing bids.

 

For a more in-depth explanation of VLOOKUP, see the video lesson: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

Of course, you might prefer to simply filter the Lot Pricing table to only show the winning bids. You can see how to do this in: Expert Skills Lesson 1-3 Apply a simple filter to a range.

 

I have attached a copy of your workbook with all of the changes applied:

 

Attached File  Practice 2.xlsx   66.55KB   115 downloads

Practice2.png

 

I hope this is useful and you're able to get the results you need, but please feel free to reply if I can offer any more help.




#2059 Is it possible to immediately display a comment or something on a locked shee...

Posted by Murph on 01 December 2015 - 05:48 PM

Hi Jonathan

 

What I am attempting to do I have attached as a sample, to illustrate the need.

 

What I would like to do is once the user has a choice to enter a Y or N, (I already have the Data Validation routines functioning as you suggested).

But additionally, I would like to pop a separate box or window with a fully detailed description of what they are replying to.

So the first line is a light overview of the question.

 

The popped box would contain full details for example:

Situation1: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Situation2: Not applicable
Situation3: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 
The Data Validation pop boxes have a very limited flexibility it appears on formatting the message.
 
I noticed the Title is presented with a bold font which is nice, however if the user wants to enhance any portion of the actual Input Message, this appears not to be possible.
 
Thanks!!

Attached Files




#1711 coding of numbers like 8/12, 8/32,... with a particular text

Posted by Jonathan on 11 June 2015 - 05:10 PM

Hi Ganga,

 

I think you can achieve what you need using the FIND, LEFT and VLOOKUP functions.

 

I have attached an example workbook showing how this can be done:

Attached File  LEFT-VLOOKUP.xlsx   10.46KB   109 downloads

LEFT-VLOOKUP.png

 

You can see that I have created a lookup table that shows the text to be used for each number and that I have split the formula into 3 steps.

 

In the first step, I use the following formula:

=FIND("/",A4)-1

 

This uses the FIND function to find the position of the / character in the code.

You can see a more detailed explanation of the FIND function in the video lesson: Expert Skills Lesson 3-21 Extract text from delimited strings using the FIND and LEN functions.

 

In step 2, I use this formula:

=LEFT(A4,B4)

 

This uses the LEFT function to extract the number from the code, using the result of the FIND function in step 1 to determine how many characters to return. This is necessary because the numbers can have either 1 or 2 digits.

You see a more in-depth example of the LEFT function in the video lesson: Expert Skills Lesson 3-20 Extract text from fixed width strings using the LEFT, RIGHT and MID functions.

 

Finally, in step 3 I use the formula:

=VLOOKUP(C4,LookupTable,2,FALSE)

 

This uses VLOOKUP to search for the number in the lookup table and return the appropriate text.

Again, you can see a much more detailed explanation of using VLOOKUP in the video lesson: Expert Skills Lesson 3-22 Use a VLOOKUP function for an exact lookup.

 

Finally, I have combined the 3 steps into a single more complex formula:

=VLOOKUP(LEFT(A4,FIND("/",A4)-1),LookupTable,2,FALSE)

 

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




#1590 Reconcile information

Posted by Jonathan on 06 April 2015 - 05:00 PM

Hi Hongmin,

 

I'd say there's no problem with that solution if you find that it's the best for you.  Personally I might prefer the formula-based approach using SUMIF, but I'm not sure that it would be significantly faster.

 

The OLAP solution is maybe a little bit overkill in this situation, but it's good to know that the option exists for more complicated requirements.




#1458 Access 2013 vs Excel 2013

Posted by Jonathan on 06 February 2015 - 08:18 PM

Hi Shawn,

 

It sounds like you need a system that allows you to maintain a table of Customers and store a list of Service Tickets for each customer. This is known as 'relational' data because there is a relationship between Customers and Service Tickets. While Excel 2013 has introduced some tools for working with relational data, Access is really the best choice when you need this kind of system.

 

For a more in-depth explanation of relational data, see the video lesson: Expert Skills Lesson 6-1: Understand primary and foreign keys.

 

Although Access is a good choice for storing relational data, Excel can still be very useful as a tool for analyzing the data in your Access database. You can see how to connect an Excel workbook to an external data source (such as an Access database) in the video lesson: Expert Skills Lesson 5-4: Use an external data source.

 

I hope this helps, but please feel free to reply if I can offer any more advice.