Excel 2007 : excel 2007 to excel 2003 conversion

[astar24]

Hello folks,
I created a worksheet with different functions and macros, using excel 2007. I am trying to make it compatible with excel 2003, but I'm not succeeding.
I think it's necessary that you guys see the worksheet in question.. so here it is. I think the major issues are related to the conditional formatting (which is not big, and I know I can add it in the 2003) but most of all the automatic fill and update of internet data in the cells.
thanks
Andrea

[Andy Pope]

It's more the CF.

If in xl2007 you do a SaveAs xl97-2003 it will display the problems and help you locate the cells.

You have +30 arguments in formula

[astar24]

is there a way to solve this? I need those 30+ arguments to be there. I also found a similar problem trying to convert it to open office. Error 512, which I believe is overflow with arguments/references.
thanks
andrea

[Andy Pope]

You either need to find another formula or break your formula out into multiple formula.

When building workbooks for multiple versions it's best to start with the oldest version.

[astar24]

thanks Andy.
how about exporting the 2007 worksheet into a different format? like HTML or whatever else..

[Andy Pope]

Sorry not sure I understand what you mean. How would a html file help?

Basically for the file to be used in different versions you have to use things that are compatible within all versions. 30+ arguments are not.

[astar24]

let me explain: I need to display this worksheet on a computer that doesn't have office 2007. Exporting the worksheet into an HTML web page would be great, but when i try to do that, it seems to lose the macros and the data refresh functions. So it saves it just as a static page.
I don't know how can I cut down the arguments.. I really don't
thanks

[Andy Pope]

You have to take you formula and split it into more managable chunks.

So your current formula in B3,

=IF(OR(
ISNUMBER(SEARCH(" 0SM ",ACY!C2)),
ISNUMBER(SEARCH(" M1/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1/16SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 3/16SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 5/16SM ",ACY!C2)),
ISNUMBER(SEARCH(" 3/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1/2SM ",ACY!C2)),
ISNUMBER(SEARCH(" 5/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 3/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 7/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1 1/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1 1/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1 3/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1 1/2SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1 5/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1 3/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1 7/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 2SM ",ACY!C2)),
ISNUMBER(SEARCH(" 2 1/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 2 1/2SM ",ACY!C2)),
ISNUMBER(SEARCH(" 2 3/4SM ",ACY!C2)),
ISNUMBER(SEARCH("OVC001",ACY!C2)),
ISNUMBER(SEARCH("OVC002",ACY!C2)),
ISNUMBER(SEARCH("OVC003",ACY!C2)),
ISNUMBER(SEARCH("OVC004",ACY!C2)),
ISNUMBER(SEARCH("OVC005",ACY!C2)),
ISNUMBER(SEARCH("OVC006",ACY!C2)),
ISNUMBER(SEARCH("OVC007",ACY!C2)),
ISNUMBER(SEARCH("OVC008",ACY!C2)),
ISNUMBER(SEARCH("OVC009",ACY!C2)),
ISNUMBER(SEARCH("BKN001",ACY!C2)),
ISNUMBER(SEARCH("BKN002",ACY!C2)),
ISNUMBER(SEARCH("BKN003",ACY!C2)),
ISNUMBER(SEARCH("BKN004",ACY!C2)),
ISNUMBER(SEARCH("BKN005",ACY!C2)),
ISNUMBER(SEARCH("BKN006",ACY!C2)),
ISNUMBER(SEARCH("BKN007",ACY!C2)),
ISNUMBER(SEARCH("BKN008",ACY!C2)),
ISNUMBER(SEARCH("BKN009",ACY!C2)),
ISNUMBER(SEARCH("VV",ACY!C2)),
ISNUMBER(SEARCH(" 0100 ",ACY!C2)),
ISNUMBER(SEARCH(" 0200 ",ACY!C2)),
ISNUMBER(SEARCH(" 0300 ",ACY!C2)),
ISNUMBER(SEARCH(" 0400 ",ACY!C2)),
ISNUMBER(SEARCH(" 0500 ",ACY!C2)),
ISNUMBER(SEARCH(" 0600 ",ACY!C2)),
ISNUMBER(SEARCH(" 0700 ",ACY!C2)),
ISNUMBER(SEARCH(" 0800 ",ACY!C2)),
ISNUMBER(SEARCH(" 0900 ",ACY!C2)),
ISNUMBER(SEARCH(" 1000 ",ACY!C2)),
ISNUMBER(SEARCH(" 1100 ",ACY!C2)),
ISNUMBER(SEARCH(" 1200 ",ACY!C2)),
ISNUMBER(SEARCH(" 1300 ",ACY!C2)),
ISNUMBER(SEARCH(" 1400 ",ACY!C2)),
ISNUMBER(SEARCH(" 1500 ",ACY!C2)),
ISNUMBER(SEARCH(" 1600 ",ACY!C2)),
ISNUMBER(SEARCH(" 1700 ",ACY!C2)),
ISNUMBER(SEARCH(" 1800 ",ACY!C2)),
ISNUMBER(SEARCH(" 1900 ",ACY!C2)),
ISNUMBER(SEARCH(" 2000 ",ACY!C2)),
ISNUMBER(SEARCH(" 2100 ",ACY!C2)),
ISNUMBER(SEARCH(" 2200 ",ACY!C2)),
ISNUMBER(SEARCH(" 2300 ",ACY!C2)),
ISNUMBER(SEARCH(" 2400 ",ACY!C2)),
ISNUMBER(SEARCH(" 2500 ",ACY!C2)),
ISNUMBER(SEARCH(" 2600 ",ACY!C2)),
ISNUMBER(SEARCH(" 2700 ",ACY!C2)),
ISNUMBER(SEARCH(" 2800 ",ACY!C2)),
ISNUMBER(SEARCH(" 2900 ",ACY!C2)),
ISNUMBER(SEARCH(" 3000 ",ACY!C2)),
ISNUMBER(SEARCH(" 3100 ",ACY!C2)),
ISNUMBER(SEARCH(" 3200 ",ACY!C2)),
ISNUMBER(SEARCH(" 3300 ",ACY!C2)),
ISNUMBER(SEARCH(" 3400 ",ACY!C2)),
ISNUMBER(SEARCH(" 3500 ",ACY!C2)),
ISNUMBER(SEARCH(" 3600 ",ACY!C2)),
ISNUMBER(SEARCH(" 3700 ",ACY!C2)),
ISNUMBER(SEARCH(" 3800 ",ACY!C2)),
ISNUMBER(SEARCH(" 3900 ",ACY!C2)),
ISNUMBER(SEARCH(" 4000 ",ACY!C2)),
ISNUMBER(SEARCH(" 4100 ",ACY!C2)),
ISNUMBER(SEARCH(" 4200 ",ACY!C2)),
ISNUMBER(SEARCH(" 4300 ",ACY!C2)),
ISNUMBER(SEARCH(" 4400 ",ACY!C2)),
ISNUMBER(SEARCH(" 4500 ",ACY!C2)),
ISNUMBER(SEARCH(" 4600 ",ACY!C2)),
ISNUMBER(SEARCH(" 4700 ",ACY!C2)),
ISNUMBER(SEARCH(" 4800 ",ACY!C2))),
"IFR","VFR")

Should be split in to 10 or so other cells, So fo rthe fist bit,

OR(
ISNUMBER(SEARCH(" 0SM ",ACY!C2)),
ISNUMBER(SEARCH(" M1/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1/16SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 3/16SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 5/16SM ",ACY!C2)),
ISNUMBER(SEARCH(" 3/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1/2SM ",ACY!C2)),
ISNUMBER(SEARCH(" 5/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 3/4SM ",ACY!C2)),
ISNUMBER(SEARCH(" 7/8SM ",ACY!C2)),
ISNUMBER(SEARCH(" 1SM ",ACY!C2)))

This will return just TRUE of FALSE. Break the other bits out as well then final do your IF test. So assuming you used cell B20:B30 the new formula in B3 would be

=IF(OR(B20:B30),"IFR","VFR")

[NBVC]

What if you create a list on the side somewhere of the 30+ possibilities and then use this array formula:



=IF(MATCH(1,SEARCH("*"&$J$1:$J$30&"*",ACY!C2),0),"IFR","VFR")

confirmed with CTRL+SHIFT+ENTER not just ENTER

where J1:J30 contains all the options to look for (each prefixed with a space)

[astar24]

thanks guys! i will give a shot to both your suggestions.

[NBVC]

Actually, now that I relook this, you don't need to prefix each item in table with a space... you can get the formula to add it:

=IF(MATCH(1,SEARCH("* "&$J$1:$J$30&"*",ACY!C2),0),"IFR","VFR")

[astar24]

I did what you wrote, but for some reason it always gives me IFR as a result.
can you see why? i'm trying to figure out by attempts..
thanks

[NBVC]

Did you remember to confirm the formula when you entered it with CTRL+SHIFT+ENTER keys... so that { } brackets appeared around the formula?

[astar24]

that's affirmative

[NBVC]

Did you create the side list to match against... and are the cells in the range filled.. (i.e. no blank cells in the list)?

Maybe post a new sample of your workbook showing issue.

[astar24]

here it is.
yes, i think the range is fine

[NBVC]

Maybe try this instead:

=IF(OR(ISNUMBER(MATCH("*"&$B$25:$B$115&"*",ACY!C2,0))),"IFR","VFR")

confirmed with CTRL+SHIFT+ENTER...

[astar24]

it tells me I entered too many arguments.
it doesn't like it, apparently..

[NBVC]

See attached...

[astar24]

still not displaying the correct answer to the IF.. I'm focusing just on the B3 cell of the summary page, before doing the other ones, because the formulas are pretty much the same. if that one works, then the other ones will work.

[NBVC]

Sorry... was a little careless.. forgot to add the space before/after the search criteria... as it was it would say 009 from BKN009 was a match, when it isn't... I did mention that earlier, but forgot to incorporate in new formula.

try this amendment:

Please Login or Register  to view this content.

CSE Confirmed

[astar24]

seems like it's working!! i'll finish the other cells and test it with some examples!
thanks a lot! i'll let you know

[astar24]

new little issue:
some of the possibilities, as you might have seen, are numbers expressed as 0100, 0200, 0300 etc. until they get to 1000 etc.
those first nine possibilities appear in the ACY table as 0100 0200 etc, therefore with a zero before the number.
in the range of possibilities in the summary page i changed the cell format to display them as 0100, 0200 etc. but in the formula string they keep showing them as 100, 200, etc.. therefore the IF formula doesn't find them in the ACY table.
I'm not sure if i expressed myself..

[NBVC]

Change the format of the cells in the range of possibilities by selecting the range and going to Data|Text to Columns... skip to 3rd window and select Text from column data format area and click Finish... then add the 0 in front of those numbers that need it.

[astar24]

sweet! it is working! thanks a lot!
i hope this will be my last question for you.. so here it is:
as you understood, i'm trying to make this worksheet compatible with excel 2003, so i just tried to save it in that format, and i still get some errors. here's the big one
quote: somce cells contain conditional formatting with the 'stop if true' option cleared. earlier versions of excel do not recognize this option and will stop after the first true condition. location: 'summary', conditional formatting.
does it refer to the red/green color coding of the cells? if that's the case, i can remove it from this worksheet, and apply it with excel03.. it's only 2 colors, so if i remember well, it should be able to do conditional formatting. correct me if i'm wrong, or if the error refers to something different.
thanks a million

[NBVC]

I think it does refer to those conditional formats... better to remove and rebuild in 2003 version.. I tried saving as .xls file and still lost some CF...

Also, earlier versions don't support the shade of green and red you have... so it seems to convert to other colours... you may need to change those too...

[shg]

earlier versions don't support the shade of green and red you have... so it seems to convert to other colours... you may need to change those too...

You can change the color palette in the 2003 workbook to any 56 colors you like.

[astar24]

i solved the conditional formatting and the color issues. those are fine.
although, i can't see the macros that refresh the internet data import every 5 minutes, and also the formula recalculation/refresh that was also every 5 minutes.
maybe i'm wrong.. here it is

[astar24]

ok, i found where i can reset the update every 5 instead of 15 minutes.. as far as the internet data import.
i'm still missing the calculation of the first page (summary)

[NBVC]

What calculation? The formula I gave you shows up in all the cells in B,C,F,G

[astar24]

yeah, it's working now. it looked stuck at first, probably because it was not updating the data as often as i needed them. now it seems to work. i'll double and triple test the worksheet, of course.
thanks again for all your help!!