formulae

Good afternoon everyone. I have an Excel order form we use with our customers for them to place small orders but I need to protect the formulae while retaining its functionality. Each cell in column C has the =sum(A2*B2/1000) to calculate the correct meterage from the quantity and length...

I have developed a formula which shows customer activity by cohort (each month a new customer "Cohort" comes on stream). The pattern is linear and in each month each customer who is active buys one item. As an example CUstomers in their first year will buy three times Month 1,5, and 9. In Month...

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

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

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

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

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

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

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

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

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

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

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

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

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?

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

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

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

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

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