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

Question

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.

Thanks.

Solution

This can be done using a rather simple formula:

=IF(ISBLANK($B5),"",$P$1)

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.

Comments

[eirikdaude]

=if(isblank($B5);"";$P$1)?

[Kyle Hinkebein]

I copied and pasted this into one of my C cells but it didn't work?? Thanks for taking the time to help me.. It's coloring the ( ) around B5 red..

[eirikdaude]

Seems to work for me. See sample data @Kyle

[Kyle Hinkebein]

I'm not supposed to copy the ? correct?

[eirikdaude]

Correct. Do you get any description of your error when putting the formula into the cell?

[Kyle Hinkebein]

No description, it keeps saying there's a problem with formula but no actual description. =IF(ISBLANK($B5);"";$P$1) it just keeps highlighting the parenthesis around B5 red and the B5 blue.

[eirikdaude]

Try opening the formula in the wizard by selecting the cell and clicking the small fx at the top (imgur.com/KOXxMBl) to see how each part of the formula evaluates. Then edit in what results you get in your original question (or just take a screenshot of what happens and post to e.g. imgur), and I'll attempt to write up an answer

[CharlieRB]

Try the formula using commas (,) instead of a semicolons (;). Those are region dependent.

[Kyle Hinkebein]

Works perfect man, it was the semicolons, much thanks.. If you wanted to post this as an answer now I'll check it for you so you get credit..

[Kyle Hinkebein]

Yep works perfect, thanks for explaining it also, I am trying to learn and not just ask questions. That is surprising they aren't the exact same..