Enter a Value into a cell, Based on Value times that by $$ Amount to give answer

[Yellowvale46]

Hi team,

I'm finding what I want to do hard to articulate, however I have 10 Columns with titles of Projects, I then have 5 Rows with Services. Each service has a Dollar amount Per Service eg $220, $118 etc.

I want to enter in each Column a service used each month, and rather than show the Amount of Actual Services, I want it to show me the Cost for the services.

Eg in Column One, we can call in Project A, In Row 1 I want to enter 4 and it show the result of 4*$220, in Row 2, if I enter 10 it will show the result of 10*$118 etc etc.

This would be in Tab January, So can someone remind me of the script I could use to create tabs Feb- Dec copying the formulas and format from Jan?

 

[Yongle]

Is this what you are expecting to see?

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​		Proj_01	Proj_02	Proj_03	Proj_04	Proj_05	Proj_06	Proj_07	Proj_08	Proj_09	Proj_10
2​	Service_1	$220​	$187​	$205​	$125​	$111​	$129​	$118​	$185​	$184​	$174​
3​	Service_2	$118​	$234​	$155​	$159​	$110​	$116​	$181​	$195​	$194​	$140​
4​	Service_3	$110​	$103​	$103​	$161​	$238​	$109​	$206​	$244​	$181​	$134​
5​	Service_4	$170​	$101​	$225​	$132​	$227​	$225​	$189​	$245​	$118​	$215​
6​	Service_5	$153​	$187​	$203​	$121​	$105​	$121​	$108​	$173​	$170​	$158​

Sheet: Projects

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​
1​		Proj_01	Proj_02	Proj_03	Proj_04	Proj_05	Proj_06	Proj_07	Proj_08	Proj_09	Proj_10
2​	Service_1	4​
3​	Service_1 $	$880​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​
4​	Service_2	10​
5​	Service_2 $	$1,180​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​
6​	Service_3	1​
7​	Service_3 $	$110​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​
8​	Service_4	1​
9​	Service_4 $	$170​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​
10​	Service_5	1​
11​	Service_5 $	$153​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​

Sheet: January

 

[Yellowvale46]

Is this what you are expecting to see?

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​		Proj_01	Proj_02	Proj_03	Proj_04	Proj_05	Proj_06	Proj_07	Proj_08	Proj_09	Proj_10
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Service_1	$220​	$187​	$205​	$125​	$111​	$129​	$118​	$185​	$184​	$174​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Service_2	$118​	$234​	$155​	$159​	$110​	$116​	$181​	$195​	$194​	$140​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Service_3	$110​	$103​	$103​	$161​	$238​	$109​	$206​	$244​	$181​	$134​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Service_4	$170​	$101​	$225​	$132​	$227​	$225​	$189​	$245​	$118​	$215​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Service_5	$153​	$187​	$203​	$121​	$105​	$121​	$108​	$173​	$170​	$158​

<tbody>
</tbody>

Sheet: Projects

<tbody>
</tbody>

Excel 2016 (Windows) 32 bit

	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​		Proj_01	Proj_02	Proj_03	Proj_04	Proj_05	Proj_06	Proj_07	Proj_08	Proj_09	Proj_10
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​	Service_1	4​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​	Service_1 $	$880​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​	Service_2	10​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​	Service_2 $	$1,180​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​	Service_3	1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​	Service_3 $	$110​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​	Service_4	1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​	Service_4 $	$170​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​	Service_5	1​
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​	Service_5 $	$153​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​	$0​

<tbody>
</tbody>

Sheet: January

<tbody>
</tbody>

Yes pretty close, thanks, that's given me an idea

 

[Yongle]

The formulas used and dragged across

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​		Proj_01	Formula
2​	Service_1
3​	Service_1 $	$0​	=B2*Projects!B2
4​	Service_2
5​	Service_2 $	$0​	=B4*Projects!B3
6​	Service_3
7​	Service_3 $	$0​	=B6*Projects!B4
8​	Service_4
9​	Service_4 $	$0​	=B8*Projects!B5
10​	Service_5
11​	Service_5 $	$0​	=B10*Projects!B6

Sheet: January

can someone remind me of the script I could use to create tabs Feb- Dec copying the formulas and format from Jan?

I will post something later this morning

 

[Yongle]

(Without seeing the whole of your January sheet) I cannot tell if deleting non-formulas in a single range can work for you
Are there any other constant values to be retained?
- then clear individual areas separately

The code below clears anything that is not a formula in the specified range
-headers in Row 1 and Column A are excluded

Sub FormulaSheet()
    Sheets("January").Copy after:=Sheets(Sheets.Count)
    With Sheets(Sheets.Count)
        .Name = "FormulasOnly"
        On Error Resume Next
        .Range("[COLOR=#ff0000]B2:Z999"[/COLOR]).SpecialCells(xlCellTypeConstants).ClearContents
    End With
End Sub

Do you want to create Feb - Dec
- one sheet at a time ?
- the whole year at once ?