Consecutive Numbering and or custom lists

  • Thread starter Thread starter John Smith
  • Start date Start date
J

John Smith

I have the following formula or something similar that I want to
copy/fill to following cells but increasing some of the numbers
consecutively:

=IIf([m1rr] And [m1dbed] And [m1vd]=-1,1,IIf([m1rr]=-1 And [m1dbed]=-1
And [m1vd]=0,2,IIf([m1rr]=-1 And [m1dbed]=0 And
[m1vd]=0,3,IIf([m1rr]=0 And [m1dbed]=-1 And [m1vd]=-1,4,IIf([m1rr]=0
And [m1dbed]=-1 And [m1vd]=0,5,IIf([m1rr]=0 And [m1dbed]=0 And
[m1vd]=0,6,IIf([m1rr]=-1 And [m1dbed]=0 And [m1vd]=-1,7)))))))

This would seem to complex for custom fill. Is there anyway of doing
this other than manually adjusting/editing the formula in each line?

Thanks John
 
John,

That isn't a working formula for Excel. Post back with one, and then we'll
figure it out.

HTH,
Bernie
MS Excel MVP
 
Bernie Deitrick said:
John,

That isn't a working formula for Excel. Post back with one, and then we'll
figure it out.

HTH,
Bernie
MS Excel MVP
Click to expand...


You are right - this is actually an Access formula but the problem is
the same. I was hoping that if I could solve the problem in Excel
with a custom fill then at least I could cut and paste into Access. I
think the only difference between the two is the use of 'Iff' rather
than 'If'?

John
 
Bernie Deitrick said:
John,

That isn't a working formula for Excel. Post back with one, and then we'll
figure it out.

HTH,
Bernie
MS Excel MVP
Click to expand...


I think this would be the excel equivalent:

=if(and(m1rr = -1, m1dbed = -1, m1vd = -1), 1, if(and(m1rr = -1,
m1dbed = -1, m1vd = 0), 2, if(and(m1rr = -1, m1dbed = 0, m1vd = 0), 3,
if(and(m1rr = 0, m1dbed = -1, m1vd = -1), 4, if(and(m1rr = 0, m1dbed =
-1, m1vd = 0),5, if(and m1rr = 0, m1dbed = 0, m1vd = 0),6,
if(and(m1rr = 0, m1dbed = -1, m1vd = -1),7,0)))))))
 
John,

Are "m1rr" and "m1dbed" data table entries? Which numbers do you want
autoincremented?

HTH,
Bernie
MS Excel MVP
 
Bernie Deitrick said:
John,

Are "m1rr" and "m1dbed" data table entries? Which numbers do you want
autoincremented?

HTH,
Bernie
MS Excel MVP
Click to expand...

Hi Bernie, Yes 'm1rr', 'm1dbed' and 'm1vd' are data table entries/cell
names. It is the '1' within these names that I am looking to increment
- not the numbers in the other part of the statement.

John
 
John,

I would suggest using four cells in one row: three with formulas:

=INDIRECT("m" & ROW(A1) & "rr")
=INDIRECT("m" & ROW(A1) & "dbed")
=INDIRECT("m" & ROW(A1) & "vd")

and the fourth with your formula, where you reference the three 'INDIRECT'
formula cells.

Copying the formulas down would increment the A1 in the ROW function, and
get the m1rr, m2rr, m3rr increment that you desire.

Whether those references are meaningful or not remains to be seen.....

HTH,
Bernie
MS Excel MVP
 
Back
Top