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.)
I am doing a financial analysis spreadsheet and need help please with a formula!
I have my total figures in column B. I have done a Data Validation list in column C and I then have my analysis columns D, E, F, G, H, and I.
My question is once i select from the data validation list. Is there a formula that will copy the total figure from column B into the correct analysis column?
Many Thanks!
Your question is, "Is there a formula that will copy the total figure from column B into the correct analysis column?"
The answer is no. No formula can write anything to any cell. Formulas can only change the value of the cell in which they reside. Therefore you need to reverse your logic, as Don has already suggested. In the attached workbook this idea is implemented. This is the formula that does all the work. Enter it in D1 and copy it to the right and down as required.
=IF($C2=D$1,$B2,0)The trick is accomplished by setting the List range for the validation dropdown to $D$1:$G$1 (I would use a named range instead of the worksheet address). Now the formula just checks if the selected validation item matches the column caption. This idea could be expanded to use substitutes, enabling validation choices to be different from column captions. That's another question.
Where validation and caption don't match a zero is inserted. I advocate this as being superior to "" (null string) which would also work. The difference is that zero is a number and "" is text. If you try to do calculations with text an error will result. However the SUM() function interprets text as zero. Hence you may not have a problem with "".
In the attached worksheet display of zeroes is suppressed. This is done in the cell format setting of range D2:G3. It's #,##0.00;-#,##0.00;;@. Quickly explained, there are 4 instructions between semicolons. Excel applies them to numbers which are positive, negative, zero or text, in that sequence. The instruction for zero is blank. Therefore zeroes are displayed as blanks.
Observe the difference in the number format in column B and D2:G3. Column B uses a built-in format, D:G have a custom format.