What am I looking for?

[jndickin]

I cannot figure out how to express what I'm looking for in a manner which will elicit the desired response from Google.

I turn to you to tell me what I'm looking for after this brief (fingers crossed) description.

I have a budget which starts out looking like this:

JAN	ELECTRIC	$30.00​
JAN	GAS	$44.00​
JAN	RENT	$994.00​
JAN	GROCERIES	$100.00​
JAN	GARBAGE	$10.00​
JAN	MOBILE	$60.00​
JAN	CABLE	$80.00​
FEB	ELECTRIC	$30.00​
FEB	GAS	$44.00​
FEB	RENT	$994.00​
FEB	GROCERIES	$100.00​
FEB	GARBAGE	$10.00​
FEB	MOBILE	$60.00​
FEB	CABLE	$80.00​
MCH	ELECTRIC	$30.00​
MCH	GAS	$44.00​
MCH	RENT	$994.00​
MCH	GROCERIES	$100.00​
MCH	GARBAGE	$10.00​
MCH	MOBILE	$60.00​
MCH	CABLE	$80.00​

As time moves on, I replace these budget numbers with actual amounts. What kind of formula do I need to automatically replace all going-forward budget numbers with the last actual amount I've entered above?

Have I been able to communicate this clearly enough for an answer?

Thanks for your help!

 

[Ezguy4u]

Jn Let's get the ball rolling. I find this confusing. Then again, I find most things confusing. So you replace the budget numbers with actual numbers. Now your problem is you want to replace budget numbers with actual numbers automatically with a formula. I am going to suggest you download XL2BB app so you can show us what your input looks like and what you expect your output will look like.

 

[jndickin]

Jn Let's get the ball rolling. I find this confusing. Then again, I find most things confusing. So you replace the budget numbers with actual numbers. Now your problem is you want to replace budget numbers with actual numbers automatically with a formula. I am going to suggest you download XL2BB app so you can show us what your input looks like and what you expect your output will look like.

Shoot! How do I get past "This file is not supported in Protected View?"

 

[jndickin]

Shoot! How do I get past "This file is not supported in Protected View?"

Nevermind. Got it. Minisheet coming.

 

[jndickin]

I kinda feel like this might make it worse, but here goes.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ORIGINAL BUDGET					DESIRED RESULT AFTER JANUARY					DESIRED RESULT AFTER JANUARY
2
3	Month	Payee	Amount			Month	Payee	Amount			Month	Payee	Amount
4	JAN	ELECTRIC	$30.00			JAN	ELECTRIC	$33.00	As actuals are input in January . . .		JAN	ELECTRIC	$33.00
5	JAN	GAS	$44.00			JAN	GAS	$44.00			JAN	GAS	$44.00
6	JAN	RENT	$994.00			JAN	RENT	$994.00			JAN	RENT	$994.00
7	JAN	GROCERIES	$100.00			JAN	GROCERIES	$112.00			JAN	GROCERIES	$100.00
8	JAN	GARBAGE	$10.00			JAN	GARBAGE	$20.00			JAN	GARBAGE	$20.00
9	JAN	MOBILE	$60.00			JAN	MOBILE	$60.00			JAN	MOBILE	$60.00
10	JAN	CABLE	$80.00			JAN	CABLE	$80.00			JAN	CABLE	$80.00
11
12	FEB	ELECTRIC	$30.00	These s/b formulas that pick up the most recent entry above for the corresponding Payee		FEB	ELECTRIC	$33.00	. . . any changes are reflected in the months following because the formula is picking up the most recent entry above corresponding to the Payee		FEB	ELECTRIC	$33.00	And--as actuals are input in the next month . . .
13	FEB	GAS	$44.00			FEB	GAS	$44.00			FEB	GAS	$44.00
14	FEB	RENT	$994.00			FEB	RENT	$994.00			FEB	RENT	$1,000.00
15	FEB	GROCERIES	$100.00			FEB	GROCERIES	$112.00			FEB	GROCERIES	$80.00
16	FEB	GARBAGE	$10.00			FEB	GARBAGE	$20.00			FEB	GARBAGE	$10.00
17	FEB	MOBILE	$60.00			FEB	MOBILE	$60.00			FEB	MOBILE	$60.00
18	FEB	CABLE	$80.00			FEB	CABLE	$80.00			FEB	CABLE	$80.00
19
20	MCH	ELECTRIC	$30.00			MCH	ELECTRIC	$33.00			MCH	ELECTRIC	$33.00	. . . any changes are reflected in the months following because the formula is picking up the most recent entry above corresponding to the Payee
21	MCH	GAS	$44.00			MCH	GAS	$44.00			MCH	GAS	$44.00
22	MCH	RENT	$994.00			MCH	RENT	$994.00			MCH	RENT	$1,000.00
23	MCH	GROCERIES	$100.00			MCH	GROCERIES	$112.00			MCH	GROCERIES	$80.00
24	MCH	GARBAGE	$10.00			MCH	GARBAGE	$20.00			MCH	GARBAGE	$10.00
25	MCH	MOBILE	$60.00			MCH	MOBILE	$60.00			MCH	MOBILE	$60.00
26	MCH	CABLE	$80.00			MCH	CABLE	$80.00			MCH	CABLE	$80.00
Sheet1

 

[Peter_SSs]

See if this does what you want. I have assumed the JAN figures are actual. As the FEB 'Actual' figures are entered to replace the existing formulas for FEB, the MCH ( & further months) figures will automatically update to the most recent values.

23 08 13.xlsm
	A	B	C
3	Month	Payee	Amount
4	JAN	ELECTRIC	30
5	JAN	GAS	44
6	JAN	RENT	994
7	JAN	GROCERIES	100
8	JAN	GARBAGE	10
9	JAN	MOBILE	60
10	JAN	CABLE	80
11
12	FEB	ELECTRIC	30
13	FEB	GAS	44
14	FEB	RENT	994
15	FEB	GROCERIES	100
16	FEB	GARBAGE	10
17	FEB	MOBILE	60
18	FEB	CABLE	80
19
20	MCH	ELECTRIC	30
21	MCH	GAS	44
22	MCH	RENT	994
23	MCH	GROCERIES	100
24	MCH	GARBAGE	10
25	MCH	MOBILE	60
26	MCH	CABLE	80
Budget v Actual

Cell Formulas
Range	Formula
C12:C26	C12	=IF(B12="","",XLOOKUP(B12,B$4:B11,C$4:C11,,,-1))

 

[jndickin]

Well, I have certainly learned A LOT tonight! I have learned that I actually have Microsoft Office Professional Plus 2016, not 365--I changed my profile. I've learned that XLOOKUP is not available in 2016. I've learned that it will cost me some buckeroos to upgrade which I'm not inclined to do at this point in time--maybe later this month.

So . . . got anything that doesn't use the "oh so convenient" XLOOKUP? Maybe like an INDEX something?

Sorry about the misleading info. (Big sigh!)

 

[Peter_SSs]

I actually have Microsoft Office Professional Plus 2016, not 365--I changed my profile.

OK, thanks for correcting.

So . . . got anything that doesn't use the "oh so convenient" XLOOKUP? Maybe like and INDEX something?

Sure ..

23 08 13.xlsm
	A	B	C
3	Month	Payee	Amount
4	JAN	ELECTRIC	30
5	JAN	GAS	44
6	JAN	RENT	994
7	JAN	GROCERIES	100
8	JAN	GARBAGE	10
9	JAN	MOBILE	60
10	JAN	CABLE	80
11
12	FEB	ELECTRIC	30
13	FEB	GAS	44
14	FEB	RENT	994
15	FEB	GROCERIES	100
16	FEB	GARBAGE	10
17	FEB	MOBILE	60
18	FEB	CABLE	80
19
20	MCH	ELECTRIC	30
21	MCH	GAS	44
22	MCH	RENT	994
23	MCH	GROCERIES	100
24	MCH	GARBAGE	10
25	MCH	MOBILE	60
26	MCH	CABLE	80
Budget v Actual (2)

Cell Formulas
Range	Formula
C12:C26	C12	=IF(B12="","",INDEX(C:C,AGGREGATE(14,6,ROW(C$4:C11)/(B$4:B11=B12),1)))