Trying to get this to work

[Highworld]

Hello In no shape of form am I am expert within Excel but trying to get this to work.

But i am trying to get it so that when clicking on D16 (should be showing an Options Button) it pulls data from H19 and puts that information in C8. For some reason I am unable to get this to function correctly.

Income Calculation Worksheet Master w updated 2023 v1.xlsm
	B	C	D	E	F	G	H
4	Owner Occupied PITI	1,000.00		Loan Size	O/O & 2nd Home	NOO
5	Second Home Outstanding Lien Balance(s)
6	Second Home PITI			<$726,200	2	6
7	Bridge Loan P&I			>$726,201 to $1,000,000	6	N/A
8	Reserve Requirement for O/O (months)	0		>$1,000,000	12	N/A
9	Reserve Requirement for 2nd Home (months)	2
10	Reserve Requirement for O/O, 2nd Home, & Bridge($)	$0		FALSE
11
12	Subject NOO Outstanding Lien Balance	$0.00		TRUE
13	Subject NOO Total PITI	$0.00
14	Non-Subject NOO Outstanding Lien Balance(s)	$0.00		FALSE
15	Non-Subject NOO Total PITI	$0.00
16	Number of financed 1-4 unit residential properties			FALSE
17	Reserve Requirement for Subject NOO ($)	$0
18	Reserve Requirement for Non-Subject NOO ($)	$0		Property Type	Max LTV	Max Loan AMT	Reserve
19	Total Assets Required by DU
20	Additional Required Reserves
Reserves & DTI

Cell Formulas
Range	Formula
C8	C8	=IF($K$1<>1,0,IF(AND($E$14=TRUE,$K$1=1,$C$3>726201,$C$3<=1000000),$F$7,IF(AND($E$14=TRUE,$K$1=1,$C$3>1000000),$F$8,IF($C$16<=4,VLOOKUP(1,ReserveReq,3),IF($C$16>=5,VLOOKUP(1,ReserveReq,4),0)))))
C9	C9	=IF($K$1<>2,0,IF(AND($E$14=TRUE,$K$1=2,$C$5>650000,$C$5<=1000000),$F$7,IF(AND($E$14=TRUE,$K$1=2,$C$5>1000000),$F$8,IF($C$16<=4,VLOOKUP(2,ReserveReq,3),IF($C$16>=5,VLOOKUP(2,ReserveReq,4),0)))))
C10	C10	=(C4*C8)+(C6*C9)+(C7*6)
C13	C13	=IF('Net Rent'!$B$2=TRUE,'Net Rent'!B19,0)
C15	C15	=IF('Net Rent'!B2=TRUE,SUM('Net Rent'!F19:G19,'Net Rent'!F38:G38,'Net Rent'!B38:C38,'Net Rent'!B57:C57,'Net Rent'!F57:G57,'Net Rent'!F76:G76,'Net Rent'!B76:C76,'Net Rent 2'!B19:C19,'Net Rent 2'!F19:G19,'Net Rent 2'!F38:G38,'Net Rent 2'!B38:C38,'Net Rent 2'!B57:C57,'Net Rent 2'!F57:G57,'Net Rent 2'!F76:G76,'Net Rent 2'!B76:C76,'Net Rent 3'!B19:C19,'Net Rent 3'!F19:G19,'Net Rent 3'!F38:G38,'Net Rent 3'!B38:C38,'Net Rent 3'!B57:C57,'Net Rent 3'!F57:G57,'Net Rent 3'!F76:G76,'Net Rent 3'!B76:C76,),SUM('Net Rent'!B19:C19,'Net Rent'!F19:G19,'Net Rent'!F38:G38,'Net Rent'!B38:C38,'Net Rent'!B57:C57,'Net Rent'!F57:G57,'Net Rent'!F76:G76,'Net Rent'!B76:C76,'Net Rent 2'!B19:C19,'Net Rent 2'!F19:G19,'Net Rent 2'!F38:G38,'Net Rent 2'!B38:C38,'Net Rent 2'!B57:C57,'Net Rent 2'!F57:G57,'Net Rent 2'!F76:G76,'Net Rent 2'!B76:C76,'Net Rent 3'!B19:C19,'Net Rent 3'!F19:G19,'Net Rent 3'!F38:G38,'Net Rent 3'!B38:C38,'Net Rent 3'!B57:C57,'Net Rent 3'!F57:G57,'Net Rent 3'!F76:G76,'Net Rent 3'!B76:C76,))
C17	C17	=(($C$13)*(VLOOKUP(3,ReserveReq,3)))
C18	C18	=IF(D5=TRUE,0)*($C$15*IF($C$16<=4,VLOOKUP(1,ReserveReq,3),IF($C$16>=5,(VLOOKUP(5,ReserveReq,4)))))

Named Ranges
Name	Refers To	Cells
ReserveReq	='Reserves & DTI'!$K$3:$N$7	C8:C9, C17:C18

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E18:H19	Expression	=$E$16="false"	text	NO
E17:H20	Expression	=E16="False"	text	NO
B19:C19	Expression	=IF($E$10=FALSE,TRUE,FALSE)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E19	List	=$L$26:$N$26
F19	List	=$T$11#
G19	List	=$U$11#
H19	List	=$V$11#

 

[awoohaw]

The formula in D16 is not showing. Can you post that please? Or is it a VBA code that runs when you use that cell?, If so, can you post that (use the vba code wrapper in the text tools to make it more readable, please.)

 

[Highworld]

The formula in D16 is not showing. Can you post that please? Or is it a VBA code that runs when you use that cell?, If so, can you post that (use the vba code wrapper in the text tools to make it more readable, please.)

There is no Formula in d16 yet. That is the issue I am having. I put an options button there and trying to get it so when that is clicked. It will pull data from H19 and place it into c8. But overriding the data that is already showing in C8 if there is any.

 

[awoohaw]

There is no Formula in d16 yet. That is the issue I am having. I put an options button there and trying to get it so when that is clicked. It will pull data from H19 and place it into c8. But overriding the data that is already showing in C8 if there is any.

Do you want to delete the formula that is in C8?
If not, what triggers the decision to copy the data from H19 to C8.

 

[Highworld]

Do you want to delete the formula that is in C8?
If not, what triggers the decision to copy the data from H19 to C8.

It should not be deleting the formula in C8 but replacing it with H19 if there is a value in h19. I honestly dont know if i am explaining this correctly. Its just that the Information that will be in H19 is not often used so it would not always come into play.

 

[awoohaw]

So, do you want C8 to be what ever that calculation is unless H19 has a value?

 

[Highworld]

So, do you want C8 to be what ever that calculation is unless H19 has a value?

I was thinking to put a check box or options button in C8 that would activate that formula to transfer data from K19 to C8. This could be wrong in my thinking.

 

[awoohaw]

So, wrap an IF(H19="",....,H19) , around the formula in C8). The pink cells have references to other worksheets or workbooks, so i deleted them.
I just want you to be focused on the formula in C8.

Mr Excel Questions2.xlsm
	A	B	C	D	E	F	G	H
8		Reserve Requirement for O/O (months)	The Calculation in H19.		>$1,000,000	12	N/A
9		Reserve Requirement for 2nd Home (months)	0
10		Reserve Requirement for O/O, 2nd Home, & Bridge($)			FALSE
11
12		Subject NOO Outstanding Lien Balance	0		TRUE
13		Subject NOO Total PITI
14		Non-Subject NOO Outstanding Lien Balance(s)	0		FALSE
15		Non-Subject NOO Total PITI
16		Number of financed 1-4 unit residential properties			FALSE
17		Reserve Requirement for Subject NOO ($)
18		Reserve Requirement for Non-Subject NOO ($)			Property Type	Max LTV	Max Loan AMT	Reserve
19		Total Assets Required by DU						The Calculation in H19.
20		Additional Required Reserves
Sheet19

Cell Formulas
Range	Formula
C8	C8	= IF(H19="", IF($K$1<>1,0,IF(AND($E$14=TRUE,$K$1=1,$C$3>726201,$C$3<=1000000),$F$7,IF(AND($E$14=TRUE,$K$1=1,$C$3>1000000),$F$8,IF($C$16<=4,VLOOKUP(1,ReserveReq,3),IF($C$16>=5,VLOOKUP(1,ReserveReq,4),0))))),H19)
C9	C9	=IF($K$1<>2,0,IF(AND($E$14=TRUE,$K$1=2,$C$5>650000,$C$5<=1000000),$F$7,IF(AND($E$14=TRUE,$K$1=2,$C$5>1000000),$F$8,IF($C$16<=4,VLOOKUP(2,ReserveReq,3),IF($C$16>=5,VLOOKUP(2,ReserveReq,4),0)))))

 

[awoohaw]

In Cell C8:

= IF(H19="", IF($K$1<>1,0,IF(AND($E$14=TRUE,$K$1=1,$C$3>726201,$C$3<=1000000),$F$7,IF(AND($E$14=TRUE,$K$1=1,$C$3>1000000),$F$8,IF($C$16<=4,VLOOKUP(1,ReserveReq,3),IF($C$16>=5,VLOOKUP(1,ReserveReq,4),0))))),H19)

 

[Highworld]

= IF(H19="", IF($K$1<>1,0,IF(AND($E$14=TRUE,$K$1=1,$C$3>726201,$C$3<=1000000),$F$7,IF(AND($E$14=TRUE,$K$1=1,$C$3>1000000),$F$8,IF($C$16<=4,VLOOKUP(1,ReserveReq,3),IF($C$16>=5,VLOOKUP(1,ReserveReq,4),0))))),H19)

Thank you that worked.