Automatically populate column based on dropdown input, dependend on number of rows

[snezana]

Hi everyone,

I've got a nice Excel challenge that I can't solve.

I have a file where the year and quarter for the data have to be selected in a dropdowns (J1 and J2). Then, the columns 'YEAR' and 'QUARTER' should automatically be filled with the selections made. That I can manage, but then I want this to happen for every row that is populated, a number which can differ. For example, sometime the column "Country description" can have 5 entries, but i can also have 20. So I want the Year and quarter columns to, respectively have 5 and 20 rows filled with the Year and Quarter selections. So I'm looking for a way to have this adapt to the number of rows filled in in columns C - F.

Example file attached If anyone can help me with this that would be great!

Excel Year Quarter.xlsx

[Moo the Dog]

If you're just looking to populate column A (year) with the value stored in J1, then put this formula in A2 and fill down farther than you think you will ever need to go:

=IF(C2<>"",$J$1,"")

For column B, use this in B2 and fill down the same length:

=IF(C2<>"",$J$2,"")

- Moo

[snezana]

Thanks for the reply!

I get an error message if I enter that formula. However, when I enter: =IF(C1<>"";$J$1) it does work. Small problem: when rows aren't filled in (so if column C=empty), Column A displays the word "FALSE", while I want it to simply not do anything and be blank. I'm sure I'm doing something wrong at this point?

[Moo the Dog]

Part 1: Yes, since you are in the Netherlands, your country settings use ; instead of , for argument separation... so that is why it works with ;

Part 2: You left out the part of the formula that "blanks out" the cell if there is nothing in column C...

My original formula (but using ';'): =IF(C2<>"";$J$1;"")

- Moo