Customizing Named Ranges in Ediphi's Excel Add-in (Last Mile)

Ediphi's Excel Add-in (Last Mile) uses named ranges to find the right columns when it builds reports like the Detailed Estimate Report. Every column on your __Estimate   sheet, such as Bid Package or Trade Package, has a named range behind it. When you want a custom Sort Field in your report, you can add your own named range for that column.

Because Excel names can’t include spaces, dashes, parentheses, or most punctuation, if a header uses any of these characters, you’ll need to encode it first. This article walks you through the process.

To learn more about how to use and install Ediphi's Excel Add-in, click here.

In this article


How Named Ranges Work

Every named range in the add-in starts with a prefix that tells you what it points to:

  • \   points to a column
  • __   points to one or more rows of data, for example __lineItems  
  • _   points to a single cell value

To turn a column header into a named range, read the header from left to right:

  1. Letters, numbers, underscores (_ ), and periods (. ) stay as they are. Each run of these characters gets a backslash (\  ) added to the front, so Bid   becomes \Bid  .
  2. Every other character is replaced with a code. Spaces, dashes, parentheses, and other punctuation each become \x   followed by a two-digit code. A space becomes \x20  .
  3. Add an extra \  between special characters that sit side by side. In Google - Trade  , the space-dash-space becomes \x20\\x2d\\x20  . A special character followed by a word doesn't need one, because the word brings its own \  .

Example: Bid Package   becomes \Bid\x20\Package   (\Bid  , then \x20   for the space, then \Package  ).

Note: Each code is the character's number written in hexadecimal. You don't need to work these out yourself; use the Special Character Codes table below.


Adding a Named Range to Your Report

Follow these steps to bring a custom column, such as a custom Sort Field, into your report. This example uses Bid Package:

  1. Open your report workbook in Excel and launch Ediphi's Excel Add-in.
  2. Add a column for the field and enter its header, for example Bid Package.
  3. Select the column, then go to Formulas > Define Name. Excel suggests a name based on the header.
  4. Replace the suggested name with the encoded named range, for example \Bid\x20\Package  .
  5. Set Scope to the report worksheet, then click OK.
  6. In the add-in, click Refresh. The column fills in with data from your estimate.

Note: If you enter the header as-is (for example Bid Package  ), Excel shows an error when you click OK. Encode the header using the rules above and try again.


Special Character Codes

These are the characters you're most likely to see in a column header, and the code each one becomes.

Character Name Code
(space) Space \x20  
-   Dash / hyphen \x2d  
(   Open parenthesis \x28  
)   Close parenthesis \x29  
/   Slash \x2f  
&   Ampersand \x26  
,   Comma \x2c  
#   Hash / pound \x23  
%   Percent \x25  
+   Plus \x2b  
@   At sign \x40  
'   Apostrophe \x27  
"   Double quote \x22  
:   Colon \x3a  
;   Semicolon \x3b  
*   Asterisk \x2a  
?   Question mark \x3f  
!   Exclamation point \x21  
=   Equals sign \x3d  
.   Period No code (kept as-is)
_   Underscore No code (kept as-is)

Worked Examples

Column header Named range
Bid Package   \Bid\x20\Package  
Google - Trade Package_desc   \Google\x20\\x2d\\x20\Trade\x20\Package_desc  
Google - Sub Program Area (SPA)_desc   \Google\x20\\x2d\\x20\Sub\x20\Program\x20\Area\x20\\x28\SPA\x29\_desc  

Pro Tip: _desc   stays attached to the word before it because underscores never get a code. In Google - Trade  , notice the extra \   between each special character in \x20\\x2d\\x20  .


FAQs

  • Why do I get an error when I click OK?

Excel doesn't allow spaces, dashes, parentheses, or most punctuation in names. Encode the header using the rules in How Named Ranges Work, then try again.

  • Do underscores or periods need a code?

No. They're kept as they are, which is why _desc   stays attached to the word before it.

  • Why do some named ranges have \\  in a row?

That happens when two special characters sit next to each other, like the space-dash-space in Google - Trade  . The extra \   keeps each code separate.

  • My header has a character that isn't in the table. What do I use?

Contact our support team and we'll help you find the right code.

If you have any questions, feel free to contact our support team, and we'll be happy to help!

Still need help? Contact Us Contact Us