Get The Values to Stick and Stay

**fffleague15** · 11-28-2015, 06:45 AM

Hi guys,

I really need your help! I am making a new system for my Window Cleaning business and I have attached a list of customers that I am cleaning windows for. I am doing this every other week, which is why the calendar at the top of the page looks how it does.

However, on the right hand side, I have a list of the weeks (26 in total) with a "Houses Cleaned" column and a "Total" column. I would like the two figures at the bottom of the page to go into the correct cells.

If that didn't make sense, say if I was cleaning windows on the 04/01/2016 and I cleaned 40 houses and made £152.00. I would like these two figures to go into that row on the right hand side. I have actually figured out how to do that but when I change the date, the figures change and it just goes on to the date which is selected.

Therefore, if possible, I would like the figures for 04/01/2016 to stay in their specific cells even if another date is selected. Therefore, come the end of the year, I would like the table on the right to be filled with figures automatically. If this is possible, I would really appreciate it if someone could help me.

I will attach the file below.

Many thanks, FFFLeague15

Trial.xlsm

**sktneer** · 11-28-2015, 07:01 AM

Delete the formula from column R and place the following code on Round One Sheet Module.
The code will place the amount in N77 into the correct cell in column R as per the date selected in cell K8.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim r As Long
If Target.Column = 5 Then
If UCase(Target.Value) = "FULL-TIME" Then
Target.Offset(0, 1).Value = 2080
Target.Offset(0, 2).Value = 260
Target.Offset(0, 3).Value = 52
End If
End If
If Target.Address(0, 0) = "K8" Then
r = Application.Match(CDbl(Range("K8").Value), Range("P:P"), 0)
Range("R" & r).Value = Range("N77").Value
End If
End Sub

Is this what you are trying to achieve?

**fffleague15** · 11-28-2015, 07:20 AM

Hi mate,
Thanks. Where abouts do I put this code?

**sktneer** · 11-28-2015, 08:06 AM

Right Click on Round One Sheet Tab --> View Code --> In the opened code window, delete the existing code and paste the code from post#2.

Get The Values to Stick and Stay

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Get The Values to Stick and Stay

Re: Get The Values to Stick and Stay

Re: Get The Values to Stick and Stay

Re: Get The Values to Stick and Stay

Thread Information

Users Browsing this Thread

Similar Threads

How do I link coluimns together so values from each column stay together.

[SOLVED] Inserting row : not all values are changed one stay the same??

Why is it when i do sort values do not stay the same

Using the "&" to stick text and values together; How to round the numbers?

Getting values from function to stay on reload

Format won't stick

[SOLVED] Weird problem -- formulas get lost but their values stay in place

Bookmarks

Bookmarks

Posting Permissions