Excel – I need a cell to automatically fill when data is entered into a neighboring cell

auto-form-fillmicrosoft excelvlookup

I would like cells E5-E39 to automatically fill with data in cell P1 when data is entered into cells B5-B39. So when something is entered in cells B5-B39, E5-E39 are auto filled with the info in P1.


Best Answer

This can be done using a rather simple formula:


It will check if the cell in column B of the same row is empty. If it is empty, it will fill in an empty string to the cell containing it, if not it will fill in whatever the current value of P1 is. Since it also references the values in column B it will update if you change the value there. The $-signs make it so that the references directly after it doesn't change as you copy it down the worksheet. That means as you copy it down, or whereever, only the "5" i B5 will change, the other references will stay the same.

Glad we finally figured out what the issue was, kinda surprised that the syntax for Excel isn't consistent worldwide, really.