Row Fill Automatically?


what forumla can I use to have my row fill automatically after i have chosen text from a drop down list in the first cell?

I want to select from the drop down in Cell A11 and have the text from sheet "SOI Abilities" to automatically fill in the cells Academic Skills, Cirricular Area, Strength When Ability is Developed



Surely that must depend upon what you want the other cells to show, relativ to the selection made in the drop-down I presume.
Variatus (rep: 2354) Nov 28, '17 at 8:31 pm
yes. that's what I want it do. I'm not even quite sure if I am asking the right question. 

After I have selected text from a drop down in the first cell in and on that same row I would like to have text from another sheet automatically fill in the rest of the row  - The way we have it now is just a load of drop downs and I am trying to aviod clicking on all of them.
heather00 (rep: 8) Nov 29, '17 at 6:19 pm
Basically, Don's answer below seems to be pointing in the right direction. If you need a more customised answer I suggest you attach a workbook to your question and explain the logic by which the text from the other sheet can be selected based on the selection in the first drop-down. Perhaps the workbook will be self-explanatory. Did you google for "Vlookup" or consult Excel help on that function?
Variatus (rep: 2354) Nov 29, '17 at 8:16 pm
Oh I just saw that i could attach a file :) and it is posted
heather00 (rep: 8) Dec 1, '17 at 4:22 pm
Add to Discussion


Selected Answer

Actually, you are very nearly perfectly set up already because you created lots of named ranges. You won't need all of them, as you shall see. Here is a formula you can use in 'Report Form'!B11:Z14.


A11 is the look-up value. SOIAbilities is the lookup range. Bear in mind that VLOOKUP always looks for the look-up value in the first column of the look-up range. INT(COLUMN()/6)+2 defines the column in the look-up range. So, if A11 equals "CMU", the look-up will find row 8 in SOIAbilities and then pick the second, third or fourth column.

In other words, "INT(COLUMN()/6)+2" is a rather complicated way of counting from 2 to 4. I used it so that you can use the same formula in all cells. You still can't copy right and down (only down) because of the merged cells but I figured it would be easier than writing a different formula for each column. You can copy the formula in the formula bar, Enter, select the next merged range, and past into the formula bar. However, B11=VLOOKUP($A11,SOIAbilities,2,FALSE), I11=VLOOKUP($A11,SOIAbilities,3,FALSE) and O11=VLOOKUP($A11,SOIAbilities,4,FALSE) would work just as well.

COLUMN() returns the number of the column in which the formula resides, which Excel understands to be the first cell in the merged range, ignoring all the others. Therefore COLUMN() equals 2, 9 and 15 for the 3 columns of your form. The integers resulting from a division of these numbers by 6 would be 0, 1 and 2 because the INT() function rounds down. Since we need 2, 3 and 4, in each case just 2 less than what you need, which is added in the formula: INT(COLUMN()/6)+2. You may need to know some of this when you adapt the formula for other uses in your workbook.

If the source being copied from is blank the cell with the formula will display a 0. The best way to convert that sero into a blank is by means of the cell format. Apply a Custom format like this.


"0;-0" instructs to display numbers as integers, with a minus sign before them if negative. You can change these instructions using any valid number formatting, Just remember that the first format applies to positive, the second to negative numbers. It's the third which applies to zero values. As you see it is blank, no format meaning no show, meaning a blank cell when its value is zero. The fourth element (@) specifies that text will be shown in whichever format it happens to arrive.

I saw that some of your cells were formatted as 'Text'. That won't do because then the cell will display the formula instead of evaluating it. I also found one of the values in 'SOI Abilities'!A:A to have a trailing blank space. That can't work, either, unless the blank is also present in A11. Better to have it in neither place.

Let me know when you hit difficulties I didn't prepare you for. :-)
Good luck!



Props for the automatic column iteration bit in the formula! When creating large files, that can really be a huge time-saver!
don (rep: 1521) Dec 4, '17 at 5:34 am
@Don "Props for the automatic column iteration bit ": can you say that in English, lol: and then explain why that should be so?
Variatus (rep: 2354) Dec 4, '17 at 7:42 pm
haha sorry I sometimes forget that everyone's first language isn't American English. I meant that this part -INT(COLUMN()/-6)+1 was an nice way to get the columns to automatically change when you copy the formula.
don (rep: 1521) Dec 5, '17 at 6:09 am
I entred in the formula in my spreadsheet. Now I'm sure that I didnt correctly enter them in? because some of the text in the dropdown list in A Column will not fillin the rest of the row?  Do i need to change the column formulas? 

heather00 (rep: 8) Dec 6, '17 at 2:24 pm
You might copy the format of cells which update correctly to the cells that don't, but I don't believe that is the issue. Look at the formulas. In B11 you should have exactly6 the same formula I published above. All other cells in B11:Z14 should have copies of B11. If you copied the formula above to all the cells B11:Z14 it won't work because it was designed for Row 11, and Excel will adjust that parameters automatically when you copy from B11 to another cell.
The other thing to remember is that VLOOKUP will look for the value in column A in the first column of the SOIAbilities range. If it isn't found there an error will occur.
Once you have got the formula working please remember to select the answer. Thank you.
Variatus (rep: 2354) Dec 7, '17 at 5:27 am
Hello Heather, Did you get the VLOOKUP working? If you didn't I should post the workbook in which I developed the formula. Let me know what help you need.
Variatus (rep: 2354) Dec 12, '17 at 10:02 pm
No I have  been able to get my formulas to work properly. I'm sorry I'm trying to get a grasp on your instructions. I just can't seem to get it! I have been trying to fix them and get them to function properly. 
heather00 (rep: 8) Dec 18, '17 at 3:48 pm
yes post the workbook :) PLEASE I've been trying to get this workbook of mine to work the way I want for a long time! Thank you for your help !!! :)
heather00 (rep: 8) Dec 18, '17 at 3:49 pm
A million apologies! This is entirely my fault. Sorry to have wasted so much of your time. The VLOOKUP() function has a fourth element (FALSE) which I omitted because I thought it was the default. It isn't. I modified my answer, simplifying the column calculation at the same time. Please use the new formula. I also found one trailing blank space in 'SOI Abilities'!A:A. Please check all contents in this range and remove trailing blanks.
You may like to replace your column caption "Cirricular" with "Curricular".
Variatus (rep: 2354) Dec 18, '17 at 11:03 pm
Brilliant! Thanks a million! The new formula is now working properly! :)  

Yikes i did the spell check! lol thanks had to a change a few things :)
heather00 (rep: 8) Dec 19, '17 at 2:50 pm
can you please help me again with another document :) i posted another question
heather00 (rep: 8) Apr 17, '18 at 6:45 pm
Add to Discussion

Generally, making cells appear to be empty and then "fill" is done using these functions:



The basic setup would be something like this:

=IF(A1="Some Value","Show Something","")

The second set of empty quotation marks is what makes the cell appear empty.

If you want to display a piece of data that is dependent upon the specific value from the drop-down list, then you could put a VLOOKUP() function in for the first argument of the IF() function.



Thank you. I will try this formula. 
heather00 (rep: 8) Dec 1, '17 at 1:15 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login