sonicyouth
Offline
Posts: 22352
|
|
« on: Friday, February 28, 2014, 10:38:52 » |
|
Help.
I have a spreadsheet listing all of the laptops at work with the warranty expiration date (date to be replaced) but for some reason it's too hard for people to read, so I've been told to go away and make another sheet that only contains information for laptops that have expired or are due to expire in the next 12 months which I presume shouldn't be too difficult?
I'm guessing this is a VLOOKUP jobby
|
|
|
Logged
|
|
|
|
Only Me
Offline
Posts: 1202
|
|
« Reply #1 on: Friday, February 28, 2014, 11:18:23 » |
|
Help.
I have a spreadsheet listing all of the laptops at work with the warranty expiration date (date to be replaced) but for some reason it's too hard for people to read, so I've been told to go away and make another sheet that only contains information for laptops that have expired or are due to expire in the next 12 months which I presume shouldn't be too difficult?
I'm guessing this is a VLOOKUP jobby
Why not put a date field and filter by date, ie only display the items required?
|
|
|
Logged
|
|
|
|
sonicyouth
Offline
Posts: 22352
|
|
« Reply #2 on: Friday, February 28, 2014, 11:20:04 » |
|
Why not put a date field and filter by date, ie only display the items required?
Apparently this is too difficult to use
|
|
|
Logged
|
|
|
|
timmyg
Offline
Posts: 614
|
|
« Reply #3 on: Friday, February 28, 2014, 11:27:16 » |
|
Can you not just sort the list by expiration date so that the ones coming up for replacement are at the top?
Conditional formatting will highlight the laptops that are needing to be replaced within a set time-frame, or if you want a separate list of laptops about to expire then you'll need to vlookup the values.
Obviously the TODAY()-"Expiration Date" formula will tell you how many days between now and replacement date, and once you've got that you can format and sort it however you like.
Depending on how big the list is I'd be tempted just to sort the list by expiration date and see the list there!
edit: just seen that having a filter is too difficult to use, so what isn't too difficult? A separate sheet?
|
|
|
Logged
|
never trust a nun...
|
|
|
sonicyouth
Offline
Posts: 22352
|
|
« Reply #4 on: Friday, February 28, 2014, 11:39:28 » |
|
I have the spreadsheet in a format whereby you can filter by date but this isn't sufficient... it needs to be a separate sheet in the workbook that lists only laptops that are expired or due to expire in the next 12 months.
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #5 on: Friday, February 28, 2014, 12:12:25 » |
|
I'd put in a extra column with the data with a formula that said if date exp<12months then "Yes" if not "No"
Then I'd do a pivot table on another sheet where you can select the criteria of "yes"
Does that help ? PM me if you want further info.
|
|
|
Logged
|
|
|
|
Simon Pieman
Original Wanker
Offline
Posts: 36318
|
|
« Reply #6 on: Friday, February 28, 2014, 12:54:47 » |
|
That's how I'd do it too. Then set the Pivot Table to automatically refresh upon opening.
Advantage is that it will automatically update not only the data but also give you a count of the number of laptops. Also you can group the expiring laptops by month in the pivot table which may be useful as well. You can even produce a useless chart to show number of laptops expiting in each month to impress middle management.
|
|
|
Logged
|
|
|
|
sonicyouth
Offline
Posts: 22352
|
|
« Reply #7 on: Friday, February 28, 2014, 13:11:27 » |
|
I've created the extra column by using this formula:
=IF(G2<=DATEVALUE("31/12/2014"),"Yes","No")
I now need a pivot table to output anything with a value of "Yes" in the column Due for replacement (I) but I can't get my head around the pivot table bit...
Thanks for the help so far, Excel confuses the shit out of me
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #8 on: Friday, February 28, 2014, 13:24:32 » |
|
Ok, click "insert pivot table", select you data range (all columns will have to have headers)
In the create pivot table box (pivot table field list) drag and drop the "yes/no" column created into the "report Filter" box
Stick the laptop reference into the Row Labels, add more fields as required. Use the criteria drop down arrow to select "yes" only.
Come back if you are stuck
|
|
|
Logged
|
|
|
|
sonicyouth
Offline
Posts: 22352
|
|
« Reply #9 on: Friday, February 28, 2014, 13:28:56 » |
|
If I do that it puts all the data into the first column so it's no longer a table format. What am I doing wrong?
|
|
|
Logged
|
|
|
|
Bewster
Offline
Posts: 4004
We fucking love you Gumbo!
|
|
« Reply #10 on: Friday, February 28, 2014, 13:43:05 » |
|
can you send me a sample of the data ??
or screen shot ?
|
|
« Last Edit: Friday, February 28, 2014, 13:51:33 by Bewster »
|
Logged
|
|
|
|
|
|