Replies

Have you tried a custom sort? Unfortunaltely we have moved to 2010 and it is slightly different. It says to drag the item, but I haven’t been able to figure out how to drag it.

In 2003 I used custom sort all the time. You can select whatever order you want, just move the names up and down to sut.

Just a suggestion

Why not build an individual sheet for each sales rep and have them all roll up into the master sheet.

eg build a page for John Smith, copy it and replace with Mary Jones info and so on until you have all 19 reps. Make one more copy and use it as your master (unless your master needs to show each rep separately).

in the cells of you master have formulas like =+A2 JSmith +A2 MJones +A2 M Monroe etc
or if you need them to show on separate lines then link each line on the master to the specific line on the rep page
only enter the data once into the individual page.
The formula will pull the totals together on your master sheet
This way you can print the individuals sheet each month and still have you master sheet.

If your master sheet is like a database eg headings across the top of each column and rows of data,
then perhaps you could use something like Crystal Reports – once you design the report, you just give it the parameters you want,eg John Smith, run it, print it, then change to Mary Jones, run & print etc
Once the report is designed should only take about 10 to 15 minutes to run all 19 reports.

Thanks John,

I passed on the message and finally heard back that it worked. She said –

Thanks very much! It worked and has finally gone!

in another program I use, to do the subtraction in a pivot table you would use the following calculated field

=([Value] Where ([Month]=Nov)-[Value] Where ([Month]=Dec))

I’ve never tried it in excel, but would presume it would be similar.
Excel doesn’t have where so you might have to use an IF statement instead

capri

Thanks Rory,

I tried the SUMPRODUCT formula and it returns blanks in the cells for future months which is one step in the right direction.

However the HLOOKUP still does not like the zero’s so returns “Month” because the July entry is zero. If I overkey that with 0.00001 then it will return November because December is zero.

I tried the Lookup formula and it works perfectly. Problem solved. I appreciate all your help.

capri

Steve

Thanks for the formula, however it did not do produce results any different than the original formula. All future months still had a zero result.

Rory,

I recently started a new job and this is from a spreadsheet developed by someone else. I am trying to figure out exactly what they have done. The spreadsheet feeds a dashboard. There are about 45 measures. Only 2 of the measures will ever have zero as they measure out of ordinary events that don’t often occur. The target is zero, but occassionaly there is a figure form 1 to 3.

I noticed in one column they had overkeyed the formula with 0.1 formatted to no decimal places. They did this for all past months with zero totals. This is not a good solution. In the other instance they left it as is and just put a comment on the dashboard spreadsheet that the cell was not working and then manually overkeyed it. This isn’t a good solution either.

I was able to figure out that the zero’s cause the problem, but not having used the SUMIF formula for lookups before I don’t know how to get around the problem. The formula is pasted in for the entire financial year and works fine in all the other columns. Even though the feeder sheets have blank cells, the SUMIF seems to produce a zero for all future months. SUMIF is needed as the data in the feeder spreadsheets is by region and we only report the overall total on the dashboard. I tried overkeying the zero’s on the feeder sheets as 0.00001 and everything worked fine. It’s not an ideal solution. I was hoping someone had run into a similar problem and found a formula that could deal with this type of situation.

capri

Thanks Rory,

However that won’t work in this spreadsheet. The person who constructed it used
=SUMIF(OTHER!A:A,dateLookup!$A6,OTHER!C:C)
to get the linked data.

The formula has been copied down for the year. In the future months it returns a zero even though the cells are blank on the “OTHER” tab.

capri

Have you tried filtering. If your email range is in one column, then on that column use the custom filter ” contains” and “hilton.com”.

If you have a column containing numbers in the data, you could block the filtered column and the sum should appear on the botton of the spreadsheet. Right click the sum and change the operation to count.

Thas should give you the number.

Thanks Paul and Jan,

I tried Jan’s suggestion first and it worked. Just saved myself heaps of aggravation.

Try unchecking ATP, exiting Excel and checking ATP again.

capri

Create a folder in your section of Documents and Settings, right click, select Sharing and Security, and select ‘Private‘.

See MSKB 930987: How to make files and folders private in Windows XP so that only you have access to them

Thanks Lief and the others,

I’ll give your method a try first, then try the others as well. It always helps to have options. At least now I can relax and not worry about taking leave.

Unfortunately our IT department doesn’t like allowing us other than the standard software. I had to fight with my managers support just to get Excel 2003 because 2002 didn’t have enough memory for my requirements. I spent 2 years trying to get a piece of software which they at first approved then rejected. The methods mentioned will hopefully be sufficient for my needs.

capri

Is is possible to put conditional formatting on data points in a chart?

I think I found the solution at

http://www.andypope.info/charts/Invertneg.htm

in case the full url does not show — ^http://www.andypope.info/charts/Invertneg.htm

in case anyone else is looking for the same type of solution. I haven’t tried it yet, but it appears to do what I want.

capri

Yes, it’s probably due to charts that automatically scale fonts – see You receive an error message when you add a chart to a workbook in Excel for an explanation. (As you can read there, a workbook can contain a maximum of 512 fonts, where each variation of size counts as a separate font)

Thanks Hans,

I’m sure it’s the autoscaling that is causing the problems. Unfortunately I can’t even change it on existing tables as I still get the same message. For now I am going to chop my report into 3 pieces, and hopefully get through this year end, but will have to completely rebuild in the new year after I change the registry setting. What I find hard to understand is it won’t even let you change to an already existing font. I managed to change the axis and legend fonts on 6 charts to arial 10 from arial 9.75 but after that I could not change any more. Since arial 10 is so common, and 9.75 less common I thought I could get rid of all of them, by changing to 10 but no luck.

capri

Thanks Hans,

You’re a lifesaver.

capri

Thanks Jezza,

It works great. I’ll have to figure out how the IF(AND part works so I can use it in future.

capri

Hi Capri

Does this do the job? Just place column N and copy down:

=IF(AND(NOW()-L2>=14,M2=”N”),NOW()-L2,””)

Thanks Mike,

I appreciate your help. it works fine.

capri