tag:blogger.com,1999:blog-1869924468172210809.comments2016-05-05T06:03:01.430+01:00Not Just NumbersGlen Feechanhttp://www.blogger.com/profile/12461985809302036952noreply@blogger.comBlogger1332125tag:blogger.com,1999:blog-1869924468172210809.post-9970076784641586242016-04-28T20:00:10.578+01:002016-04-28T20:00:10.578+01:00Rather than use the wildcard, have a dummy workshe...Rather than use the wildcard, have a dummy worksheet at the end included in the sum, then just inswrt the new worksheets before that one.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-65613351676096297082016-04-28T19:59:47.692+01:002016-04-28T19:59:47.692+01:00Rather than use the wildcard, have a dummy workshe...Rather than use the wildcard, have a dummy worksheet at the end included in the sum, then just inswrt the new worksheets before that one.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-21394010730592263872016-04-28T18:09:41.363+01:002016-04-28T18:09:41.363+01:00It is true about: =SUM('work*'!A1), but if...It is true about: =SUM('work*'!A1), but if you have let's say work1 and work2, excel populates the formula with the actual sheet names. For example, when you put in the formula box: =SUM('work*'!A1), after the enter you get: =SUM('work1:work2'!A1). Is there any way to keep the original formula, so it can be still valid when adding more worksheet with the same pattern name)? Thanks in advance. DavidDavid Lealhttp://www.blogger.com/profile/15282075893436086442noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-12665074871954824072016-04-25T12:37:32.532+01:002016-04-25T12:37:32.532+01:00Which cells have you highlighted to apply the form...Which cells have you highlighted to apply the formula to?Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-49175842740214663492016-04-25T12:03:23.780+01:002016-04-25T12:03:23.780+01:00Hi great post.
I have a table that has standard co...Hi great post.<br />I have a table that has standard conditional formatting showing green if the figure in the cell is positive, and red if negative. I have another table underneath with the same formatting (1 table shows sales, the other shows profits). I now want to format the next column to green if both sales and profit are green/positive, and red if both are red/negative, and yellow if sales are positive and profits are negative. I have tried to insert a formula in conditional formatting option e.g. for green:<br />=and(a2>0,a29>0)<br />but its not giving me the right results. Can you please help me? Where am I going wrong? Thanks a lotFraz Rnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-59602006842141027462016-04-24T09:07:31.143+01:002016-04-24T09:07:31.143+01:00Your formula should return bkank if A1 is actually...Your formula should return bkank if A1 is actually empty, however if A1 contains a space for example, it will return "true".<br /><br />The following should do what you want even if there is text (such as a space in A1:<br /><br />=IFERROR(IF(VALUE(A1)>0,"true",""),"")<br /><br />VALUE(A1) will return the value of A1, or an error if A1 does not represent a number.<br />Encasing the whole function within the IFERROR function will return the second argument of the IFERROR function if the first one returns an error.<br /><br />=IFERROR(YourFunction,"")<br /><br />Will return "" if YourFunction returns an error, otherwise it will return the result of YourFunction.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-23992403753252750292016-04-24T06:45:14.119+01:002016-04-24T06:45:14.119+01:00I'm using this statement =IF(A1>0,"tru...I'm using this statement =IF(A1>0,"true",""). The problem is that if A1 is blank(which isn't greater than 1), it is still putting "true" in B1. How can I make it show cell B1 as blank if A1 is blank?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-49795998362528406552016-04-12T21:44:54.461+01:002016-04-12T21:44:54.461+01:00Thanks for asking the question, and thanks for the...Thanks for asking the question, and thanks for the answer, Glen.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-6121238306387107642016-04-10T07:11:02.139+01:002016-04-10T07:11:02.139+01:00If the current sheet is within the range Worksheet...If the current sheet is within the range Worksheet2, say, in our example, then it should work no differently. If not, just add it as below:<br /><br />=SUM('Worksheet1:Worksheet5'!A1)+A1Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-25316757042401269642016-04-09T19:22:20.978+01:002016-04-09T19:22:20.978+01:00How can we also add the current sheet as well? How can we also add the current sheet as well? antepartshttp://www.blogger.com/profile/03454193715204894783noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-50652421293488702572016-04-07T16:11:00.290+01:002016-04-07T16:11:00.290+01:00I'm not sure what you'e trying to do.
From...I'm not sure what you'e trying to do.<br />From what I can understand:<br />if (B4*C4/D4) is an error, you wish to return "", this leaves only one argument to enter in your IF statement - i.e. what you want it to return if it's not an error. You have the following:<br /><br />(E4*F4/G4),"",H4*I4/J4+K4*L4/M4<br /><br />which is 3 arguments without a bracket to close the IF.<br />Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-81341513510569249872016-04-07T16:05:28.790+01:002016-04-07T16:05:28.790+01:00IF(ISERROR(B4*C4/D4),"",(E4*F4/G4),"...IF(ISERROR(B4*C4/D4),"",(E4*F4/G4),"",H4*I4/J4+K4*L4/M4<br />where false formula please solve problem Om Gautamhttp://www.blogger.com/profile/18409525484390388854noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-52698582474638183252016-04-02T07:06:15.740+01:002016-04-02T07:06:15.740+01:00John - I'm not sure what you are asking. As th...John - I'm not sure what you are asking. As the commenter above said, you can paste anywhere you want. Are you suggesting grouping sheets in such a way that the linking is not between specific cells in those sheets that are not in the same place? I'm pretty sure that's not possible, not without code anyway.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-80782983972344856362016-04-02T06:59:48.902+01:002016-04-02T06:59:48.902+01:00Thanks Jim, that sounds a plausible explanation.Thanks Jim, that sounds a plausible explanation.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-29948809852374261532016-04-01T23:35:13.650+01:002016-04-01T23:35:13.650+01:00you can copy stuff wherever you like, but this has...you can copy stuff wherever you like, but this has nothing to with grouping sheets - or am I missing something?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-68628851295892408932016-04-01T21:14:39.954+01:002016-04-01T21:14:39.954+01:00Hi Glen,
I might be missing it but is it possible...Hi Glen,<br /><br />I might be missing it but is it possible to have for example Sheet1 A3 copied into Sheet2 I8? Johnnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-91878736782836779972016-04-01T17:25:09.921+01:002016-04-01T17:25:09.921+01:00@Angela/Glen above (for some reason I can't us...@Angela/Glen above (for some reason I can't use the Reply against that thread)<br />Protecting doesn't seem to affect that; what it might be is that Angela has multiple sheets selected in the workbook she is copying from - when a single sheet is selected in the target, then a multi-sheet copy is copied across sheets in the target from that sheet (if sufficient exist)<br /><br />hope that makes sense<br /><br />jimAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-9795236642207723452016-04-01T14:59:32.099+01:002016-04-01T14:59:32.099+01:00Angela - could the workbook be protected?Angela - could the workbook be protected?Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-22869732159255639012016-04-01T13:11:14.005+01:002016-04-01T13:11:14.005+01:00Hi Glen,
I wonder if you can help me - my sheets ...Hi Glen,<br /><br />I wonder if you can help me - my sheets are acting as if they're grouped (ie when I paste into one sheet, it copies into them all in the same cell). However, when I right clock they do not show as grouped/there is no option to ungroup. I have never grouped them before, except to select all for the search function. Any advice would be greatly appreciated!<br /><br />Thanks,<br />AngelaAngela Symonshttp://www.blogger.com/profile/08670387910909173065noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-51537933630530101482016-03-29T16:39:16.998+01:002016-03-29T16:39:16.998+01:00This worked well. Thanks!!This worked well. Thanks!!Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-38379294618521509722016-03-29T12:32:19.751+01:002016-03-29T12:32:19.751+01:00Nice solution for a variable sum. Thnx!Nice solution for a variable sum. Thnx!Henknoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-21387287268508730392016-03-27T22:55:32.839+01:002016-03-27T22:55:32.839+01:00If it is just for ease of printing particular colu...If it is just for ease of printing particular columns and/or rows for a hard copy document it may be easier to just "hide" unwanted data. Anon E. Moushttp://www.blogger.com/profile/11737338930545999695noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-19668688248041346122016-03-17T13:18:55.609+00:002016-03-17T13:18:55.609+00:00There are a few ways but I think SUMPRODUCT might ...There are a few ways but I think SUMPRODUCT might be the most efficient.<br /><br />If the BB dates are in the range B2:B1000 and the posted dates are in the range C2:C1000, then you could enter the following in D2 (and copy it down):<br /><br />=SUMPRODUCT(($B$2:$B$1000< B2)*($C$2:$C$1000 >C2))<br /><br />Obviously, change the ranges to suit your data. I would advise against using the whole column as this could lead to a lot of unnecessary calculation time. Just pick a number of rows that will comfortably cover your data.<br /><br />This will return the number of rows that exist where the date in column B is less than that in cell B2 AND the date in column C is greater than the date in cell C2.<br /><br />If this number is greater than 0, then a FEFO breach has occurred. You could then use a straightforward IF statement (based on column D) to conditionally format the row as per my post.<br /><br />The SUMPRODUCT works as follows:<br /><br />($B$2:$B$1000< B2) returns an array of 1s and 0s depending on whether the condition is true (1) or false (0).<br /><br />($C$2:$C$1000 >C2) does a similar thing with column C.<br /><br />The SUMPRODUCT multiplies the two arrays together and sums the result.<br /><br />The resulting array will only include 1s where both conditions are true, as if either condition is false, we will be multiplying by zero.<br /><br />I hope that makes sense.Glen Feechanhttp://www.blogger.com/profile/15601120984639539346noreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-82622340335486742642016-03-17T09:30:33.884+00:002016-03-17T09:30:33.884+00:00How do you tell there was a First Expired First Ou...How do you tell there was a First Expired First Out (FEFO) breach.<br />I have this very large data and am trying to determine if a product with a later (greater) BB date is posted before one with an earlier (less) BB date on excel. The BB dates are in a column while the posting date is in another column<br />I have been doing this manually and will like to have a formula that is faster.<br />I'll appreciate your assistanceAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-1869924468172210809.post-90337210605561895022016-03-15T19:05:14.930+00:002016-03-15T19:05:14.930+00:00That worked great! Thank you so much. That worked great! Thank you so much. Thomas Wightmanhttp://www.blogger.com/profile/13742685275205639957noreply@blogger.com