vlookupprojectinstructionsfinal(2)(1)

xlsx

School

Michigan State University *

*We aren’t endorsed by this school

Course

PHY 481

Subject

Accounting

Date

Nov 24, 2024

Type

xlsx

Pages

Uploaded by ngenobrian

Vlookup assignment We are going to create invoices using the vlookup function for product prices and shipping costs. We will group the sheet tabs to minimize the typing required. Sheet tabs will be renamed. For example, 197 will match 100 not 200. Open a new file, and click on sheet tab 1, 2, and 3 (you can use the control key or the shift key as they are adjacent tabs) Right click on the highlighted tabs and chose insert, worksheet, ok (since you had clicked on 3 worksheets first, your action resulted in 3 new worksheets) Rename the worksheet tabs: Sheet 1 Product List (right click on the tab, chose rename and type the name) Sheet 2 Shipping Cost Sheet 3 Sales Invoice 1_15 (remember from accounting the term 1/15 meant if paid within 15 days, you could take a 1% discount) Sheet 4 Sales Invoice 2_10 (if pay within 10 days of invoice date, you can take a 2% discount) Sheet 5 Sales Invoice net 30 (money due 30 days from date of invoice) Sheet 6 Sales Invoice EOM (money due at the end of the month) Reorder sheets so that Product list is in the first sheet position, Shipping Cost is in second, etc. through 6th place Click on the Product List tab (Remember this is formerly Sheet 1 which has now been renamed) a1 Your name column b on this worksheet indicates the cell where the information in column c should be entered, when necessary d1 Tables a4 Product a5 cake a6 cookies a7 coffee a8 creamer cell to put information in from column C, so for example, you would put your name in a1 a9 cups a10 muffins a11 nuts information to be entered in cell indicated in column B, so the word cake would go into a5 a12 plates a13 silverware a14 sugar a15 tea b4 Price b5 7.99 b6 5.99 b7 4.99 b8 2.69 b9 3.29 b10 5.99 b11 4.99 b12 3.99 b13 3.29 b14 3.29 b15 3.29 Autofit column A Click on the shipping cost tab (this is formerly sheet 2) a3 Sale Amount column a is the cell location for where the information in column b will be entered, so for example, the phrase sale amount goes into a3 a4 0 a5 100 a6 200 a7 300 a8 400 a9 500 a10 600 a11 1000 a12 5000 a13 10000 a14 25000 Autofit column A b3 Shipping Cost b4 6 b5 7.5 b6 10 b7 15 b8 18 b9 21 b10 25 b11 30 b12 65 b13 120 b14 150 Column A must be in ascending order if you use true Values are ordered from smallest to largest Letters from A to Z When using lookup tables, true yields the closest lowest match, not necessarily the closest match. There can be no duplicates in column A as this is the item being referenced. Sheet tab names can have spaces contrary to when we do range names

Click on all 4 invoice tabs (check that the word group appears at the top or this will not work) (these are formerly sheet 3-6) They must all be selected at the same time. (click on one, hold control and click on second, hold control and click on third invoice and then control and last invoice) all invoice tabs should be white and the word group should be on the top line of your screen next to the file name a3 Type in: "customer name" in cell a3 a4 Type in: "address" in a4 and continue down until d11 is filled in once again column a is the cell reference and column b is the cell contents a5 city a6 phone number a8 freight terms a9 credit terms a11 quantity c5 state e5 zip code e2 date b11 product c11 unit cost d11 total cost Now ungroup as the rest of the information appears on one invoice only (click on any one invoice to ungroup.. Make sure the word group is gone) Click on the sales invoice 1_15 tab (formerly sheet 3) f2 enter the date of sale (use the current date) once again column a is the cell reference and column b is the cell contents c3 Stam Catering Services c4 10 Oswego Street b5 Oswego d5 New York f5 13126 c6 315 342-1111 (notice this cell is left aligned, that means that Excel is treating it as text, why: we did not use an = sign) c8 FOB plant c9 1/15, n/30 a12 10 a13 5 a14 3 a15 5 a16 5 a17 3 a18 2 a19 5 a20 3 a21 2 b12 muffins b13 cake b14 coffee b15 plates b16 silverware b17 cups b18 tea b19 cookies b20 creamer b21 sugar c12 create a vlookup function lookup value: item purchased table array: use the product list column:?? We want to know its price range lookup:??? (I need an exact match) go to c12 and change whatever parts need to be made absolute so that it can be copied down d12 determine the amount charged d22 find the correct amount for this cell c22 the word subtotal should be put in this cell c23 the word shipping is in this cell c24 the word tax is in this cell c25 the word total should be put in this cell d23 put a vlookup formula in this cell lookup value: need to lookup the total on the shipping chart table array: go to the shipping cost tab and highlight the information column: ??? range lookup: ??? d24 the tax rate is 8% The tax rate is going to be multiplied by the sum of the subtotal and freight (make sure to tax freight also) d25 compute the total Have all cells showing 2 decimals only Change scaling to fit to one page, so that if you click on all 6 sheets, each one would be printed on 1 page for ex. I did Control ~ so that I could print the formulas, and in print preview, mine showed that it would have printed on 10 pages save as invoice fileyourname submit completed project in dropbox Be careful that you have no spaces. A space will be treated as a character by Excel and then they won't match. If it doesn't work, try retyping the information to make sure that there is not something in the cell that you can't see but prevents them from matching.

(click on one, hold control and click on secon all invoice tabs should be white a once again column a is the cell reference and column b is the cell c

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

nd, hold control and click on third invoice and then control and last invoice) and the word group should be on the top line of your screen next to the file name contents

(click on one, hold control and click on secon all invoice tabs should be white a once again column a is the cell reference and column b is the cell c

nd, hold control and click on third invoice and then control and last invoice) and the word group should be on the top line of your screen next to the file name contents

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version