Replies

The 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

Could 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

Hi 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

Your 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

Does 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

Cheers for the hints.. Once I’ve finished moving domains arund and setting up mailboxes I’ll have a look at this.

Peter

Hey, 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

Thanks Hans… Hadn’t considered the security implications… Harumph!

Maybe have to consider Zimbra over Hosted Exchange.

Cheers
Peter

Problem 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

Steve/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

I 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

Thanks Steve.. Much appreciated.

Cheers
Peter

Hi 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

(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

Thanks 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