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

Y

Yellowvale46

New Member
Joined
Nov 9, 2017
Messages
3
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?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Is this what you are expecting to see?

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td]Proj_01[/td][td]Proj_02[/td][td]Proj_03[/td][td]Proj_04[/td][td]Proj_05[/td][td]Proj_06[/td][td]Proj_07[/td][td]Proj_08[/td][td]Proj_09[/td][td]Proj_10[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Service_1[/td][td]
$220​
[/td][td]
$187​
[/td][td]
$205​
[/td][td]
$125​
[/td][td]
$111​
[/td][td]
$129​
[/td][td]
$118​
[/td][td]
$185​
[/td][td]
$184​
[/td][td]
$174​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]Service_2[/td][td]
$118​
[/td][td]
$234​
[/td][td]
$155​
[/td][td]
$159​
[/td][td]
$110​
[/td][td]
$116​
[/td][td]
$181​
[/td][td]
$195​
[/td][td]
$194​
[/td][td]
$140​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Service_3[/td][td]
$110​
[/td][td]
$103​
[/td][td]
$103​
[/td][td]
$161​
[/td][td]
$238​
[/td][td]
$109​
[/td][td]
$206​
[/td][td]
$244​
[/td][td]
$181​
[/td][td]
$134​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Service_4[/td][td]
$170​
[/td][td]
$101​
[/td][td]
$225​
[/td][td]
$132​
[/td][td]
$227​
[/td][td]
$225​
[/td][td]
$189​
[/td][td]
$245​
[/td][td]
$118​
[/td][td]
$215​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Service_5[/td][td]
$153​
[/td][td]
$187​
[/td][td]
$203​
[/td][td]
$121​
[/td][td]
$105​
[/td][td]
$121​
[/td][td]
$108​
[/td][td]
$173​
[/td][td]
$170​
[/td][td]
$158​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Projects[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td]Proj_01[/td][td]Proj_02[/td][td]Proj_03[/td][td]Proj_04[/td][td]Proj_05[/td][td]Proj_06[/td][td]Proj_07[/td][td]Proj_08[/td][td]Proj_09[/td][td]Proj_10[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Service_1[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]Service_1 $[/td][td]
$880​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Service_2[/td][td]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Service_2 $[/td][td]
$1,180​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Service_3[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Service_3 $ [/td][td]
$110​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Service_4[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Service_4 $[/td][td]
$170​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]Service_5[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Service_5 $[/td][td]
$153​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][td]
$0​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: January[/td][/tr][/table]
 
Upvote 0
Yongle said:
Is this what you are expecting to see?

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

Excel 2016 (Windows) 32 bit
[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[TH][/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]A[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]B[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]C[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]D[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]E[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]F[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]G[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]H[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]I[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]J[/COLOR]​
[/TH]
[TH]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]K[/COLOR]​
[/TH]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]1[/COLOR]​
[/TD]
[TD][/TD]
[TD]Proj_01[/TD]
[TD]Proj_02[/TD]
[TD]Proj_03[/TD]
[TD]Proj_04[/TD]
[TD]Proj_05[/TD]
[TD]Proj_06[/TD]
[TD]Proj_07[/TD]
[TD]Proj_08[/TD]
[TD]Proj_09[/TD]
[TD]Proj_10[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]2[/COLOR]​
[/TD]
[TD]Service_1[/TD]
[TD]
4​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]3[/COLOR]​
[/TD]
[TD]Service_1 $[/TD]
[TD]
$880​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]4[/COLOR]​
[/TD]
[TD]Service_2[/TD]
[TD]
10​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]5[/COLOR]​
[/TD]
[TD]Service_2 $[/TD]
[TD]
$1,180​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]6[/COLOR]​
[/TD]
[TD]Service_3[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]7[/COLOR]​
[/TD]
[TD]Service_3 $ [/TD]
[TD]
$110​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]8[/COLOR]​
[/TD]
[TD]Service_4[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]9[/COLOR]​
[/TD]
[TD]Service_4 $[/TD]
[TD]
$170​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]10[/COLOR]​
[/TD]
[TD]Service_5[/TD]
[TD]
1​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E0E0F0]#E0E0F0[/URL] "]
[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]11[/COLOR]​
[/TD]
[TD]Service_5 $[/TD]
[TD]
$153​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[TD]
$0​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: January[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


Yes pretty close, thanks, that's given me an idea :)
 
Upvote 0
The formulas used and dragged across

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td][/td][td]Proj_01[/td][td] Formula[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]Service_1[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]Service_1 $[/td][td]
$0​
[/td][td] =B2*Projects!B2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]Service_2[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Service_2 $[/td][td]
$0​
[/td][td] =B4*Projects!B3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]Service_3[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]Service_3 $ [/td][td]
$0​
[/td][td] =B6*Projects!B4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]Service_4[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]Service_4 $[/td][td]
$0​
[/td][td] =B8*Projects!B5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]Service_5[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]Service_5 $[/td][td]
$0​
[/td][td] =B10*Projects!B6[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: January[/td][/tr][/table]

can someone remind me of the script I could use to create tabs Feb- Dec copying the formulas and format from Jan?
Click to expand...
I will post something later this morning
 
Upvote 0
(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

Code: 
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 ?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
110
Abdo
A
K
  • Question Question
Need to create/source a macro/vba/program that auto populates info based on matching word criteria for community support agency
Replies
3
Views
709
Micron
M
V
  • Question Question
Add value (number) to a dynamically referenced cell based on input in a cell in another sheet.
Replies
9
Views
278
Voxov
V
K
  • Question Question
Formula needed to look up specific name and enter data from specific column within same row
Replies
1
Views
452
awoohaw
awoohaw
A
  • Solved
If a cell contains the word "Completed", then return the date/time when it changed.
2
Replies
14
Views
739
dreid1011
dreid1011

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top