I am attempting to use dynamic named ranges to make it so I can pre-set the column portion of the print ranges in a workbook without assuming a number of rows. So I opened the name manager and redefined the refers to a dynamic declaration based on the number of filled cells in a particular column and it worked just fine.

But when I tried to do the same thing on the next sheet in the workbook, it set the range (correctly) based on the formula and the current state of the worksheet, but the range was not dynamic. The formula looks correct in the name manager when I add it to the name manager, but when I close the name manager and re-open it the range has been converted to a static reference.

This is the structure of the equation I used in the refers to box, =offset(NameOfTopLeftCellInPrintRange,0,0,counta(referenceToColumn)+4,17) and it became ='SheetName'!$B$2:$R$21 when I go back into the name manager after setting the range, but only for the second sheet's print range.

Thanks in advance if anyone has any ideas about what might be causing this behavior, and possible remedies.

EDIT: I'm not posting this as an answer because I still would like to know what was causing this and/or how to actually fix the issue directly, but I have a messy fix. I noticed that it was just one of the worksheets that was exhibiting this behavior. I tried copying the sheet and the copy had the same problem. So I just copied a different sheet that was working, and moved all the data from the problem sheet to the other sheet and everything worked.

EDIT 2: The problem appears to have re-surfaced in this document after I re-opened it (ie, it was working before I . I think it may be related to print titles like it says here, but I can't afford to take out the print titles for this document.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.