Help with Formular to keep constants
Hi I have 2 rows for percentage values as per this table. C3 will be the value of (C2 + B3) / 2. However I need to check for an empty value so my calculation is =IF($B$3="",0, IF($C$2="",0,($C$2+$B$3)/2)) - I hope that is right, A B C D E F G 1 Name Name Name Name Name 2 50.25% 70.78% 60.14% 24.86% 53.64% 3 Name 60.12% 55.18% 65.45 4 Name 45.58% 47.91% I then need to copy(drag) the cells across and down and provide the correct values, so it uses the same top value and left value. For example D3 will be =IF($B$3="",0, IF($D$2="",0,($D$2+$B$3)/2)). C4 will be =IF($B$4="",0, IF($C$2="",0,($C$2+$B$4)/2)) and so which will alter as I drag down or left and it keeps using the correct column value and row value based on what column/row it is in. What I am doing now does not retain the right column or row value. I hope I make sense. Thanks.
Summarising a number of sheets to a front sheet.
I have a simple spreadsheet with Sheets for individual investment details. The TABS contain the name of the investments eg BP I am trying to put together the front sheet summarising certain data from the individual sheets, one row per sheet. Data such as Investment Name and Value. How can I do this please?
MATCH formula anomaly?
Can anyone explain this? If I simply type 490.44 and then calculate 490.44 as 490.44/40*40 (or even directly B3/40*40) excel recognizes both as equal through a simple A=B calculation yet not with a MATCH calculation. What is more odd is any other number I use does not cause this error...
Combinar correspondencia ahora que han desaparecido los DDE con resultados con solo dos decimales
Buenos días: antes combinaba correspondencia de un word con un excell, para hacer mis facturas mensuales con DDE, pero ahora esa opción ha desaparecido y cuando combino me salen muchísimos decimales. He aplicado el formato directamente en Word modificando el campo de fusión con un cambio de formato numérico (por ejemplo, \# "#,##0.00" para forzar dos decimales), pero sigue sin hacérmelo... alguna idea para poder fijar solamente dos decimales en el excell para que los resultados de la combinación en word den solo 2 decimales?
Pasting formatted tables from Excel into other software - issues
I am copying and pasting a large table, with lots of cells merged into, headers for combined rows and colums, with lots of coloured and formatted cells. When it is pasted into Word or Excel, the first 20-30 rows are OK, but then after a while, mid-row, the colour formatting (but not the cell text contents) becomes displaced a two cells to the right. The colouring then corrects itself further down the table, but then drifts in and out of alignment. I think it may be an issue with the merged cells, particularly in the case where if they weren't merged then the content of the cell is wider than the cell, so requires wrapping - but this does not appear to be consistently the issue. I have tried unmerging everything and allowing columns to autofit to full width, but that does not resolve... it had been fine until around March 2026 - was there an update around then? Any suggestions welcome.
Closing VBA module is crashing excel
If I open a macro enabled workbook, go to developer>visual basic and try closing any VBA module windows, excel closes. If I then start excel, open a blank workbook, go to developer> visual basics and insert several blank modules, and then close one, excel closes. If I reboot my PC, open a blank workbook, go to visual basic and insert modules, now I can close them ok. But once I open an existing macro enabled workbook the problem starts over again. Weirdly, if I start from scratch, open a new workbook, then developer and insert just one module and then delete it, that's ok. I can do that repeatedly. Opening and closing form windows in the developer doesn't show the same problem. It isn't just one particular macro enabled workbook that is triggering the problem. Sometimes the workbook opens without macros enabled and I close it without enabling macros and the problem has been triggered. I have switched off all addins. I did recently activate the Inquire COM addin. My excel version is MS Excel for.365 MSO (Version 2604 Build 16.0.19929.20096) 64-bit running on Windows 11. I don't know when I first saw this but I think within the last month. It's a company laptop with phone IT admin support so I'm limited on the trouble shooting I can do myself. Office 365 has been uninstalled and reinstalled but did not help. Other users in my organisation are not seeing the same issue when they open the same workbooks. The IT admin however told me they did see the issue. Updated. Starting from a clean state after reboot, I open a new workbook, add several blank VBA modules then save as xlsm and close. Then when I open a new workbook the problem is triggered again. No VBA code has been run or written. Updated If I maximise the VBA module windows before closing them then I can close them without crashing!Series fill a formula down a column automatically skipping a set number of rows
I am trying to fill a formula down a column every 6th row but incrementing the variable in the formula ($A2) for each entry. The formula uses the Take command and inputs 5 rows of data, so to keep it from "spilling" I need the formula to increment every 6 rows, having one blank row between each section. I have been able to accomplish "copying" it each 6th row with VBA #1 and filling the series with VBA #2 but can't figure out how to combine the two. Any help would be appreciated. VBA #1: Sub FillEvery6thCell() Dim ws As Worksheet Dim startRow As Long, lastRow As Long, col As String Dim formulaText As String Dim r As Long ' Set your sheet and parameters Set ws = ThisWorkbook.Sheets("Top 5 Employees") col = "A" ' Column to fill startRow = 10 ' First row to start filling lastRow = 60000 ' Last row to fill ' Get the formula from the starting cell formulaText = ws.Range(col & startRow).Formula ' Fill every 6th cell For r = startRow + 6 To lastRow Step 6 ws.Range(col & r).Formula = formulaText Next r Range("A2:A60000" & iRow).Replace What:="@", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2 MsgBox "Formula copied to every 6th cell in column " & col End Sub VBA #2 Sub FillFormulasDown() Dim sourceCell As Range Dim fillRange As Range ' Define the cell containing the formula Set sourceCell = Range("A2") ' Define the target range Set fillRange = Range("A2:A5000") ' Fill formulas down sourceCell.AutoFill Destination:=fillRange, Type:=xlFillSeries End Sub
Excell function problem
I have been using Excel for a good few years, i have a spreadsheet that has been working fine until now. My main problem is that when i copy & paste data everything works fine, i have been able to add cells to make a new total. but when i cut & paste the value of cells i get a REF error. so when i need to move a series of cells higher or lower any cells that contain word they move fine, the cells containing numbers move but the cell with the function just goes to #REF, the function was working okay with my previous versions of Excel but now it will not. if anyone has got any idea what the problem is or any way of fixing it i would be most gratefull
