200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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
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.
=VLOOKUP($A11,SOIAbilities,INT(COLUMN()/6)+2,FALSE)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;;@"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!
Generally, making cells appear to be empty and then "fill" is done using these functions:
IF()
VLOOKUP()
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.