| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 63275 | 2005-11-04 01:13:00 | Any Excel whizzes out there? | miknz (3731) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 401726 | 2005-11-04 01:13:00 | Hi, I am looking for an Excel formula for sorting my pricelists and finding relevant information when I need it . Basicly my problem is that as a tyre reseller I have several retail pricelists from varying suppliers that I plan to merge onto one excel sheet . I have created a formula to give me my sell prices on all these products but I face the problem of having around 1000 rows of information that will be very hard to read through if I print it out . What I am looking form having a box or cell that I can type a tyre size into then have it search my spreadsheet and display all the options available in that particular size(hope that makes sense) . For example, a customer rings up and wants a 235/45r17 for there falcon, all I do is type the tyre size into the sheet and it gives me a list of all the options and the retails . Is this something that I can do within excel? (Ihave excel 97) Thanks in advance Mike |
miknz (3731) | ||
| 401727 | 2005-11-04 01:59:00 | I think you are after data filters (www.personal-computer-tutor.com) . | gibler (49) | ||
| 401728 | 2005-11-04 08:20:00 | Hi, micnz! Perhaps you could use the tutorial I wrote for just this purpose. You will put your 1000-record pricelist as "sheet 2" in the tutorial. You will only go to that pricelist when prices change, but meanwhile you can do a lookup to them or even create an invoice to print out. If you have any questions or difficulties learning from the tutorial, please post back and I'll be happy to answer any questions you might have. The tutorial uses VLOOKUP, which is explained here (and if you're daring, it could be all you need): www.officearticles.com The tutorial itself, along with downloadable sample files is here: www.officearticles.com If I don't hear from you...good luck! |
Dreamboat (9170) | ||
| 401729 | 2005-11-04 20:52:00 | Thanks Dreamboat, I have had a read of your tutorial and it seems like this is what I am after although I have just spent an hour trying to get the formula to work within my s/s with little success. I am having trouble with "argument 2". Is there a simpler way of appling the formula? maybe I will try and post the lay out of the s/s and my formula and you can see if you can tell where I am going wrong Cheers |
miknz (3731) | ||
| 401730 | 2005-11-04 22:01:00 | Below is my basic s/s layout . The sheet is about 1000 rows long and I plan to ad to this when I get the formula worked out . As I said before my plan was to be able to type a tyre size into a blank cell, hit enter and have the all the available options matching the entered size (a6:a1035) show up with there corresponding retail prices (i6:a1035) . This is the formula that I have been trying to get working, can you point me in the right direction please? =VLOOKUP(A6,A1,5,TRUE) . God I hope this makes some sense to you! Because I am getting more confused by the second! a b c d e f g h I 6 205/55R17 V103 91Y $485 . 78 7 205/50R17 V103 93Y RF $511 . 88 |
miknz (3731) | ||
| 401731 | 2005-11-04 23:56:00 | Hi, mik. As you know, I got your file, hooked you up with what I believed you wanted (a price lookup) and sent it back. VLOOKUPs can be confusing, to say the least. We ended up with this formula for your VLOOKUP. This particular VLOOKUP returns your V-Number (whatever that is!) on the tire size you chose. Other VLOOKUPs return other information about that tire (tyre!): =VLOOKUP($C$4,Pricelist,2,FALSE) Here's a sample file derived from mik's for anyone who's interested: www.officearticles.com Hope it helps, mik. There's other cool downloads in my downloads folder, free for the taking: www.officearticles.com Slowly, but surely, it is building up to be a pretty cool resource. |
Dreamboat (9170) | ||
| 1 | |||||