# Need a Formual that will populate different values in multiple cells

#### Collins1805

##### New Member
Excel 2010

I am trying to create a goal sheet for my sales people. I have an area that is populating 52 cells using VLOOKUP from another sheet. It is pulling their sales from each week last year. I then added at the end of each VLOOKUP formula *1.45 if I wanted a 45% increase etc.

So now that I have my goals I copied those values to another "goal table" (on the same sheet) to avoid dealing with all of the VLOOKUP formuals and I could just hide those.

Then I made another table for the sales people to enter their 2014 weekly numbers (on the same sheet).

So here is my question: I am trying to figure out a formual that adjust the goals based on their 2014 input that keeps them on pace. Example: If Week 1 of 2014 my goal is 10K and I did 61K I want a formual that will take the difference (51K) divide it by the remaining weeks (51) which in this example is 1,000 and distribute it to all of the remaining weeks cells so each week is 1,000 less because I over shot my goal week 1. And I also want it to do the opposite and add \$\$\$ if I under shot my goal to keep me on pace.

So this is what I came up with that kind of works: =COUNTIF(B42,"<>B4")*(((B4-B42)/51)+B5)

My 1st weeks goal is B4 (\$449.47)
My 2014 first weeks input is B42 (week 52 green)
B5 = Week 2's goal that i want adjusted to keep pace. (\$5229.76)

Below is a snapshot of what I am talking about.

 2421 Period 1 \$10,132.86 Week 52 \$449.47 Week 1 \$5,229.76 Week 2 \$4,407.25 Week 3 \$46.39 Period 6 \$147,210.99 Week 20 \$52,318.83 Week 21 \$56,533.69 Week 22 \$21,108.14 Week 23 \$17,250.33 Period 11 \$15,002.89 Week 40 \$2,986.19 Week 41 \$11,454.19 Week 42 \$0.00 Week 43 \$562.51 Period 1 \$10,132.86 Week 52 \$449.47 Week 1 \$5,238.57 Week 2 \$4,416.06 Week 3 \$55.20 Period 6 \$147,210.99 Week 20 Week 21 Week 22 Week 23 Period 11 \$15,002.89 Week 40 Week 41 Week 42 Week 43 Period 1 Goal \$10,132.86 Remaining \$10,132.86 Week 52 \$0.00 Week 1 \$0.00 Week 2 \$0.00 Week 3 \$0.00 Period 6 Goal \$147,210.99 Remaining \$147,210.99 Week 20 Week 21 Week 22 Week 23 Period 11 Goal \$15,002.89 Remaining \$15,002.89 Week 40 Week 41 Week 42 Week 43

<tbody>
</tbody>

The reason why my formual only kind of works is because when I input a value in B42 (Week 52 green) that does everything I want it to. But if I leave B42 "0.00" it adjust the goal from its original value (\$5,238.57 (orange) instead of \$5,229.76 (yellow))

the bigger problem I am having is figuring out how to adjust multiple cells to keep my pace. So going back to my orginal example I want each cell to go down \$1000 if that is the avg that I over shot my goal for the next 51 weeks. And then I need to duplicate that for the next cell to adjust the next 50 weeks and then the next cell to adjust the next 49 weeks and so on.

I appreciate any insite I can get on this. I know this was extremely long but I wanted to give as much detail as possible. I ahve been working on this for the past 3 days and can't figure it out.

any suggestions?

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Replies
0
Views
139
Replies
0
Views
108
Replies
3
Views
211
Replies
1
Views
96
Replies
1
Views
395

1,203,507
Messages
6,055,809
Members
444,826
Latest member
aggerdanny

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back