# Excel Formula

Copper Contributor

# Excel Formula

Hi,

As discussed please look at the below.
The issue I have is with the Dashboard tab.

Everything has a formula except Column C ‘Budgeted’ as this needs a monthly manual input and can change each month.

Row 3 is a drop down for the month but when I swap to a new month the ‘Budgeted’ amount does not change. So I need a formula for those amounts to change when the month changes.

Hopefully someone can help.

Thanks,
Andy
14 Replies

# Re: Excel Formula

As you said budgeted column(C) is a manual input column. Why would it update if there isn't a dynamic formula that responds to changes in the month dropdown menu?

# Re: Excel Formula

Hi,

Any ideas on what formula I would need.

Thanks,
Andy

# Re: Excel Formula

Hi Andy- What would you like to see upon choosing a value from the month dropdown menu? I'm a bit confused. Would you want to see the values in Column C?

# Re: Excel Formula

Hi,

What I want to see is that when I change the month in the drop the budgeted amount in column C changes with the month selected.

Thanks,

Andy

# Re: Excel Formula

From where should it source the data corresponding to the selected months?

# Re: Excel Formula

I guess that’s part of the question. Do I need a separate table for the budgets amount?

# Re: Excel Formula

@Andyshepasyou should keep a distinct table for your monthly budget information. Kindly refer to the provided screenshot for guidance.

# Re: Excel Formula

Yes, makes sense thanks. I guess the only thing I’m missing is how do I get the table to reference the drop down so that when you change the month it automatically selects the correct numbers. Thanks

# Re: Excel Formula

The formula I provided already accounts for this. It references the month field. However, ensure that your 'budget' data follows the format shown in the screenshot above.

# Re: Excel Formula

I can’t see the actual formula. Could you send it please.

# Re: Excel Formula

Sorry I can see the formula now.

# Re: Excel Formula

=INDEX(\$P\$8:\$Y\$10,MATCH(P16,\$P\$8:\$P\$10,0),MATCH(YourMonthDropdowncell,\$P\$7:\$Y\$7,0))

# Re: Excel Formula

Hi Santos,

Still struggling to get this to work, it keeps returning the type e.g. ‘Clare Salary’ - =INDEX(Sheet1!\$A\$4:\$C\$10,MATCH(A5,Sheet1!\$A\$4:\$A\$10,0), MATCH(\$B\$3, Sheet1!\$B\$2:\$C\$2,0))