Can anyone help me select a numbering format in Excel that recognizes chapter
and verses in scripture? I.e., I'm doing a running thematic development in
Mark so one field would have "1:1-8" and the next reference to it would be
"1:9-13" etc. Is there a way I could do a formula where the program will
either add verses without changing the chapter or am I left to formatting it
as 'general'?

JBR,

You could use four columns to create your references.

Starting in row 2, Column A has the chapter, column B the starting verse, column C the ending verse,
and column D the reference-creation formula. In A2:C2, enter the first reference values, then:

In D2, enter the formula =A2 & ":" & B2 & "-" & C2
In A3 enter the formula =A2
In B3 enter the formula = C2 +1
In C3 enter the formula = B3 +1
Copy D2 down to D3, then copy A3:D3 down for as many rows as you want.

When you change chapter, starting or ending verse from those suggested by the formulas, simply
overwrite the formula with the value that you want.

HTH,
Bernie
MS Excel MVP


"JBR" wrote in message
...
Can anyone help me select a numbering format in Excel that recognizes chapter
and verses in scripture? I.e., I'm doing a running thematic development in
Mark so one field would have "1:1-8" and the next reference to it would be
"1:9-13" etc. Is there a way I could do a formula where the program will
either add verses without changing the chapter or am I left to formatting it
as 'general'?

How would Excel determine that the next verses after 1:1-8 should be 1:9-13?

1:1-8 is 9 verses. 1:9-13 is 5 verses

Is there any order of incremetation to follow?


Gord Dibben MS Excel MVP


On Tue, 15 Apr 2008 09:23:00 -0700, JBR wrote:

Can anyone help me select a numbering format in Excel that recognizes chapter
and verses in scripture? I.e., I'm doing a running thematic development in
Mark so one field would have "1:1-8" and the next reference to it would be
"1:9-13" etc. Is there a way I could do a formula where the program will
either add verses without changing the chapter or am I left to formatting it
as 'general'?

You could also set your column B to equal the column C value from the row
above plus 1 (so B3 would contain the formula "=C2+1"). You would always
have to supply the ending verse, because there is no way that Excel will
know/nor could you program it to know how many verses you wanted to reference
on any given line (Gord's question). Personally, I've always just hand
entered the information then used a formula like Bernie suggested to get the
final formatting to look right.

"Bernie Deitrick" wrote:

JBR,

You could use four columns to create your references.

Starting in row 2, Column A has the chapter, column B the starting verse, column C the ending verse,
and column D the reference-creation formula. In A2:C2, enter the first reference values, then:

In D2, enter the formula =A2 & ":" & B2 & "-" & C2
In A3 enter the formula =A2
In B3 enter the formula = C2 +1
In C3 enter the formula = B3 +1
Copy D2 down to D3, then copy A3:D3 down for as many rows as you want.

When you change chapter, starting or ending verse from those suggested by the formulas, simply
overwrite the formula with the value that you want.

HTH,
Bernie
MS Excel MVP


"JBR" wrote in message
...
Can anyone help me select a numbering format in Excel that recognizes chapter
and verses in scripture? I.e., I'm doing a running thematic development in
Mark so one field would have "1:1-8" and the next reference to it would be
"1:9-13" etc. Is there a way I could do a formula where the program will
either add verses without changing the chapter or am I left to formatting it
as 'general'?