sequential text and numbers problem
 

I need a formula that will help me generate a sequential list. My values are
as follows note that the "+" is strictly text and has no mathematical
significance): 0+83, 0+88, 0+93, 0+98, 1+03, 1+08, 1+13, .... The number
before and after the "+" change per each report but the number after the "+"
always increases by 5. The number before the "+" increases by 1 once the
numbers after the "+" exceed 100 (so that there are only ever 2 space values
after the "+").

Someone please help as I have to generate hundreds of these reports.

FYI the numbers reference a station on a CAD drawing and I will later be
inputting voltage potential data in the cells adjacent the stations so that
the report will show a location corresponding to a potential.

sequential text and numbers problem
 

enter:

=IF(ROW(A73)+4*(ROW(A1)-1)100,1,0) & "+" & RIGHT(ROW(A73)+4*(ROW(A1)-1),2)


and copy down.
--
Gary''s Student - gsnu200909


"patrickmcdiver" wrote:

I need a formula that will help me generate a sequential list. My values are
as follows note that the "+" is strictly text and has no mathematical
significance): 0+83, 0+88, 0+93, 0+98, 1+03, 1+08, 1+13, .... The number
before and after the "+" change per each report but the number after the "+"
always increases by 5. The number before the "+" increases by 1 once the
numbers after the "+" exceed 100 (so that there are only ever 2 space values
after the "+").

Someone please help as I have to generate hundreds of these reports.

FYI the numbers reference a station on a CAD drawing and I will later be
inputting voltage potential data in the cells adjacent the stations so that
the report will show a location corresponding to a potential.

sequential text and numbers problem
 

If the list is required in a column the following is a suggestion, where x is
the starting row number -1 (so if list starts in row 5 x=4). Works
independandantly of which column the list is in.

=IF(INT((((ROW()-x)*5)+3)/100)=0,"",INT((((ROW()-x)*5)+3)/100))&"+"&IF(LEN(MOD(((ROW()-x)*5)+3,100))=1,"0"&MOD(((ROW()-x)*5)+3,100),MOD(((ROW()-x)*5)+3,100))

There is probably a neater way, but appears to work...
--

Kind regards

Rik


"patrickmcdiver" wrote:

I need a formula that will help me generate a sequential list. My values are
as follows note that the "+" is strictly text and has no mathematical
significance): 0+83, 0+88, 0+93, 0+98, 1+03, 1+08, 1+13, .... The number
before and after the "+" change per each report but the number after the "+"
always increases by 5. The number before the "+" increases by 1 once the
numbers after the "+" exceed 100 (so that there are only ever 2 space values
after the "+").

Someone please help as I have to generate hundreds of these reports.

FYI the numbers reference a station on a CAD drawing and I will later be
inputting voltage potential data in the cells adjacent the stations so that
the report will show a location corresponding to a potential.

sequential text and numbers problem
 

Ooops didn't realise significance of 0 for items less than 100, makes it less
complex though...

=INT((((ROW()-x)*5)+3)/100)&"+"&RIGHT(MOD(((ROW()-x)*5)+3,100)+100,2)

--

Kind regards

Rik


"RikNeedsHelp" wrote:

If the list is required in a column the following is a suggestion, where x is
the starting row number -1 (so if list starts in row 5 x=4). Works
independandantly of which column the list is in.

=IF(INT((((ROW()-x)*5)+3)/100)=0,"",INT((((ROW()-x)*5)+3)/100))&"+"&IF(LEN(MOD(((ROW()-x)*5)+3,100))=1,"0"&MOD(((ROW()-x)*5)+3,100),MOD(((ROW()-x)*5)+3,100))

There is probably a neater way, but appears to work...
--

Kind regards

Rik


"patrickmcdiver" wrote:

I need a formula that will help me generate a sequential list. My values are
as follows note that the "+" is strictly text and has no mathematical
significance): 0+83, 0+88, 0+93, 0+98, 1+03, 1+08, 1+13, .... The number
before and after the "+" change per each report but the number after the "+"
always increases by 5. The number before the "+" increases by 1 once the
numbers after the "+" exceed 100 (so that there are only ever 2 space values
after the "+").

Someone please help as I have to generate hundreds of these reports.

FYI the numbers reference a station on a CAD drawing and I will later be
inputting voltage potential data in the cells adjacent the stations so that
the report will show a location corresponding to a potential.