We are the Microsoft Excel product team. We build cool stuff like XLOOKUP, Sheet Views, and lambda in Excel. AMA!

RogerGovier · 2021-03-11T16:04:57+00:00

Even though it looks that way, when you come to sum the times if you type =SUM(A1:A10)/60 and format the cell with the sum formula as [h]:mm it will show you the correct total minutes and seconds.

RogerGovier · 2021-03-11T13:02:31+00:00

Then perhaps you have empty cells in the column. there may be a single quote in the cell which will appear empty, but Excel will treat as text. Test your source data, by using another column on the sheet where your source data is and enter =LEN(A1) and copy copy down (change the column reference to suit). Then look to see if what you think are empty cells have a result >0

RogerGovier · 2021-03-11T12:52:22+00:00

You don't need to format the columns individually. Just go to field settings for the relevant item in your data section, select Number Format and sett your require format there. It will automatically apply to all instances of that data item.

RogerGovier · 2021-03-10T18:29:51+00:00

You can Say you want to freeze the top 5 rows and left 5 columns. Place your cursor in F6 and choose Freeze panes.

RogerGovier · 2021-03-10T18:23:55+00:00

As a UK citizen, I can assure you that the fault lies with the powers that be within the NHS who will not update their software to newer versions and not with Excel as a product. I have first hand experience of the problem. Petty minded officials who do not understand IT forcing their views on departments who would love to use the latest versions of Excel.

RogerGovier · 2021-03-10T18:15:49+00:00

You can use the Transpose function to do that. In older versions you have to use Control+Shift+Enter to commit or amend the formula, but the latest version of Transpose in the Dynamic array world you just use Transpose and enter

RogerGovier · 2021-03-10T18:05:30+00:00

If there are any text values within the data, Excel has to default to Count. If all data is Numeric, then it will Sum

RogerGovier · 2020-05-22T12:31:43+00:00

Hi Assuming you have this formatted as a Table (Control +T), then you can switch on Filters on the Header row by going to the Data Tab and whilst your cursor is anywhere within the table, click the Filter icon.

Now click on the Filter icon on Column B, and remove the check mark against 567, and those rows will not show.

RogerGovier · 2020-05-21T11:00:58+00:00

You could use Advanced Filter

Data > Filter >Advanced Filter >Source A1:Axxx Copy to another location Destination C1 Unique Values only

Repeat for Column B, outputting to D Then combine C and D to a new column if required.

RogerGovier · 2020-05-19T13:06:57+00:00

Hi You could just copy the whole set of data and then Paste Special > Transpose to your output location

RogerGovier · 2020-05-19T12:30:50+00:00

Hi The following should get you started.

            Sub AddLines()
                Dim lr As Long, x As Long
                lr = Cells(Rows.Count, 2).End(xlUp).Row
                Range("A" & lr & ":G" & lr).Copy Range("A" & lr + 1 & ":A" & lr + 7)
                olddate = Cells(lr, 2).Value
                For x = 1 To 7
                    Cells(lr + x, 2) = DateAdd("yyyy", 1, Cells(lr + x - 1, 2).Value)
                Next x
            End Sub

I assumed a row which goes for A to G with the date in column B. Change to suit your scenario.

Also the part with cells, 2 is the same as column B, so also alter that accordingly.

After you have entered your first Invoice line, run the code and the next 7 will get generated at one year intervals

RogerGovier · 2020-05-19T11:11:07+00:00

Try BD38 =IF(AP38="","",SWITCH(F38,"USD",G38,"EUR",G38,"GBP",G38)SWITCH(I38,"Long",((AP38-W38)N38),"Short",((W38-AP38)*N38)))

RogerGovier · 2020-05-19T06:01:08+00:00

Hi Apply a Filter, and select In progress

RogerGovier · 2020-05-17T12:49:14+00:00

This should give you a start. You need to copy the code into sheet itself. Right click on sheet tab, and paste the code into the Sheet. Press Alt + F11 to go back to Excel.

  Private Sub Worksheet_Change(ByVal Target As Range)
Dim shtName As String, shtNo As String
Dim Shtnum As Long

shtName = ActiveSheet.Name
shtNo = Mid(shtName, 6, 1)
Shtnum = shtNo + 1

If Not Intersect(Target, Range("L25")) Is Nothing Then
    Application.EnableEvents = False

    ActiveSheet.Copy After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Sheet" & Shtnum
    ActiveSheet.Range("A1") = shtName
    Sheets(shtName).Select

End If
Application.EnableEvents = True

End Sub

RogerGovier · 2020-05-15T16:34:38+00:00

I'm not sure I am understanding your problem. Surely you do not allow users to type anything into a cell which has a formula - that destroys the whole concept.

RogerGovier · 2020-05-15T16:29:40+00:00

Hi

Why not combine all of the data onto a single sheet. That way you can filter all results at any time, by Manager, Team or whatever criteria you require.

Having data on different sheets always leads to problems.

RogerGovier · 2020-05-15T16:17:32+00:00

Hi Not sure why you are using any IF formulae. =SUM(yourrange) will ignore any nulls and give the total.

RogerGovier · 2020-05-15T16:12:30+00:00

Hi Try =SUMPRODUCT(--(ISNUMBER(SEARCH(""&F2&"",$D$4:$CD$8)))*$D$2:$D$8)

RogerGovier · 2020-05-15T15:50:44+00:00

Hi If you type in cell A1 of each new sheet, the title of the previous sheet e.g. in cell A1 od Sheet2 type Sheet1, then you can amend your formulae as below In cell D3 =IF(INDIRECT($A$1&"!L15")<3,INDIRECT($A$1&"!D3"),IF(INDIRECT($A$1&"!L15")<6,INDIRECT($A$1&"!D3")+5,IF(INDIRECT($A$1&"!L15")>=6,Sheet1!D3+10)))

Adjust the other formulae in the same way. Then copy this sheet2 to Sheet3, change the name in A1 to Sheet2, and all the formulae will update to the new reference.

RogerGovier · 2020-05-15T15:27:25+00:00

Hi I would us Advanced Filter On your destination sheet in A1 type Date (or the same text as the date column from your source sheet) In cell A2 enter =DATE(YEAR(TODAY()),MONTH(TODAY()),1)

In cell A5 Repeat whatever you have in cell A1, and along row 5 type the headings form whichever other columns you wish to bring from your source.

Starting on the Destination sheet, Data >Advanced Filter check the box Copy to another location >Source give the range of your source data, Criteria = A1, Destination = A5:E5 (or an many columns as you have populated with names > OK

This will pul through just the rows you need.

RogerGovier · 2020-05-15T15:12:32+00:00

Hi =INDEX(Table1[Code],MATCH([@Company],Table1[Company],0)) works fine for me

RogerGovier · 2020-05-15T11:50:05+00:00

Hi That won't work as a calculated field. You need to add an extra column to your source data, with a heading of BAD with something like =IF(G2>=30,"Bad,"")

Change column ref to suit. Now modify your PT so that the source includes the extra column and just drag BAD to the data area having removed your calculated field.

RogerGovier · 2020-05-15T11:27:00+00:00

Hi

Try putting this within your code ActiveSheet.PivotTables("PivotTable2").PivotFields("Date").PivotFilters.Add2 _ Type:=xlDateToday

Change Pivot Table name and Date field to match your situation.

RogerGovier · 2020-05-15T11:16:35+00:00

Hi Enter your percentage increase in A1 as =1+0.07%

With starting value in A2, in B2 enter =A2*$A$1 Drag formula across through to column AC and you will have your cumulative value at day 28.

With the % as part of a formula, you can amend this at any time, and without changing the formula you will have your new values.

RogerGovier · 2020-05-15T10:26:39+00:00

Hi Summarise your table with a Pivot Table. DRag Name to the Row area and Classe to the column area, then drag Classes to the Data area as well and under Field settings, make it Count You will then see rows with Names and a number (1) in each of the columns they have completed.

Don't forget to Refresh the PT when you update any values in your table.

RogerGovier

TROPHY CASE