- Joined
- Jun 10, 2011
- Messages
- 3
- Reaction score
- 0
In the spreadsheet I'm trying to make I did the following:
-Made three tabs – one for the previous quarter(initial), one for the current quarter(current), and the final one for comparison
-Used the indirect function to pull values in the first two tabs to the workbook from the external files stored in my work drives.
-In the third sheet or the comparison sheet, I set up an IF function which will calculate a percentage change between the first two sheets if the numbers are different (otherwise it will be left blank)
formula: IF(Initial!C20<>Current!C20,(((Current!C20)-(Initial!C20))/Initial!C20),0)
-From there, I set up conditional formatting which allows me to see unusual changes between the quarters.
My problem:
Sometimes different external files I pull from have a different number of columns thus throwing off my comparison file formula. Is there a way that a formula can match the columns headers(and below) that are alike in the first two tabs and return it to the third tab and then still use the if statement above to calculate the percentage change? Any help is much appreciated. I need a formula, not a macro if possible. Thanks!
-Made three tabs – one for the previous quarter(initial), one for the current quarter(current), and the final one for comparison
-Used the indirect function to pull values in the first two tabs to the workbook from the external files stored in my work drives.
-In the third sheet or the comparison sheet, I set up an IF function which will calculate a percentage change between the first two sheets if the numbers are different (otherwise it will be left blank)
formula: IF(Initial!C20<>Current!C20,(((Current!C20)-(Initial!C20))/Initial!C20),0)
-From there, I set up conditional formatting which allows me to see unusual changes between the quarters.
My problem:
Sometimes different external files I pull from have a different number of columns thus throwing off my comparison file formula. Is there a way that a formula can match the columns headers(and below) that are alike in the first two tabs and return it to the third tab and then still use the if statement above to calculate the percentage change? Any help is much appreciated. I need a formula, not a macro if possible. Thanks!