formulae

  1. 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...
  2. 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...
  3. 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...
  4. 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])"...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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?
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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...
  19. 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...
  20. F

    Text conversion to number

    Good day all I know there is someone that can assist. I work at a company who's accounting system always exports negative numbers/credits with the annotation of cr at the end of the number for eg, 1000cr, instead of -1000. Is there a formulae that one can use to change it to the latter?

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top