Named Range, Pivot Table failure
Hello, I've come across a problem that I've not seen before and I can't find a fix anywhere. My source data is 31 columns wide and is bound under a Dynamic Named Range using the OFFSET, COUNTA formula. When I test the range on the source it's fine and goes from A1 to AE3483 without any problems; however, when I apply the range to a pivot table, it stops at column AB. As a consequence, data in the final columns does not appear in the list for my pivot table and I cannot use it. (For this task I must use a Dynamic Range as the source data is constantly being updated and I want to be able to easily refresh my pivot tables without having to manually adjust the ranges - because there are many) Can anyone offer some advice into why the pivot table isn't counting across all columns? Here is an example of the formula being used: =OFFSET('Dash Source'!$A$1:$A$500000,0,0,COUNTA('Dash Source'!$A$1:$A$500000),COUNTA('Dash Source'!1:1))
Named Range (Apply Names) formula being erased bug
There are several issues with named ranges - I'll start with the formula being erased problem. PROBLEM #1 - FORMULA GETTING ERASED I've uploaded a sample file (testnamedrange3) to go with this example - here's how to replicate it. Create a simple spreadsheet. I have two input cells (Input1 and input 2). I have NOT named them yet. Create other cells that refer to those cells via formula (e.g. look at cells c7-c9). Now go give cells C3 and C4 names (like Input1 and Input2). Now go on to the formulas tab and select 'Define Name' and then 'Apply Names'. I highlight Input1 and input2 and press 'OK'. Rows 7 and 8 update correctly (the formulas now read "Input1 + Input2" and "Input1 * Input2"... It's row 9 that's the problem. Any additional calculations or formulas that were in the cell get erased. E.g. - originally the cell said c3*c4*100. After you apply names the c3*c4 exists -- but it has erased the *100 portion. This only happens on the Windows version of Excel. It updates correctly on the Mac (I'm using the most recent versions of both - both 64-bit). PROBLEM #2 If you have a cell that refers to another cell and includes the 'Sheet1' information in the formula (e.g. if you are referring to a prior sheet -- say something like "='Sheet1'!$C$3"). When you look at the named range you can see that the cell C3 (called Input1) actually has the full name exactly like the formula (e.g. Sheet1$c$3) -- but when you apply names it doesn't update this (whether it's on the same page as the named range cell or on a different page). That's it for now - let me know if you have any additional questions on repeating these bugs.