• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel formula MAX +1

R

Rodrigues

Member
Hi All
I'm wondering if someone could help me please with MAX formula.
On sheet2 Cell C4 have a MAX formula, which works perfectly fine, however I would like to be able to add a (+1) to that formula, so works as a sequential number. Have tested on Cell C14 (sheet2) this formula, =IF(D14="","",MAX(Sheet2:Sheet3!C6:C1000)+1) but get a circular reference warning. the result I was expecting was 19 and so on for the others cells on column C.

Thanks in advance
Regards
R
 

Attachments

  • Book1.xlsx
    35.4 KB · Views: 6
Hi ,

What exactly do you want to do ?

The formula :

=IF(D14="","",MAX(Sheet2:Sheet3!C6:C1000)+1)

will give a circular reference because it is entered in cell C14 , which is in between C6 and C1000.

Change it to :

=IF(D14="","",MAX(Sheet2:Sheet3!C6:C13)+1)

and you will not get the error.

Narayan
 
Hi Narayank991

When user key in a date on column D (I.e.Sheet2 D14, cell C14 should show 19). the formula will look at the last highest number used on both Sheet2 and Sheet3 , and populate on C14 the last number used +1. Would like to make it auto sequential, rather the operator have to input the number.
Thanks for your help.
Regards
R
 
Hi ,

So why not use :

=IF(D14="","",$C$4+1)

After all , C4 in Sheet2 already has a formula which returns the maximum number present in the range C6:C13 in Sheet2 and Sheet3.

Narayan
 
Sorry Narayank, haven't explained properly.
The range has to be at least C6:C2000, so I have the same problem.
Any ideas?
Thanks R
 
Sorry, forgot to say that, sheet3 works as a archive of sheet2, when row(s) on sheet2 are set to complete.
When set to complete the entire row is moved to sheet3.
I need to look for the last number on both sheets 2&3 C6:C2000.
Thanks for your help.
R
 
You must log in or register to reply here.
Back
Top