Updating a column's formula when new data is entered into a column.

[learnerex]

Hello-

I have a sheet with sales data that I update on a monthly basis. Columns B-M are Jan through Dec. Column N is a YTD Total.

Row 3 lists my month titles (Jan - YTD)
Row 4 gives Sales Data for product A.

Row 8 gives Sales Data for product A's last year's performance.

I would like column O to give a % to last year's sales for the month of data I fill in. If I were filling in my form after this month, I would like this column to read % to last year for May and next month for June, etc.

I am wondering if there is a formula I can put into O4 that would know if there is data in F4(for May Sales), it will calculate (f4/f8)-1 and if there is data in G4 (for June sales) it will calculate (g4/g8)-1, and so on through the year.

I would like to avoid having to change the formulas in column O monthly as there will be multiple people using this sheet, many of whom are excel beginners. Having a "trigger cell" for the formula would work ok (for example, if the user would have to type "May" into cell O2 to trigger the formula to change in column O) would be ok.

Hope my question makes sense.

Thank you!

 

[ChrisR]

Hi,

Look at using the =CHOOSE function

That way your index number would just need to return the last non blank month, then it 1-12 of the formula you just need each months formula.


So, the format would be :-

=CHOOSE(Last non blank month,FOrmula for Jan,Formula For Feb, Formula For March.............) etc for all 12 months........

Regards,
Chris

 

[learnerex]

Thank you for your help, but I can't make your suggestion work. I'm just getting back to this file now... have been avoiding it. I think my question has changed slightly as well. I've pasted my file information below. My columns are A-O and the lines are 1-10. In cell O4, I want to calculate the 2013 YTD value for each month that has data in line 4. For example, as shown below, because I have 2014 data for Dept A until July, I would like O4 to be sum(B9:H9). But, I would also like this formula to know that when I add data into I4 for August 2014, it should automatically calculate the 2013 data for January through August, effectively sum(B9:I9), and so on for all months of the year. Is this possible? Please let me know if you need further information. Many thanks.

2014</SPAN>

Volume</SPAN>

JAN</SPAN>

FEB</SPAN>

MAR</SPAN>

APR</SPAN>

MAY</SPAN>

JUNE</SPAN>

JULY</SPAN>

AUG</SPAN>

SEPT</SPAN>

OCT</SPAN>

NOV</SPAN>

DEC</SPAN>

YTD TY</SPAN>

YTD LY</SPAN>

A</SPAN>

$2,345</SPAN>

$1</SPAN>

$1</SPAN>

$1</SPAN>

$1</SPAN>

$1</SPAN>

$1</SPAN>

$2,351

#NAME?</SPAN>

B</SPAN>

$47,705</SPAN>

$13,225</SPAN>

$9,955</SPAN>

$5,890</SPAN>

$13,060</SPAN>

$15,950</SPAN>

$16,755</SPAN>

$122,540

FALSE</SPAN>

2013</SPAN>

Volume</SPAN>

JAN</SPAN>

FEB</SPAN>

MAR</SPAN>

APR</SPAN>

MAY</SPAN>

JUNE</SPAN>

JULY</SPAN>

AUG</SPAN>

SEPT</SPAN>

OCT</SPAN>

NOV</SPAN>

DEC</SPAN>

YTD</SPAN>

Budget</SPAN>

A</SPAN>

$2,345</SPAN>

$5,820</SPAN>

$4,680</SPAN>

$34,900</SPAN>

$21,810</SPAN>

$25,750</SPAN>

$13,715</SPAN>

$6,705</SPAN>

$4,485</SPAN>

$695</SPAN>

$14,365</SPAN>

$22,215</SPAN>

$157,485

$0</SPAN>

B</SPAN>

$47,705</SPAN>

$13,225</SPAN>

$9,955</SPAN>

$5,890</SPAN>

$13,060</SPAN>

$15,950</SPAN>

$16,755</SPAN>

$25,335</SPAN>

$11,250</SPAN>

$23,145</SPAN>

$12,968</SPAN>

$1,300</SPAN>

$196,538

$0</SPAN>

<TBODY>
</TBODY>