Can I Get The Two Figures To Constantly Stay?

[fffleague15]

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. Just to make it clear, I would like the "40" and "£152.00" to stay in each of the boxes when the user changes the dates.

Many thanks, FFFLeague15

Trial.xlsm

[Arkadi]

The problem here is that the table on the right gets updated based on what the date is, since the table on the left is not date specific... and applies only to the date at the top. One thing I can suggest is to make a button next to the date at the top, which you use to "Freeze" the data for that week. In other words, the button would take the row for the given week and put in static values on that row of your right hand table.

[dflak]

Arkadi is right. I am going to propose a simpler design. It uses Excel tables and it uses data validation. You can find out more about tables here: http://www.utteraccess.com/wiki/inde...ables_in_Excel and Data Validation here: http://www.utteraccess.com/wiki/inde...ata_Validation.

There are a couple of nice things about Excel tables: one of them is that the table grows to accommodate however much data you have. So you don't have to guess how many houses you will do in a time period. You won't have to deal with extra lines. Put the cursor in the last row and column of the table and press <TAB> and it opens a new line in the table for you. Another thing about tables is the natural-language type syntax.

I did use one trick with the data validation. The source for the drop-down list in Week Beginning is =INDIRECT("Table_Dates[Start Date]") normally you would use a range of values or a named range, but this syntax is a shortcut if you already have the data in a table.

So here is the setup: Column A has the week beginning, you can select the value you want from the dropdown list. Alternatively, you can type in the date manually. You can use the latter method to open up a new line in the table if you start typing on the row immediately after the existing data. In either event, you should not be able to add in a wrong date.

The rest of the columns have the same formulas as your worksheet except that the formulas are in table format. So instead of =0.5*K2 you have =0.5*[Total Windows Cleaned].

The Table in Columns M through O is used both as a source for data validation and for the computation of the results you want. The SUMIF Formula is used =SUMIFS(Table_Windows[[Total]],Table_Windows[Week Beginning],[@[Start Date]]).

The box in Columns P-R is called a slicer. It's just a fancy name for filter. You can also use the filter in Column A to limit the data to whatever month or week you are working in. If you do, the slicer will show you what weeks are selected. They are two different ways of doing the same thing. You can read up on slicers using a search engine. They're a lot simpler than they look.

That's all there is to it. You will have the entire year's data in the green table and you only need to look at as much of it as you need. You don't need extra lines "just in case" and if you have a really busy month, the tables won't limit the number of houses you can do.
Windows.xlsb

[Richard Buttrey]

Hi,

A simple Sheet Change event macro could do this.

Name the K8 cell "selection". In M8 enter the formula

=MATCH(K8,P:P,FALSE)

Then replace the current sheet change event macro with this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lRow 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 Not Intersect(Target, Range("Selection")) Is Nothing Then
        lRow = Sheet1.Range("selection").Cells(1, 3)
        Application.EnableEvents = False
        Sheet1.Range("Q" & lRow & ":R" & lRow) = Sheet1.Range("Q" & lRow & ":R" & lRow).Value
        Application.EnableEvents = True
    End If


End Sub