0

I use this formula to find highest value in column A of other sheet, and then add 1 to it to get new unique number for a record ID, and I use...

=if(B3<>"",max(Backend!A3:A)+1,)

with this formula in cell underneath to get a sequential record number for the rest of the cells.

=if(B4<>"",A3+1,)

It's used on a sheet where the amount of values in column B shouldn't reach more than 80-100 values, so I have 100 IF statements in column A.

How do I turn these two formula's into an array? Or is what I am trying to achieve possible?

0

Not sure what you trying to achieve but here's a try

={if(B3<>"",max(Backend!$A$3:$A)+1,);ArrayFormula(if(B4:B100<>"",A3:A100+1,))}

Try to provide us a sample with expected results.

  • Hi PaulJ, thanks for your help. This works on one cell, but it doesn't fill down. If I change B3 in the IF statement to B3:B, I get circular dependancy. What I was trying to achieve was for new records to check main data (backend) for last/highest record number, and to then +1 for a new unique record ID. I've now taken a script route that adds the formula and autofills to neighbour, then pastes as values. I should probably delete the question? – Ceebee86 Aug 20 at 13:48
  • Instead of an array with the mustaches {} try maybe to concatenate your results with formula1()&formula2() ? Or divide the formula in 2 columns to attain your expected result ? I think it'll simplify your problem to break it down in multiples pieces. – pjmg Aug 21 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.