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.


  • 1
    =if(isblank($B5);"";$P$1)? – eirikdaude Mar 23 '15 at 16:10
  • 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.. – Kyle Hinkebein Mar 23 '15 at 16:27
  • Seems to work for me. See sample data @Kyle – eirikdaude Mar 23 '15 at 16:32
  • I'm not supposed to copy the ? correct? – Kyle Hinkebein Mar 23 '15 at 16:34
  • 1
    Try the formula using commas (,) instead of a semicolons (;). Those are region dependent. – CharlieRB Mar 23 '15 at 16:46

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.

  • 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.. – Kyle Hinkebein Mar 23 '15 at 18:06

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.