Reply to thread

Message

<blockquote data-quote="deleteme123456" data-source="post: 4427690" data-attributes="member: 65832">Excel allows you to name ranges (and by definition a single cell is a range). I don't have o2007 on my work PC so you'll have to dig in the ribbon for it, but in o2003 it's Insert -&gt; Name -&gt; Define (ALT-I, N, D may work). I vaguely recall it being on the data tab of the ribbon.Basically you define a range either by a fixed absolute or relative reference (handy if say you're making a character sheet, you could have a cell that calculates your STR mod and name it STRMOD). Any formula or VBA function can access that range, and if it resolves to a single cell, then you just use it like you would $B$25 or wherever your strmod is. This definition is a little more interesting, in that it uses OFFSET and COUNTA to find the boundary of the table. The width of the table is found by looking across row 1 for all nonblanks (if you have a blank interposing, this won't work, which is a minor problem but workable, and I could fix it but I'm lazy <img src="https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f609.png" class="smilie smilie--emoji" loading="lazy" width="64" height="64" alt=";)" title="Wink&nbsp; &nbsp; ;)"&nbsp; data-smilie="2"data-shortname=";)" />)The height of the table .. well, I needed something reliable to count, so I used PC names (column b). Again, I was lazy / sloppy and only did a counta instead of a match(*) but oh well. So that way the range is always dynamically checked, if you add a PC, as long as they have a name, you're golden, and you can add more columns as long as they have a header (hard not to in a list).</blockquote>

[QUOTE="deleteme123456, post: 4427690, member: 65832"] Excel allows you to name ranges (and by definition a single cell is a range). I don't have o2007 on my work PC so you'll have to dig in the ribbon for it, but in o2003 it's Insert -> Name -> Define (ALT-I, N, D may work). I vaguely recall it being on the data tab of the ribbon. Basically you define a range either by a fixed absolute or relative reference (handy if say you're making a character sheet, you could have a cell that calculates your STR mod and name it STRMOD). Any formula or VBA function can access that range, and if it resolves to a single cell, then you just use it like you would $B$25 or wherever your strmod is. This definition is a little more interesting, in that it uses OFFSET and COUNTA to find the boundary of the table. The width of the table is found by looking across row 1 for all nonblanks (if you have a blank interposing, this won't work, which is a minor problem but workable, and I could fix it but I'm lazy ;)) The height of the table .. well, I needed something reliable to count, so I used PC names (column b). Again, I was lazy / sloppy and only did a counta instead of a match(*) but oh well. So that way the range is always dynamically checked, if you add a PC, as long as they have a name, you're golden, and you can add more columns as long as they have a header (hard not to in a list). [/QUOTE]

Verification