-
WSj.peter.orourke
AskWoody LoungerThe names have in common that they use the HEX2DEC function. In Excel 2003 and before, this is a function which is part of the analysis toolpak addin and hence technically a “User defined function”.
In Excel 2007, these functions are part of Excel itself.As soon as you export an Excel 2007 file to 2003 fileformat, defined names using these ATP functions are “mistreated” obviously.
The only way I’d know how to solve this is to avoid usage of former ATP functions. Maybe they do work if they are just in cells directly iso range names.
Thanks Jan Karel – Great spot!
Moved the HEX2DEC() out of the Defined Name and into the worksheet itself. I left the rest of the formula in the Defined Name(s). Works exactly as I need in both Excel 2007 and File > Save As > Excel 97-2003. File size also now reduced to 1Mb from it’s 2Mb in Excel 97-2003, though perhaps that;s not related.
Anyway. It works. Thanks again for you insight.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerCould you perhaps list the 4 offending range names and their formulas?
cal1stPrep =HEX2DEC(MID(INDEX(tblTestProgressLogs,calPrimaryTestLog,COLUMN()-inpColumnOffsetPrep),INDEX(lstIndexInLogData,ROW()-inpRowOffset),CH))
cal2ndPrep =HEX2DEC(MID(INDEX(tblTestProgressLogs,calSecondTestLog,COLUMN()-inpColumnOffsetPrep),INDEX(lstIndexInLogData,ROW()-inpRowOffset),CH))
cal1stTest =HEX2DEC(MID(INDEX(tblTestProgressLogs,calPrimaryTestLog,COLUMN()-(inpColumnOffsetTest+IF(COLUMN()>=inpColumnFudge,1,0))),INDEX(lstIndexInLogData,ROW()-inpRowOffset),CH))
cal2ndTest =HEX2DEC(MID(INDEX(tblTestProgressLogs,calSecondTestLog,COLUMN()-(inpColumnOffsetTest+IF(COLUMN()>=inpColumnFudge,1,0))),INDEX(lstIndexInLogData,ROW()-inpRowOffset),CH))
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerHi Folks
Has this issue progressed at all? I’ve been developing a model in Excel 2007 that makes extensive use of Defined Names to store repetitive formulas and/or as means of keeping functions in cells reasonably easy to read. I’ve used this approach extensively in the past without any issues, albeit in versions of Excel prior to 2007.
While I am working in Excel 2007, because of the client I am working with I need to deliver my model in Excel 1997-2003 compatible format and I think this is the root cause of these issues.
The situation I now have is that doing a File Save As (Into an Excel 1997-2003 format file) will now routinely remove the same 4 Defined Names. i.e. They are there but have are set to “=#VALUE!”. The problem does not occur if I File Save As into a 2007 format version of the file.
Other observations. The 1997-2003 format file is about 2Mb, the 2007 version is considerably smaller (as you would expect) at 271Kb. OK. The working 1997-2003 format of the file is 2,313 KB. The crocked version of this file, created via File Save As, is 2,218 KB. clearly there is something “missing” from the crocked version(s).
I’ve ported the working 1997-2003 format version (2,313Kb) over to another PC running Office 2003, opened the file without issue. All Defined Names are present and correct. However, on this PC this file shows as being 951Kb in size. i.e. Different to what is reported on Excel 2007? N.B. Just done a test on the Office 2003 based PC by opening an archived copy of a previous version of the Excel 2007 created file (in 97-2003 format) and done a File Save As. The file changes from circa 1.9Mb to 704Kb when saving via Excel 2003 rather than Excel 2007. Is any of this of significance?
Also tested porting an Excel 2007 version of the file to the Excel 2003 based PC. I have the necessary Microsoft conversion software to open Excel 2007 files in Excel 2003. The same four Defined Names are missing when I open this Excel 2007 file in Excel 2003. However, the Defined Names are there and working if I open the original file in Excel 2007.
Any of the above help, shed any light? Oh yes.. I’m fully patched and I’ve downloaded the hotfix mentioned earlier in this thread.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerYour description switches from “I” to “you”. Is this some kind of homework assignment?
Hi Hans
Nope. just my bad grammar! Thanks for the feedback, most helpful and works as required.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerDoes anyone know how to establish the e-mail address, not the Friendly Name but the actual e-mail address the original inbound message was sent to?
You can use mailitem.to – but that isn’t going to help you if there are multiple recipients, because it returns a string containing all of the recipients and, you only get the actual e-mail address if the receipient doesn’t have a Friendly Name that was used.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerCheers for the hints.. Once I’ve finished moving domains arund and setting up mailboxes I’ll have a look at this.
Peter
-
WSj.peter.orourke
AskWoody LoungerHey, I can’t see why the Account button wouldn’t be present – The accounts are defined as POP3/SMTP addresses and so are available for selection, allegedly.
Can you/anyone else point me at a piece of code to discover the To: address in the original inbound e-mail?
Functionally, I guess what I need to do is create a custom Reply button which when the user clicks it:
1 – Discover’s the To: address.
2 – Run’s your code (amended) and looks for the Account that matches the To: address, and sets this as the From address.
3 – No need for the user to select anything, I want to enforce the account selection.
4 – If the user wants to use another account, they can manually select it from the drop down Accounts list anyway.
At some future point, when feeling brave, replace the functionality of the installed Reply and Reply all with the above.
Thanks for your input.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerThanks Hans… Hadn’t considered the security implications… Harumph!
Maybe have to consider Zimbra over Hosted Exchange.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerSeptember 20, 2007 at 11:42 am in reply to: 2002 NOT Handling 2003 Meeting Requests (2002 SP3) #1076140Problem sorted!
On MY Outlook 2003 installation – Go into “Options”, “Select Calendar Options” in the Preferences Tab, bottom third of window titled “Advanced options”, uncheck the option “When sending meeting requests over the Internet, use iCalendar format”
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerSeptember 25, 2006 at 8:43 pm in reply to: Pass cell address or text to formula/function? (Ex #1030270Steve/Thread Watchers
Attached is final version of this model, for now. Added a section at the top of the “User Input” sheet which generates sample formulas for accessing/manipulating lists. (Requested by a colleague.) I’ve parked the UDF idea for the time being.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerSeptember 25, 2006 at 8:37 pm in reply to: Pass cell address or text to formula/function? (Ex #1029873I think I need help writing a UDF/VBA? The attached spreadsheet shows a technique I’m using for manipulating extendable and related lists. It pretty much does what I want with one exception which is that I have to hard code the name of the target list I want to use via this kind of command:
=INDIRECT(INDEX(tblLists,MATCH(B2,INDEX(tblLists,0,1),0),3))
The “B2” in the above is the hard coded bit. I can change it to the specific name of the list I want to use, “MyList”, “ThisList”, etc, etc. I am using a Defined Name formula, “FindList”, in places, and this is OK provided the cell I’m calling the FindList formula from has the same relative position to the cell containing the name of the list I want to use. What I’d like to arrive at in say a Data Validation cell setting (Allow set to List), is for the Source to be something like:
=FindList(B2 or Defined Name) or =FindList(“Hardware”) or =FindList(=AnotherFormula)
i.e. Either a cell containing the name of the list, the hard coded list name or, a formula that resolves to the list name. Ergo, assuming B1 had something I was looking for, and B2 had the name of the list that ought to contain it, I could then write a formula in B3 like:-
=MATCH(B1,FindList(B2),0)
Can anyone give some pointers as to how to start writing a suitable formula/UDF to achieve the above? N.B. tblLists is an “n” by 3 array. “n” will expand to match the number of entries in the array.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerSeptember 25, 2006 at 8:12 am in reply to: Pass cell address or text to formula/function? (Ex #1030140Thanks Steve.. Much appreciated.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerSeptember 24, 2006 at 5:14 pm in reply to: Pass cell address or text to formula/function? (Ex #1030096Hi Steve
Located in….. entirely the wrong place! i.e. Finger trouble on my part. Found another thread User Defiend Functions that helped – Insert Module… Doohh..
The UDF doesn’t appear to do what I ‘think’ i want it to do, which doubtless has more to do with my inability to explain what I want… So, I think I’ll leave this for now – the model does what I set out to achieve anyways – and come back to it when I’ve had time to learn more about VBA.. Any good tutorial/reference links out there? I’m far from being a ‘virgin’ programmer – just not overly familiar with VBA, though I did fo a lot of VB stuff up to version 2 and WordBasic, back in the mists of time I know!
Cheers and thanks again to all for your help and support. Fantastic place!
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerSeptember 24, 2006 at 7:33 am in reply to: Pass cell address or text to formula/function? (Ex #1030067(Edited by j.peter.orourke on 24-Sep-06 09:33. Fixed typo!)
Thanks Rory… Well. perhaps if I can get the UDF to work I can demonstrate my ‘confused’ solution… Any pointers/other threads worth looking at to help me understand why I’m getting #NAME? when I try to call the UDF?
As an aside.. this model does exactly what I want it to do already – self maintaining extendable lists which can easilly be linked as nested sub lists – the UDF thing is just a refinement to aid clarity, it won’t alter/improve functionality.
Cheers
Peter -
WSj.peter.orourke
AskWoody LoungerSeptember 22, 2006 at 3:21 pm in reply to: Pass cell address or text to formula/function? (Ex #1029916Thanks Rory..
I get a #NAME? error if I try to insert in a cell the command =GetRange(“lstWorksheet”).. Which I suspect is finger trouble on my part perhaps having put the UDF code in the wrong place? Just did Alt+F11, inserted code, closed and returned… Is there something I should be reading up to learn to do do this properly?
Also, I suspect I’ve confused everyone, myself included! I’m NOT actually looking to return a cell range that is refererred to via a Defined Name. I’m looking to return the contents of a specific cell in column 3 of the array/Defined Name tblLists. Although I use this formula to point at the correct cell:
=INDIRECT(INDEX(tblLists,MATCH(B2,INDEX(tblLists,0,1),0),3))
It’s actually the portion in red that needs reducing to a UDF, and the part in blue that I’d like to pass as a parameter.
Apologies for any confusion.
Cheers
Peter
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Windows 11 Insider Preview Build 22631.5116 (23H2) released to Release Preview
by
joep517
7 hours, 30 minutes ago -
Windows 11 Insider Preview build 27813 released to Canary
by
joep517
7 hours, 32 minutes ago -
Windows 10 Build 19045.5674 (22H2) to Release Preview Channel
by
joep517
7 hours, 33 minutes ago -
PartWork™ for Windows
by
bbearren
9 hours ago -
Toll road scams are back: What to do if you get a text saying you owe money
by
Alex5723
11 hours, 24 minutes ago -
Windows update download issue…
by
CAS
10 hours, 57 minutes ago -
WUMgr & KB5053602 Update/Install fail
by
dataman1701
4 hours, 5 minutes ago -
Finding Microsoft Office 2021 product key
by
Kathy Stevens
1 hour, 54 minutes ago -
Over-the-Top solves it!
by
RetiredGeek
1 day, 1 hour ago -
To Susan – Woody Leonhard, the “Lionhearted”
by
Myst
1 day, 8 hours ago -
Extracting Data From All Sheets
by
WSJon5
1 day, 10 hours ago -
Use wushowhide in Windows 11 24H2?
by
Tex265
12 hours, 14 minutes ago -
Hacktool:Win32/Winring0
by
Marvel Wars
20 hours, 57 minutes ago -
Microsoft Defender as Primary Security Question
by
blueboy714
15 hours, 42 minutes ago -
USB printers might print random text with the January 2025 preview update
by
Alex5723
15 hours, 20 minutes ago -
Google’s 10-year-old Chromecast is busted, but a fix is coming
by
Alex5723
1 day, 22 hours ago -
Expand the taskbar?
by
CWBillow
1 day, 22 hours ago -
Gregory Forrest “Woody” Leonhard (1951-2025)
by
Susan Bradley
24 minutes ago -
March 2025 updates are out
by
Susan Bradley
7 hours, 12 minutes ago -
Windows 11 Insider Preview build 26120.3380 released to DEV and BETA
by
joep517
2 days, 16 hours ago -
Update Firefox to prevent add-ons issues from root certificate expiration
by
Alex5723
2 days, 23 hours ago -
Latest Firefox requires Password on start up
by
Gordski
12 hours, 11 minutes ago -
Resolved : AutoCAD 2022 might not open after updating to 24H2
by
Alex5723
3 days, 11 hours ago -
Missing api-ms-win-core-libraryloader-11-2-1.dll
by
IreneLinda
5 hours, 55 minutes ago -
How Much Daylight have YOU Saved?
by
Nibbled To Death By Ducks
2 days, 13 hours ago -
A brief history of Windows Settings
by
Simon Bisson
2 days, 7 hours ago -
Thunderbolt is not just for monitors
by
Ben Myers
7 hours, 2 minutes ago -
Password Generators — Your first line of defense
by
Deanna McElveen
2 days, 11 hours ago -
AskWoody at the computer museum
by
Will Fastie
1 day, 11 hours ago -
Planning for the unexpected
by
Susan Bradley
2 days, 12 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.