Forum Discussion
Formula not working - is it because we are using an older Excel version?
SergeiBaklan JanePG Sergei, it would appear your formula also doesn't work for pre-DA and that is your answer Jane that it appears you have dynamic arrays and your husband does not. I suspect if you type =FILTER you will see information about it while your husband's version would only show FILTERXML as a valid function.
They are still rolling DA out so it is still possible your husband may get it soon.
As for your formulas please note the your "LEFT(... , 1)" is purely summing the first character and if those values exceed 1 digit OR if the corresponding values in the table change (e.g. 2today has "5" in col B) that formula will not work.
Hi Mat
thanks for your reply.
Attached is a simple version of the spreadsheet which hopefully will make things clearer.
- jpg are my initials and so I have named my lookup table jpg. Nothing to do with images.
- the data that I want to lookup up is chosen from a validated list, thus ensuring it will be in the lookup table, so in the cells E5:E8 they are alphanumeric but always start with a number
I'm afraid both of us are reliant on our respective companies for upgrades so we basically have what we are given. My husband may be able to get an upgrade if he can say that the version he is on is insufficient.
thanks
Jane PG
Maybe these non-array formulas--I did each of them "the old fashioned way"--which should work on your husband's computer, can also illustrate the difference between the capabilities.
These newest functions that have been referred to, in the most recent release, would give both of you a LOT more capabilities. Here's a link to a YouTube video that I used to become familiar with FILTER, UNIQUE, and SORT. I've been employing these functions ever since.
Maybe both of you could use it to sell your organizations on the value of the newer system. https://www.youtube.com/watch?v=9I9DtFOVPIg