Value or formula in a cell dependent on another cell

[muhleebbin]

Is it possible to have a value or a formula in H3:H54 that's dependent on J1? Basically trying to build a new rate sheet for the upcoming year and cells G3:G54 have the previous years rates. So depending on how a user would like to use this sheet, they could either fill in a 5% in J1 and H3:H54 would show a 5% increase from the previous year. However some users will probably just populate the individual rates or want to override the calculation with a more palatable amount.

Below is a test sample but I'd like to either be able to override the values of H (like H14) and it still calculate appropriately if J1 is filled in again.

Test.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Test									5%
2		Room	Den	Occupancy	Helper	2021	2022	2023 Rates
3	1 Bedroom	100	Y	Single	Y|Single	900	1,000	1,050		5.00%
4		101	Y	Double A	Y|Double A	800	900	945		5.00%
5		102	Y	Double B	Y|Double B	800	900	945		5.00%
6		103		Single
7		104		Double A
8		105		Double B
9		106		Single
10		107		Double A
11		108		Double B
12		109	Y	Single	Y|Single	900	1,000	1,050		5.00%
13		110	N	Double A	N|Double A	800	900	945		5.00%
14		111	N	Double B	N|Double B	800	900	975		8.33%
15		112		Single
16		113		Double A
17		114		Double B
18		115	N	Single	N|Single	900	1,000	1,050		5.00%
19		116	N	Double A	N|Double A	800	900	945		5.00%
20		117	N	Double B	N|Double B	800	900	945		5.00%
21		118		Single
22		119		Double A
23		120		Double B
24		121		Single
25		122		Double A
26		123		Double B
27		124		Single
28		125		Double A
29		126		Double B
30		127		Single
31		128		Double A
32		129		Double B
33		130		Single
34		131		Double A
35		132		Double B
36	2 Bedroom	200	Y	Single	Y|Single	1,200	1,500	1,575		5.00%
37		201	Y	Double A	Y|Double A	1,100	1,300	1,365		5.00%
38		202	Y	Double B	Y|Double B	1,100	1,300	1,365		5.00%
39		203		Single
40		204		Double A
41		205		Double B
42		206		Single
43		207		Double A
44		208		Double B
45		209		Single
46		210		Double A
47		211		Double B
48		212		Single
49		213		Double A
50		214		Double B
51	3 Bedroom	300		Single
52		301		Double A
53		302		Double B
54		303
Sheet1

Cell Formulas
Range	Formula
F2	F2	=G2-1
H2	H2	=CONCATENATE(G2+1," Rates")
H3:H5,H37:H38,H18:H20,H12:H13	H3	=G3+(G3*$J$1)
H36	H36	=G36+(G36*J1)
E3:E54	E3	=IF(OR(C3="",D3=""),"",CONCATENATE(C3,"|",D3))
J3:J54	J3	=IFERROR((H3-G3)/G3,"")

 

[OaklandJim]

So when you enter a value you want it to automatically calculate the total based on the value in J1? So when you enter 975 into H14 you want the value of 975 * (1+J1) = 1,023.75 to be put into that cell?

Is the percent value always in J1?

The only way I can think of to do this is to use Excel's Change Event. It is triggered when there is a change to the worksheet, like entering a data value (for example, entering 975 into H14). It would determine whether the value entered is in column H. If so it would multiply the value entered by the value in J1 -- value 975 * (1+$J$1) -- then put the result (1,023.75) into the cell.

 

[muhleebbin]

So when you enter a value you want it to automatically calculate the total based on the value in J1? So when you enter 975 into H14 you want the value of 975 * (1+J1) = 1,023.75 to be put into that cell?

Is the percent value always in J1?

The only way I can think of to do this is to use Excel's Change Event. It is triggered when there is a change to the worksheet, like entering a data value (for example, entering 975 into H14). It would determine whether the value entered is in column H. If so it would multiply the value entered by the value in J1 -- value 975 * (1+$J$1) -- then put the result (1,023.75) into the cell.

Not exactly. I suppose it is better explained with users. User 1 may just opt to put in a % in J1 whereas user 2 may want to put in numbers like the 975. The rest of column J will tell them the percentage change so user 2 may just not care if it's a uniform change or not. But if user 2 got a hold of it first and put in a set value for column H, if user 1 subsequently got a hold of the sheet and they put in a value for J1 then the cell that was overridden would no longer calculate.

Your suggestion of the change event though is interesting. Can I have it where if J1 is a value then fill cells J3:J54 with the appropriate formula but if it is blank then it clears out any formulas in the same cells?

Thank you very much for your question and suggestion!

 

[OaklandJim]

I'm kinda confused about what is needed. The value of 975 could be multiplied by J1 or?

if J1 is a value then fill cells J3:J54 with the appropriate formula but if it is blank then it clears out any formulas in the same cells?

Huh? So if someone clears the value in J1 the values in J3:J54 contains what? What is the "appropriate" formula.

If J1 is cleared then if user enters 975 into H14 what happens?

 

[muhleebbin]

If J1 has anything in there H3:H54 contains the formula =G3+(G3*$J$1) respectively. If J1 gets cleared then the formula's would clear out and the user can input static values.

I can also see someone putting in a value for J1 and then overriding the number because they don't think it's enough or too much. If that happens and then they put in a value for J1, I want the H cell that was overridden to again calculate =G3+(G3*$J$1) respectively to its row.

 

[muhleebbin]

I'm kinda confused about what is needed. The value of 975 could be multiplied by J1 or?



Huh? So if someone clears the value in J1 the values in J3:J54 contains what? What is the "appropriate" formula.

If J1 is cleared then if user enters 975 into H14 what happens?

"if J1 is a value then fill cells J3:J54 with the appropriate formula" this line should have read 'if J1 is a value then fill cells H3:H54 with the appropriate formula'

J3:J54 is calculating the percentage change for the user

Sorry for the confusion

 

[OaklandJim]

OK, what is the formula in J14 if J1 is empty? What is the formula in J14 if J1 is empty.

 

[muhleebbin]

J column is just a percentage change that calculates the increase or decrease in rate from the previous year. =IFERROR((H3-G3)/G3,"") in this case =IFERROR((H14-G14)/G14,"")

 

[muhleebbin]

H column is the one that really has the variable, whether it's a static value or a calculated value and that's dependent on if J1 has a value or not.

 

[OaklandJim]

OK so what is the appropriate formula? Do you mean like in cell H3 the formula is G3+(G3*$J$1). Otherwise H3:H54 are empty or what?