Daily posts of Excel tips…and other stuff
Archive for the ‘Names’ Category.
On Simon’s blog, sam comments regarding OFFSET vs. INDEX:
There is a huge performance hit.
…
You will notice hardly any difference in the Calculation times
But the recalculation times will significantly different in case of OFFSET- to the tune of 25-30 times slower compared Index.
I’ve heard this many times before, but I guess I’m just lazy. I’ve [...]
Hi all,
As some of you may be aware, Excel 2007 has been exhibiting an obscure but very annoying bug. On some rare occasions, Range Names may disappear from your workbook after saving it.
I have never been able to detect any pattern in this behaviour, it just happens on and off.
I’m happy to say Microsoft created [...]
To account for freight on incoming inventory, I allocate the freight charges proportionately over the inventory items by total value. Quickbooks doesn’t have a way to handle that, so I use a simple Excel spreadsheet to do the calculation.
The last entry is always the freight (or handling or whatever I want to distribute). [...]
Hi All,
As many of you know I give away a number of tools for Excel through my site www.jkp-ads.com. Today I have updated the two most popular downloads:
Name Manager (which I created together with Charles Williams, www.decisionmodels.com):
Most important change: the unused names filter now includes objects in its search, as well as VBA code. It [...]
When I create an external data table in Excel, I almost always want to have a named range that refers to every column. This way I can create array formulas and refer to column names rather than cell references. Which formula is easier to read?:
=SUM((LEFT(Assembly!Name,3)="AXI")*(Assembly!QuantityOnHand))
=SUM((LEFT(Assembly!B2:B174,3)="AXI")*(Assembly!G2:G174))
Another advantage is that the named ranges expand and [...]
As many of the frequenters of this great place will know, Charles Williams and I created The Excel Name Manager. The # 1 (FREE) tool on the web to work with defined names in Excel.
Well, we do occasionally get feedback on this tool (which is always appreciated!) and in all these years, we got two [...]
Hi all,
Now that Office 2007 is RTM, Charles Williams and I have released an Excel 2007 version of our Name Manager.
The only differences with the 97-2003 version are that it is saved in native 2007 xlam format and uses the new ribbon UI:
Enjoy.
Regards,
Jan Karel Pieterse
JKP Application Development Services
Hi everyone,
Since no new issues were reported for quite some time, I decided it is time to release Name Manager version 4.0.
Look here and here for previous posts and discussions on this version.
Enjoy!
Regards,
Jan Karel Pieterse
JKP Application Development Services
