I need to create a range with absolute address, i.e. $A$1:$A3 instead of A1:A3. I was able to get this done as follows:
sheet.Range(sheet.Cell(a, b).Address.ToStringFixed(), sheet.Cell(c, d).Address.ToStringFixed());
But that seems more like a hack than an elegant solution. Perhaps support for absolute addresses should be improved in ClosedXML?
Just as one suggestion, how about being able to do something like this:
sheet.Range(a, b, c, d, true);
Where fifth argument indicates that absolute (as opposed to relative) addressing is required.
Now, let me tell you how and why I ran into this. And perhaps I am doing it wrong.. I am creating list-validation of a range as follows:
var listRange = sheet.Range(...);
var dataRange = sheet.Range(...);
This code has the following problem. Let's say, my listRange is A1:A3 and my dataRange is B1:B3. Everything works great for cell B1. It is validated by the A1:A3 range. And I would expect every single cell in B1:B3 range to be validated against the same
A1:A3 range. However, supposedly because of the relative address of listRange, B2 is validated against A2:A4, B3 is validated against A3:A5, and so on.
So, perhaps I am not doing my list validation properly?