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,"")

 

[muhleebbin]

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?

Yup. If J1 has a value then H3 should have that formula, otherwise it would be blank.

 

[tonydev314]

The way to do this is to put the formula elsewhere e.g. AA3:AAxx.
In the AA cells you set the rate dependent on the value in J1, or the individual rates if entered.
The H cells use the AA values NOT the J values.

 

[muhleebbin]

The way to do this is to put the formula elsewhere e.g. AA3:AAxx.
In the AA cells you set the rate dependent on the value in J1, or the individual rates if entered.
The H cells use the AA values NOT the J values

Taking OaklandJim's suggestion of creating a worksheet_change vba and sort of have it working:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim strFormulas(1 To 1) As Variant

If Not Intersect(Target, Range("J1")) Is Nothing Then

    With ThisWorkbook.Sheets("Bellingham Forecast")
        .Range("H3:H54").ClearContents
    
        strFormulas(1) = "=IFERROR(G3+(G3*$J$1),"""")"
        
        .Range("H3").Formula = strFormulas
        .Range("H3").Copy
        .Range("H3:H54").PasteSpecial Paste:=xlPasteFormulas
        .Range("H3").Select
    End With

End If

Application.ScreenUpdating = True

End Sub

A couple of issues with it are: I'd like to clear all the contents in H3:H54 if J1 is blank or deleted. Also the copy ring on cell H3 continues to show if J1 does have a value. Is there a way to get rid of the ring on H3?

 

[muhleebbin]

Figured out the clearing out of the cells if J1 is blank but cannot for the life of me figure out the stupid copy ring around H3 if J1 does have a value. Also can this code be cleaner???

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

Dim strFormulas(1 To 1) As Variant
Dim ws As Worksheet

Set ws = Worksheets("Bellingham Forecast")

If Not Intersect(Target, Range("J1")) Is Nothing Then
    
    If ws.Range("J1") <> "" Then
    
    With ThisWorkbook.Sheets("Bellingham Forecast")
        .Range("H3:H54").ClearContents
    
        strFormulas(1) = "=IFERROR(G3+(G3*$J$1),"""")"
        
        .Range("H3").Formula = strFormulas
        .Range("H3").Copy
        .Range("H3:H54").PasteSpecial Paste:=xlPasteFormulas
        .Range("H3").Select
    End With
    
    Else
        ws.Range("H3:H54").ClearContents
    End If

End If

Application.ScreenUpdating = True

End Sub

 

[OaklandJim]

Application.CutCopyMode = False

 

[muhleebbin]

Application.CutCopyMode = False

Thank you!!!!