Free Microsoft Excel 2013 Quick Reference

Constructing formula for matching value separate spreadsheet Results

Good morning, All. I've been reviewing some of the posts to solve on my own what is probably a rather simple problem based on the knowledge bank here, but I can't quite locate the best option.

Every two weeks I download spreadsheet populated by an outside organization listing revenue line items and their amounts. What I need to do is match their text listing of a line item with our code for that item.

I've created a separate spreadsheet with column one showing their text description and column two showing our matching code.

How can I automate the matching of this data and returning the code value on to the newly downloaded spreadsheet?

Please advise if sample data is helpful and thanks for your time!

Hey all,
I have an assignment due about an hour ago, ive been wokring on it for quite sometime and i just cant seem to figure it out... The Microsoft help function was my best freind untill it stared putting out errors....
This is probably really simple stuff to some of you guys, so please take a look and help me out cuz i need to submit this by midnight

Part 1:
This spreadsheet will list a number of television shows and determine whether they are currently playing or about to play.

The Shows
You're going to create a list of television shows (real or imaginary) in your spreadsheet.
Your list should have the same headings as shown below, in bold, and surrounded in a thick border (more headings are going to be added).
Below the headings, list at least ten television shows, along with the TV channel, the weekday (1 for Sunday, 2 for Monday, 3 for Tuesday... etc), the start time, and the end time.
You DON'T have to use real television shows if you don't want to.
You can make them up.
You DON'T have to use the CSI: Miami example shown in the image.
You can use your own television shows.
Use a different time-slot for each entry, so that no two shows overlap.
To simplify the formulas that you will need to create, avoid using television shows that start before midnight and end after midnight.
The start and end times should actually be time values in the spreadsheet (i.e. NOT just text you've typed that looks like a time value - Quattro Pro or Excel should "know" the values are times), and should use the display format shown below (hours and minutes separated by colon, followed by "AM" or "PM"). Both Quattro Pro and Excel will allow you to display time values in that format.
The Show column data and heading should be left-justified.
The Channel and Weekday column data and headings should be centered.
The Start and End column data and headings should be right-justified.
Make sure your list and headings start at least five rows down from the top of the sheet - you will need a bit of space above the list to add things later on.

Your list should have these headings and this format.

Add a new column to the right of End, with the heading: Duration.
The column data and heading should be right-justified.
Use a formula in that column to calculate the duration of your television shows
(note: a simple subtraction formula will do it - remember: time values are just numbers).
The duration time value should be displayed in the same time value format as the start and end data, but without "AM" or "PM", since those suffixes don't apply to duration times. Both Quattro Pro and Excel allow you to display time values in that format.

The CSI: Miami show, above, has a duration of one hour (8pm to 9pm), which would be displayed as "1:00".

User Prompts
Above your list of television shows (in those few rows of space you left blank at the top of the sheet), have a message to the user which prompts "Please enter a date:" followed by a cell in which the user can enter a date. The date cell should use the date format shown in the images below. The user can enter a date by typing the date in the proper format. After you've set the cell to display with the proper format, try entering a few dates yourself so you can see how it works. If you enter the date properly, the cell will stay right-justified and Quattro Pro or Excel will "know" that what you've entered is a date.
Below the prompt for a date, there should be another message prompting "Please enter a time:" followed by a cell in which the user can enter a time (formatted as a time value as shown below, with the "AM" or "PM" suffix). Try entering a few time values on your own as well, to make sure you understand how to type the time in the right format (for example: if you forget the space before the "AM" or "PM" it may not understand what you've typed). You'll need to be able to do that in order to test your spreadsheet before you submit it.
Have the date and time formatted like this...

These are the formats for Quattro Pro users (notice the 2-digit day of the month).

These are the formats for Excel users (notice the single-digit day of the month).

Bold the prompts, as shown above, and surround the four cells with a thick border as shown above.
The date and time that the user enters in these cells will be used in the formulas described below. The TAs will test your spreadsheet by trying different dates a times and seeing how they cause different outcomes from the formulas described below.
Add a new column to the right of Duration, with the heading: Playing.
The column data and heading should be centered.
Create a formula for the Playing column, which displays "YES" if the show is playing at the date and time entered by the user, and "NO" otherwise.

For example:

See the "CSI: Miami" example shown in the first image of Part 1.
It plays from 8pm to 9pm on Thursdays (Thursday is weekday 5).
If the user were to enter the date "March 30, 2006" and enter the time "8:47 PM" then the Playing cell for the "CSI: Miami" row should read "YES" because that day is a Thursday and that time falls between the show's start and end times.
If the user were to then change the time to "9:03 PM" then the Playing entry would automatically change to "NO". You need to come up with a formula that will behave this way.


For this formula you will need to check whether the weekday for the user's date matches the weekday for the show, and then determine if the user's time is between the start and end times for the show. The only functions you should need are IF and WEEKDAY, (Excel users may need AND() and OR() as well, which aren't technically functions in Quattro Pro) but you may use any functions you wish - try to keep your formula as simple as you can. Don't forget that you can compare time values with < (less than), > (greater than), <= (less than or equal), >= (greater than or equal), and = (equal) inside IF conditions.

Add a new column to the right of Playing, with the heading: Soon.
The column data and heading should be centered.
Create a formula for the Soon column, which displays "YES" if the show isn't playing but it will start within 30 minutes (within 30 minutes of the date and time entered by the user, of course).

For example:

In the case of the "CSI: Miami" show that start at 8pm, if the user were to enter a date which is a Thurday, and the time "7:38 PM" then the Soon entry should display "YES" because the show would be starting within 30 minutes of that time - otherwise it would display "NO".


time values are really just numbers which the spreadsheet program is displaying in a fancy way to look like a time.
If you add 1 to such a number, it's like adding 24 hours (1 day).
If you add (1 / 24), it's like adding 1 hour (one 24th of 1 day).
If you add (1 / 24 / 60), it's like adding 1 minute (one 60th of 1 hour).
If you add (1 / 24 / 60 / 60), it's like adding 1 second (one 60th of 1 minute).
And so on.
You can use this knowledge to help you create a formula that determines if the show is starting with 30 minutes of a given time. This formula will only be slightly more complicated than the Playing formula. The two formulas will be very similar.

Add a new column to the right of Soon, with the heading: Attention.
The column data and heading should be left-justified.
Create a formula for the Attention column, which works as follows...
If the show is about to start (i.e. the Soon column shows "YES"), display the sentence "xxxx is starting soon on channel yyyy" but with xxxx replaced by the name of the show, and yyyy replaced by the channel number.
If the show is playing (i.e. the Playing column shows "YES"), display the sentence "xxxx is now playing on channel yyyy" but with xxxx and yyyy replaced, as above.
If the show is neither about to start nor currently playing, the formula shouldn't display anything at all (i.e. just display the empty string: "").


Don't put show names, channels, ..etc directly into the formulas - just refer to the show names, channels, ..etc in with cell references. In other words, you should create a formula for your first television show, and just copy & paste that formula down the list for the other shows, without needing to make any changes (same for the Duration, Playing, and Soon formulas too, of course!).


The CONCATENATE function can be used to construct those sentences from their individual pieces. Also, you have already created two columns that indicate for you whether or not a show is about to start or is currently playing - it is perfectly acceptable for the Attention formula to make use of those columns' results. In fact, your Attention formula will be simpler if you do.


If A1 contains "hi", B1 contains " there ", and C1 contains "dude", then CONCATENATE(A1,B1,C1) will produce "hi there dude". CONCATENATE joins its parameters together into a single string/sentence.

Some More Formatting
Once finished, make sure the list headings are all in bold, with a thick border, and justified as per the instructions, above.
Surround the entire list of shows with a single thick-bordered rectangle as well.
Colour the cell backgrounds for the column headings. Use any colour other than white (don't use black either, since it would make the headings impossible to read).
Colour the cell backgrounds for the date and time prompts as well.
Make sure every column in your spreadsheet is wide enough to hold its longest entry.

Save your spreadsheet file.


No luck finding an answer? You could always try Google.