Some functions in Excel workbook not working as expected in previous versions of Excel

[[email protected]]

The attached workbook has all of the functions working as expected in Excel 365 however some users have expressed that some functions do not work. On investigation it appears that they are using previous / earlier versions of excel and it appears that one function in particular is not working as expected.

When a user follows the instructions on the "Instructions" tab and fills out all of the pink shaded cells on "Work Order" tab they are experiencing that they cannot select from the list of athletes in cells D6:D15, M6:M15, D22:D31, M22:M31.

There are 3 hidden worksheets. All worksheets are protected with the password 1234.

Is there anyway to overcome / resolve this issue so the workbook functions in previous / earlier versions of excel?

Many thanks

Stewart

[AskMeAboutExcel]

There's a tool within Excel that can guide you towards the answer - go to File > Info > Check for Issues > Check Compatibility.

(You can probably filter out Excel 97-2003 as few are likely still using it)

It will then report all the locations in your workbook that have back-compatibility issues. If there are specific formulas that you need help adjusting, let us know and we can try to help

[[email protected]]

Thanks AskMeAboutExcel,

That was very helpful for me to see what the actual compatibility issues are, my problem now is ... how to resolve them?

If you could offer some assistance that would be appreciated.

I've attached the workbook with all of the tab showing and protection turned off.

Sheet 2 has the Compatibility Report.

Cheers,

Stewart

[AliGW]

We'll need to know the OLDEST version of Excel that is being used by those who need to use this workbook. Please provide this information so that we know what we can work with.

[[email protected]]

Thanks Ali,

It's hard to say which would be the oldest version of Excel that any one user would be using so to be safe I'd have to assume it could be a old as 97-2003.

Cheers,

Stewart

[AskMeAboutExcel]

Hi Stewart - this is quite a big task. Not only will the formulas need to be re-written, but parts of the workbook will need to be restructured.

For example:
- the SEQUENCE formula in cell I1 of 'Rotation Order' won't work, so you need to find a new way of pulling the selected values through to conditional formatting
- any IFERROR formulas won't work pre 2007, so you will need to find another method of error handling
- it's not obvious how some of these formulas work / what they drive (e.g. cell K2 on 'Gymnasts') so will take a long time to unpick.

We can help, but suggest you take it formula by formula to make sure we don't break anything inadvertently. Also, I would suggest against making it back-compatible too far unless absolutely necessary - the further back you go the more complex it will become.

[AliGW]

If you do want one person to do the whole job for you with no input from yourself, then you should look at the Commercial Services section where you can purchase points with which to pay your helper.

Otherwise, do as has been suggested, but there must be some input from you in trying to solve the issues. I hope this makes sense to you.

[[email protected]]

Thanks AskMeAboutExcel,

Wow, sorry I didn't realise it would be such a big task. Completely my fault for assuming all end users of the workbook would be using an up to date version of Excel

I may just contact the users concerned and ask them to consider updating to the latest version of excel or finding a friend (or club contact) that has the latest version of excel and ask them to fill out the workbook and submit it as this may be far less work for me, them and anyone on Excel Forum as we are all volunteers.

[[email protected]]

Thanks Ali,

I have received quite a lot of help from Excel Forum on this workbook already and feel I have made some contributions however it may be best at this stage that I just contact the users concerned and ask them to consider updating to the latest version of excel or finding a friend (or club contact) that has the latest version of excel and ask them to fill out the workbook and submit it as this may be far less work for me, them and anyone on Excel Forum as we are all volunteers.

Thanks heaps for your feedback, it is greatly appreciated.

[Fluff13]

Another option would be to get those users to sign-up for an Outlook.Com email (it's free) & that will give them access to Excel online which has all the latest bells & whistles.

[[email protected]]

Thanks Fluff13, I am currently evaluating this as an option / workaround.

if it all works as expected then this will certainly resolve the issue.

Thanks heaps for your contribution, it is greatly appreciated.

[Fluff13]

You're welcome & thanks for the feedback.

[AskMeAboutExcel]

Hi Stewart,

No need to apologise, and sorry if my responses were a little direct - was just trying to help. Fluff's suggestion is likely the easiest option for you though.

Just be aware that the macros won't run on Excel for the web, so you may want to add instructions on how to sort and clear/reset the data manually if required.