Excel question

[sonicyouth]

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

[Only Me]

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?

[sonicyouth]

Why not put a date field and filter by date, ie only display the items required?

Apparently this is too difficult to use

[timmyg]

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?

[sonicyouth]

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.

[Bewster]

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.

[Simon Pieman]

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.

[sonicyouth]

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

[Bewster]

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

[sonicyouth]

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?

[Bewster]

can you send me a sample of the data ??

or screen shot ?

[Not that Nice If I'm Honest]

Pen and paper ?