Formula which will populate the next empty row without VBA

[andrewmrichards]

Many years ago I created a spreadsheet which had two sheets - Sheet1 contained a "form" structure, and Sheet2 contained the entered data. Iterative calculation was turned on, and set to 1 iteration. The user entered data into the "form", and formulas on Sheet2 populated the next empty row with the entered value when that sheet was calculated. No VBA code was used.
I've been trying to recreate this, but cannot.

As a simplified example, let's say F1 contains the space for a user to type the next "first name" value

Within the "list" section, First names should populate column A. A1 contains the heading, so the first "first name" should go into A2.

A2 contains the following formula:

=IF(A2<>"",A2,IF(A1="","",$F$1))

So, in essence I'm saying "If this cell is blank, and the one above isn't, then it's my turn to be filled in - copy whatever's in F1."

I know that this worked many years ago (this would have been 1996), but it no longer does. Every cell is populated; it's as though A2 is calculating, "It's my turn" and then A3 calculates and says "Well, I'm blank, but A2 isn't so it's my turn" and it fills in, then A4 does the same, and so on. Again, I have iterative calculations set on, with 1 iteration.

Reading lots on the Microsoft website (especially at Excel Recalculation) it seems that in 2002 and again in 2007, there were significant changes to the way in which calculations were organised and then performed. So - is this something that would have been possible then but no longer is? Or am I mis-remembering something about my formula from 25 years ago?!

Any ideas on how I could do this would be appreciated.

Incidentally, I know that this would be trivial with VBA, but I'm trying to create an example for a client who doesn't want to go down the VBA route...

Thanks a lot
Andrew

 

[Sufiyan97]

Your this formula
=IF(A2<>"",A2,IF(A1="","",$F$1))

should not be in A2, it could be in any of other cells or you need to change the formula

 

[Eric W]

It's been a while since I played with iterative formulas. Your formula "should" work, but it doesn't. It looks like there's something odd going on with how Excel treats an empty cell, as a 0 or a "". But if you have Excel 365 I came up with an interesting alternative:

Book3
	A	B	C	D	E	F
1	List					rr
2	0
3	x
4	y
5	z
6	t
7	rr
8
Sheet1

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(INDEX(A2#,SEQUENCE(ROWS(A2#)+IF(AND(F1<>"",F1<>INDEX(A2#,ROWS(A2#))),1,0))),F1)
Dynamic array formulas.

Every time you put a non-blank value in F1, it's added to the A2 list. The 0 appeared when the formula was first entered. I haven't found a way to avoid that.

 

[andrewmrichards]

Hi Eric
Thanks so much for your help with this - yes, your sample works for me...
I'm going to have to spend some time picking through exactly what's going on in it though ... Not very up-to-date with spill references, for example!
Thanks for your help.

 

[andrewmrichards]

So, tackling this afresh on a new day - I'm really appreciative of your suggested formula, which works really well (aside the blank first row). However, the requirement that Office365 are pretty stringent for it to work - many of my clients are using Excel versions other than O365, and so for them this won't work.
If anyone is able to help me with correcting the original function I'd proposed - or conclusively confirm that it no longer works - I'd be really grateful.
Many thanks
Andrew

 

[Eric W]

It looks like it was the blank/0 issue. I got this to work:

Book3
	H	I	J
1	Cyndi		List
2			Andy
3			Bob
4			Cyndi
5
6
Sheet1

Cell Formulas
Range	Formula
J2:J6	J2	=IF(AND(J2<>"",J2<>0),J2,IF(J1="","",IF(OR($H$1="",$H$1=J1),"",$H$1)))

 

[andrewmrichards]

Perfect. Thank you for that. I tweaked the code to test whether the match to the previous mark extends to more than one column (in reality, there is a first name, last name, and other columns to the "form") as it's possible that we could have two people called Bob (or whatever). I then tweaked further to use named ranges, and to use XLOOKUP and INDEX to look for the appropriate fields in the form, to make it easier to change the field names in future requirements. It makes the formula look slightly horrendous, but gives exactly the functionality I needed.

I've posted the final result I came up with in case it's of use to anyone else...

Thanks so much again for your help!
Andrew

Here's the form:

Data Entry Form
First
Last
Subject
Mark

And here's the results sheet:

Excel sample calculations.xlsm
	A	B	C	D
1	First	Last	Subject	Mark
2	Andrew	Richards	English	99
3	Angela	de Sousa	Portuguese	77
4	Ian	Black	Maths	66
Entry form 2 results

Cell Formulas
Range	Formula
A2:D4	A2	=IF(AND(A2<>"",A2<>0),A2,IF(A1="","",IF(OR(XLOOKUP(A$1,INDEX(EntryForm2,,1),INDEX(EntryForm2,,2))="",AND(XLOOKUP($A$1,INDEX(EntryForm2,,1),INDEX(EntryForm2,,2))=$A1,XLOOKUP($B$1,INDEX(EntryForm2,,1),INDEX(EntryForm2,,2))=$B1,XLOOKUP($C$1,INDEX(EntryForm2,,1),INDEX(EntryForm2,,2))=$C1,XLOOKUP($D$1,INDEX(EntryForm2,,1),INDEX(EntryForm2,,2))=$D1)),"",XLOOKUP(A$1,INDEX(EntryForm2,,1),INDEX(EntryForm2,,2)))))

 

[Eric W]

Whew! That is something!

A couple of thoughts. First, you said you wanted to avoid Excel 365 specific functions, but XLOOKUP is only in Excel 365. You might need to switch to an INDEX(MATCH structure.

Second, since you're looking at a single record with multiple fields, it might be nice to move them as a group. This version does that:

Book3
	A	B	C	D	E	F	G
1	Data Entry Form			First	Last	Subject	Mark
2				Andrew	Richards	English	99
3	First	Ian		Angela	de Sousa	Portuguese	77
4	Last	Black		Ian	Black	Maths	66
5	Subject	Maths
6	Mark	66
7
Sheet1

Cell Formulas
Range	Formula
D2:G7	D2	=IF(AND(D2<>"",D2<>0),D2,IF(D1="","",IF(OR($B$6="",$B$6=$G1),"",INDEX($B$3:$B$6,COLUMNS($D2:D2)))))

These formulas all look at the last field, Mark, and will update when that changes. The weakness with this is if 2 consecutive entries have the same mark. I'm still pondering what to do about that.

 

[andrewmrichards]

Whew! That is something!

A couple of thoughts. First, you said you wanted to avoid Excel 365 specific functions, but XLOOKUP is only in Excel 365. You might need to switch to an INDEX(MATCH structure.

Second, since you're looking at a single record with multiple fields, it might be nice to move them as a group. This version does that:

Book3
	A	B	C	D	E	F	G
1	Data Entry Form			First	Last	Subject	Mark
2				Andrew	Richards	English	99
3	First	Ian		Angela	de Sousa	Portuguese	77
4	Last	Black		Ian	Black	Maths	66
5	Subject	Maths
6	Mark	66
7
Sheet1

Cell Formulas
Range	Formula
D2:G7	D2	=IF(AND(D2<>"",D2<>0),D2,IF(D1="","",IF(OR($B$6="",$B$6=$G1),"",INDEX($B$3:$B$6,COLUMNS($D2:D2)))))

These formulas all look at the last field, Mark, and will update when that changes. The weakness with this is if 2 consecutive entries have the same mark. I'm still pondering what to do about that.

That's cool - thank you for your thoughts.

I did hesitate before posting my reply - not because I'd remembered that XLOOKUP ties me back to O365, but because it's one of those cases where actually a good old-fashioned VLOOKUP is actually rather simpler. And, of course, that would be available in versions back to Excel 84.

But I do like the idea of a single call to INDEX with COLUMNS to copy across the data.

I've come up with a hybrid of sorts:

Excel sample calculations.xlsm
	P	Q	R	S
1	First	Last	Subject	Mark
2	Andrew	Richards	English	99
3	Kate	Winslet	Goddess study	100
Data entry form (2)

Cell Formulas
Range	Formula
P2:S3	P2	=IF(AND(P2<>"",P2<>0),P2,IF(P1="","",IF(OR(VLOOKUP(P$1,EntryForm2,2,FALSE)="",VLOOKUP($S$1,EntryForm2,2,FALSE)=$S1),"",INDEX($C$5:$C$8,COLUMNS($P2:P2)))))

Named Ranges
Name	Refers To	Cells
EntryForm2	='Data entry form (2)'!$B$5:$C$8	P2:S3

This uses VLOOKUP to remove the reliance on O365, and could, of course, be extended (with the same method I used earlier, unless I can find a simpler one) to include a check for multiple identical fields, whilst maintaining the idea of being able to rename fields without too much pain if required.

<sigh> Why don't I just learn to say 'Sorry - it can't be done'

Thanks again for your help.
Andrew

 

[Eric W]

<sigh> Why don't I just learn to say 'Sorry - it can't be done'

?

Glad to help! It was interesting to figure out.