Trouble with SetCellFormula after creating new Worksheets with numbers in sheet name

Feb 25, 2010 at 4:37 PM

I have some sample code I put together that creates a worksheet with data for a lookup, and then creates another separate sheet with cells that use a lookup on the first spreadsheet created.  The issue is that when I call SetCellFormula with the formula, the references to the original sheet get garbled.

New Sheet 1 is called Raw2010.  I stuff in a row with column headers and then a single row with data representing the data to be looked up.

New Sheet 2 is called January2010 and it too has a row with column headings and a single row of data.  One of those cells uses a lookup formula to the Raw2010 sheet to get a value.  This is the formula being put in:

LOOKUP($A2,'Raw2010'!$D$2:'Raw2010'!$D$64000,'Raw2010'!$B$2:'Raw2010'!$B$64000) & " - Delivery"

The formula in there when I open it is this:

LOOKUP($A2,'SIC2010'!$D$2:'SIC2010'!$D$64000,'SIC2010'!$B$2:'SIC2010'!$B$64000) & " - Delivery"

Why would it change Raw2010 to SIC2010 in the formula?

After seeing this I tried to use a different name for the Raw2010 so I changed the worksheet name in the code to RawData2010 and in the formula reference and now it comes out as RawDAIAG2010.

I found this strange so I change the RawData2010 sheet name to just RawData and then the formula reference to match and it works just fine.

So I guess the question is, why would having numbers in the sheet name cause this issue in the formula reference?

 

Darren

 

 

Feb 25, 2010 at 4:42 PM

Update!

This issue does not occur if the number is in front of the name for the sheet name and formula reference.

Naming the lookup sheet 2010Raw or 2010RawData works.  Must be some kind of bug in there somewhere.

Darren

Coordinator
Feb 25, 2010 at 9:13 PM

Hi Darren,

Thanks for your feedback.

Cell formulas are managed using a regular expression that seeks out cell references and keeps them up-to-date when worksheet geometry changes (for example, when a row or column is inserted into or deleted from the worksheet). This process is essential to keeping all the formulas correctly in sync.

However, the regular expression process is a very simplified attempt to mimic what Excel does and there are clearly situations where it is incapable of complying with everthing that the OpenXML spec defines. In fact, the OpenXML spec just for formulas runs to many pages, so it would not be possible to implement this functionality using a regex - it would require some extremely complex parsing code.

In your specific situation, it seems that your sheet names are being mistaken for cell references (because they begin with a sequence of alpha characters and end with a sequence of numeric characters). This clearly requires an improvement to the regex (so I will log this as a bug) but the workaround solution is exactly what you have done - slightly modifying the format of your sheet names will prevent them from being incorrectly matched as cell references.

I'll post an update here when a the regex has been updated.

Tim