1. Dermot

    Code for marking formulae as a check they have been copied correctly

    This code will make a copy of the active sheet and then mark up the formulae, using shading to show which ones have been copied, and from where. It works from left to right, top to bottom. Solid colour = this cell has NOT been copied from left or above, ie it is new Horizontal hatch = this cell...
  2. X

    How To Populate Date Conditinally?

    Dear All, I do have the following sample data set across A1:F4. <colgroup><col span="4"><col><col></colgroup><tbody> Site SKU Qty Date Maximum Qty Maximum Qty Date a 1 10 01-Oct 35 10-10-2019 a 1 35 10-Oct 35 10-10-2019 a 1 25 24-Oct 35 10-10-2019 </tbody> In column E the formulae is...
  3. A

    Filtered List Formula

    Hi I’m attempting to create an array formula that only picks upvalues in a filtered list. Is itpossible to combine both formulae Array formula: =(SUM(IFERROR((AK5:AK215<(TODAY()))*(AK5:AK215<>""),0))) (courtesyof Jasonb75) Picks up non-blanks with the filtered list...
  4. B

    Passworded Sheet is NOT protected

    I have a very involved Workbook which embodies many formulae. To ensure that each worksheet and process is not inadvertently corrupted I have initiate two strategies. The first was to construct macros which write formulae to workbooks, sometimes on demand but mostly when called by some other...
  5. T

    VBA instead of array formulae

    Is it possible to replace array formulae with just VBA? If I have the data as follows: 1 10 2 25 3 30 and I want to sum column A and put the result in cell D1, I can record a macro to generate this code: Range("D1").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:R[2]C[-3])"...
  6. B

    Manual enter formula 'conflicts' with VBA created formula

    I have a rather complicated table of formulae which could be easily corrupted by manual manipulation (I'm aware of password protect but that is not my concern). I have set about rewriting all of the formulae in that table in Visual Basic. Now in one particular column the formulae are of the...
  7. C

    index / match now sum

    Hi, First time poster long term lurker :) Wonder if someone can help me out? I have a one to many relationship I need to add up. On my first sheet I have the 'one', a server in field A with my formulae in field B On my second sheet I have the 'many', the servers numerous disks in field C. I...
  8. L

    Index Match Multiple criteia Giving zero value

    <code>=INDEX('Search Console Data'!$D$2:$D$76140,SMALL(IF(ISNUMBER(MATCH('Search Console Data'!$C$2:$C$76140,Sheet1!$B$2,0)),MATCH(ROW('Search Console Data'!$C$2:$C$76140),ROW('Search Console Data'!$C$2:$C$76140)),""),ROWS($A$2:A2)))</code> The above code is for single criteria works awesome...
  9. P

    If Statement

    Hey guys, I need a small help. Cell G36 has the formulae =O38+O39 Cell F36 has the formulae =O32-G36 So i want to make a IF statement in cell D43, If G36 is equal to 0 (or if cell is blank) , then i want to show blank or 0 in D43. If G36 is not blank then, I want it to have this formulae...
  10. M

    Formulae not pasting all the way down to end of range

    Hi I've got a spreadsheet that imports new or updates existing using VBA. I then have a Sub which copies the formula in row 1 from columns BY to CL then pastes them into the cells from BY5 to CL5 until the bottom of the range that the new data has gone up to (see below). The new data goes...
  11. P

    Formulae Help

    I have a formulae which you can see returns Y, N or # NUM! where column A is a start date and Column B is the finish date. If no finish date you get # NUM! IF(ISNUMBER(DATEDIF(A30,B30,"d")),IF(C30<=90,"Y","N"),"# NUM!") I have another formulae which looks for the result and if Y or N then all...
  12. T

    does IFS() calculate the redundant terms in background

    to save wasted processing I sometimes use =if(A1="","", [heavy formula]) which clearly works, as the heavy formula isn't calculated when its redundant. But if i use IFS() to do the same (as per the below) =IFS(A1="", "", A1=0, "", 1=1, [heavy formula]) it should do the same... BUT when...
  13. T

    Protecting cell ranges

    I have some cells with Formulae in and at times users need to use CLEAR to empty cell content but I dont want them to CLEAR cells with formulae in. Normal protection appears to lock the whole sheet and I just want to protect cell ranges.... Is there a way to do that?
  14. S

    Slow to close workbook if another workbook open

    Hi all! Not really sure what supporting info to provide but I have a spreadsheet that has a rather odd behaviour. The workbook has a lot of formulae and a sheet that has over 7000 rows. That sheet has about 10 columns of formulae which include INDEX/MATCHES referring to a couple of tables in...
  15. F

    Problem with column including vlookup in table not sorting accurately

    Hi - hope someone can help me as my brain is aching with trying to work this one out! I have a large table with loads of columns - many of them including text but a few of them are vlookup formulae. When I sort the table on one of the text columns, the columns with the formulae in them go...
  16. P

    Find specific text error

    I have a formulae in an Excel cell that looks at a text string and returns a code dependent on the found text. I am having to a add a similar code to one already used and have an issue with the result. The formulae is :- =IF(ISERROR(FIND("-SL",$C$4,1)),"","SMT...
  17. J

    cell reference separated from formulae

    I have a question regarding cell referencing (range). For instance in the vlookup function you defilé the range in the second part as i.e. C3:E9 like this vlookup(what, range, position, false). I know that you can select the range and rename this. But can I also put the range as input in for...
  18. M

    A cell with text and a formulae, then more text

    I have a cell N40 with this info After MG1 arrives at 3:30, then advise What I would like is the 3:30 refer to a cell N33 which has a time based on a main cell N31 elsewhere eg main cell N31 has 3:15, secondary cell N33 has this formulae =N31+TIME(0,15,0) which shows as 3:30 which is used...
  19. J

    =Left Formulae

    Hi All, i have a left formulae which copies the 2 first letters from another cell. I have put a true /false statement in this cell though now, and now the left formulae is showing TR and FA before data is entered. Is there a way for the Left formulae to ignore if data in the cell is TR or FA...
  20. G

    Best Way to Arrange Data in Worksheet

    Hi, I have a number of worksheets in a workbook from which I extract data using lookup-type formulae to a single “Data” worksheet in the same workbook. The data in the “Data” worksheet is arranged in a conventional form, with column A containing Product Name, column B showing Sales, column C...

Watch MrExcel Video

This Week's Hot Topics

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
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 "".
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