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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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
144
Abdo
A
A
  • Question Question
Auto populate columns in one tab based on entries in another
Replies
6
Views
103
dermie_72
D
datatronics505
  • Solved
Absolute reference to a cell of another table
Replies
5
Views
160
datatronics505
datatronics505
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
730
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
282
Voxov
V

Forum statistics

Threads
1,223,903
Messages
6,175,279
Members
452,630
Latest member
OdubiYouth

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