1. Excel formula needed..... (Excel 2000 with all updates)

Hi Everyone!
I'm trying to develop a way to do the following:
I have a spreadsheet which contains about 830 lines of data. It is in fact a world-wide phone rates price sheet with the name of the country and/or carrier in the first column - A. In column F I am provided with percentages which compare the price this month (in Column [img]/forums/images/smilies/cool.gif[/img] with the price in the previous month (column E) and which show either a +ve value for an increase, a -ve number for a decrease or a zero if there was no change (in fact some zeros are not really zeros, but are shown as such because they have no decimals showing and are thus rounded to zero by Excel (no formula involved).
For specific countries and/or carriers that I define (often the same set of them, but not in the same place in the sheet as they come and go or lines are added. There are about 20 to 25 countries, with up to 10 or 12 different prices per set i.e. Australia, Australia - Canberra and so on). I would like to be able to determine if they show an increase or a decrease (not interested if they really are at zero change) and pick them out. I then would like to count the number of these selected data rows that have an increase and the same for decrease, showing the count value on the sheet. I don't mind if the columns beyond the last one with data in it (which is F) are used to do stuff. If anyone has an idea how to accomplish this "automatically", I'll be eternally grateful as I do it manually now and it kills me every month !! I have attached a file for reference, truncated to meet the 100k limit.

2. Re: Excel formula needed..... (Excel 2000 with all updates)

How do you want to specify the set of countries/carriers?
- By creating a list of them in a separate area of the worksheet.
- By adding an extra column to the existing table, with for example an "X" in rows you want to be in the set.
- By physically selecting the rows.

3. Re: Excel formula needed..... (Excel 2000 with all updates)

Hi Hans,

A list of them in a separate area of the spreadsheet would be fine, as would adding an extra column and identifying the three different types of change - Increase Decrease and No change for example. The key here is to be able to count and show the value of the increases and decreases for the selected countries.

Cheers,

David

4. Re: Excel formula needed..... (Excel 2000 with all updates)

See the attached version. I added a column in which the selected countries are indicated with an "X", and created formulas at the top to display the number of increased and decreased rates.

5. Re: Excel formula needed..... (Excel 2000 with all updates)

Hi Hans,
Many thanks for posting this solution so quickly. I guess this will take care of summing the Increases and Decreases. However, the selection of the various countries and marking an "X" in the separate column remains a manual job. is there any easy way to automate this process, say with some kind of script, that would choose the contries I programme in once (and could modify as needed) and which adds the "X" in the new column? Once this is done, the formulas would automatically be added in two chosen cells where they produce the sums I'm interested in? As the file I provided you is sent to me once a month, I'd need to be able to port whatever script might do this to the new sheet or be able to use it "globally" from within Excel. Look forward to reading any tips you have to accomplish this. Again, many thanks for your help.

6. Re: Excel formula needed..... (Excel 2000 with all updates)

From your original post I got the impression that the list of entries to be selected is changeable. You could keep a list of selected entries in a separate worksheet, and create a UserForm with a multi-select list box to let the user select/deselect entries.

7. Re: Excel formula needed..... (Excel 2000 with all updates)

Hi Hans,
Again thanks for your swift reply. Yes, the list does change, but quite infrequently and often places that are of no interest. However, I guess this does mean that the countries move their position (row) in the table from time to time. Is there no way to mark them, copy and paste along with the "X" in the separate column using a script? Otherwise it is quite tedious work! Thanks for all the help so far.

9. Re: Excel formula needed..... (Excel 2000 with all updates)

You could create list on another sheet of the items you want to mark. Then use a MATCH to look up each item in the changeable sheet, in that list from the other sheet and if it is on the list, mark with an "X", if not put in a null ("").

This formula could be copied down the column,

Steve

10. Re: Excel formula needed..... (Excel 2000 with all updates)

Hi Guys, Thanks for the quick replies. My overall aim here is to remove as much "manual" work as possible, so that only minor edits are needed to accommodate any changes that may (infrequently) appear on the list of countries. Look forward to reading your thoughts Hans!
Cheers,
David

11. Re: Excel formula needed..... (Excel 2000 with all updates)

Here is an adaptation of Hans' modifications with my suggestion. Add Names to Sheet 2 and they will be "X"d in Sheet 1.

You could also just use the new sheet to "extract" the values desired in a table and just use the other sheet as a Lookup.

Steve

12. Re: Excel formula needed..... (Excel 2000 with all updates)

And here is a version that doesn't modify Sheet1 at all. The formulas are on Sheet2, and no "X"s are placed on Sheet1.

(Steve, I shamelessly borrowed your version. Thanks!)

13. Re: Excel formula needed..... (Excel 2000 with all updates)

(Hans, And my version was borrowed from yours, originally and now again...)

This one "extracts" the values chosen from Sheet1 and gets rid of the complicated sumproduct and just calculates based on the extracted values. Change the values in column A in Sheet2 and you have a sublist which reads from Sheet 1. if sheet 1 is changed (or copied over and the rows change) the sheet2 values are updated to reflect the new locations of the rates...

Steve

14. Re: Excel formula needed..... (Excel 2000 with all updates)

Hi Guys,
This is just brilliant ! Exactly what I need. Thanks very very much for the time you both put into this. I'm very grateful.
Cheers,
David

15. Re: Excel formula needed..... (Excel 2000 with all updates)

Hi Guys,
Have encountered a slight glitch using the "Sheet 2" that you have produced. It works well when I stick to the file in which the two original sheets are contained, but when I try to copy "Sheet 2" into another workbook (which I will need to do every month) the formulas in "Sheet 2" produce a reference to the original file name when copied. I can delete the reference to the original file name in each formula and then copy this down the page, but was wondering if there is a way to copy the "Sheet 2" without the reference to the original file name appearing in front of the "Sheet 1" or "Sheet 2" in the formulas ?
Cheers,
David

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•