Replies

So far, I haven’t seen much advantage to Vista. Some things seem to work a bit better, but the unfamiliarity of it is a big drawbask. Too bad.

Wait ’til you get a load of XL07!

I have used XL 2007 for almost a year – I like the increase in rows / columns, although usually when I have that much data Excel is only a way-station in the process of loading into Access; a million rows just means slightly fewer steps in importing and reassembling the data in the database. Apparently ’07 is much better in using multiple processors than prior versions, so calculation-intensive tasks may be faster (if you have a dual-core or higher machine) than in 2003 – I never ran 2K or ’03 on a dual core box, so I can’t compare. As noted by others, the big difference is “the Ribbon” – some seem to tolerate it: I absolutely hate it – partially because it is such an absolulutely useless fiddle: I honestly cannot identify a problem that the Ribbon solves, although there are several that it creates. The graphing interface is less intuitive (although that may be familiarity speaking) than before, and the results -although all in soft pastels- are somewhere between downright ugly and hideous. The only improvement I’ve seen in graphing is that log scales don’t have to be integer powers of ten, which solves some problems; that change is long overdue.

Thanks Hans

I ended up cut/pasting the required data into a new s/sheet and it imported fine. There may have been formatting or blanks entered as ” ” outside the data range, but I’d have to go back and look. The data actually comes in two files (dates and prices for two different markets), and I want both prices to form part of a record for the same date, so it’s easiest to cut & paste in Excel, rather than importing as two tables and assembling the data through a query. If I’m going to be cut/pasting in Excel anyway, it’s just as easy to paste into a new file that I know is clean.

[indent]

---

and instead of FireFox I HAVE to use IE – It’s slowly killing me ……

---

[/indent]
Unfortunately, Mozilla isn’t supporting FF2 anymore. I just downloaded FF 3, and it chased me back to IE, although I plan to have a look at Opera. I tried Chrome, and wasn’t much impressed.

[indent]

---

If InStr(ws.Name, “Budget”) > 0 Then

---

[/indent]

InStr(String1, String2) searches String1 to find instances of String2, and returns the starting position within String1, so

InStr(“Winter”, “inter”) would return 2.

If String2 is not found, it returns zero – so testing for a return value > 0 is just testing to see if the search string has been found anywhere in the target string

If the “month of record” is consistently the prior month up to the 21st, and the current month thereafter you can determine the “month of record” by formula, and use that in the PT.

I see, after looking back at the thread – my apologies for my confusion!

I also have that book, and it is excellent, but with due respect, I would suggest that if you like reference books, as well as or in preference to web citations (I use both, and I suspect that most other people do as well) you might be better off with John Walkenbach’s “Power Programming in Excel XXXX with VBA” (where XXXX coresponds to the version you are using, or higher). The “Programmer’s Reference” is pretty heavy going, compared to the level of question you are asking. I have no doubt it will assist you; but I suspect that Walkenbach will be quicker and easier for now: the Programmer’s Reference will be more use when you have a little more experience.
One caveat about the Programmer’s Reference: when WROX published the Excel 2003 version, the four authors of the original were not involved, and all of them disclaimed any responsibility for that version – at one time at least one of them had some unflattering remarks about the editing and accuracy of the material in the book on their website. WROX has now published an Excel 2007 version, and three of the original “Excel 2000” authors (along with a fourth whose name escapes me at the moment) participated in it – from personal corespondence with one of the common authors, they all consider the “2007” version as the ‘real second edition’ of the ‘2000’ original.

I don’t know if he has updated much for XL 2007, but whenver I have a charting issue, I wander over to Jon Peltier’s site…

Why do you care about the order in the .mdb table? Whenever accessing that data you can specify a sort order for it, either by specifying an ORDER BY in the query that returns the recordset from the table, or dumping the whole table (in its existing order) into Excel and then sorting it in the s/sheet.

The limit on conditional format isn’t really three conditions – it is three formats. You can combine more than one logical test with a logical AND or an OR to form the condition that will trigger a particular format. I wasn’t sure whether you meant that the value of the cell would be the literal (string) value of -say- “S1” or it would be equal to the value in cell S1 (etc). I assumed the former, but the approach would be the same. The test for one of the conditional formats will be something like (for the cell B4 – obviously that part changes as required):

"Formula Is"
=OR(B4="S1",B4="S2",B4="S3",B4="S4")

the condition would evaluate as “TRUE” if the cell takes on any of the values “S1” through “S4” and in this case would apply a red pattern: other conditions would apply the appropriate green or orange colour.

If you wanted to test for equality with the values in Cell S1 (etc) you would change the condition so it wasn’t testing for a match with the string value “S1” (etc), but just whether B4=S1 (etc).

[indent]

---

it assumes you will never have ~#@ as part of your text!

---

[/indent]
You mean, no comic-book cursing?

You weren’t including the interest that the bank would have been charging

See the attached….

You could use a pivot table, as Hans suggests, or use an array formula or DSUM function to calculate the total for rows that meet a given criteria (ie, the ‘type’ column = “pears,” “apples,” “oranges,” etc). If you prefer to use subtotals on sorted data, you should insert a new column with values of 1,2,3,4, incrementing for each row. After sorting by “type” and applying subtotals you can remove the subtotals and re-sort in this key number sequence to restore the original order.

I doubt that anyone is following this saga, but over at Mr Excel Jon Peltier mentioned that he had implemented a solution similar to Andy Pope’s, that would allow a sloping target line, but that he had done it via formulas rather than by a VBA routine. I don’t think Jon intended it as a challenge, but I decided to take a whack at it out of interest – the result is attached.

In effect the formulas apply a Gaussian elimination to the augmented matrix formed from the target line and the line defined by each successive pair of data points. There is a test for which line (if either) has a zero slope, since that would normally be the second row of the matrix as the “X” coefficient would be zero. There is still one (minor problem) – the formulas crash if the data line is vertical, since the slope is some value divided by zero. If I was providing this for a production environment I would probably insert a test for that condition – but for now I think I will leave it as is. The logical next step would be to allow a series of target lines, which could approximate a curve – but for that level of graphing / display, I think Excel might not be your first choice in any event.