Custom List Message Item  

using col SUM function, but column contains imsub function totals, and cannot get total?
IMSUM returns complex number in text format, and SUM ignores any texts.
If in column you have mix of complex numbers and numbers you may use IMSUM to sum entire column. R...
Read More
Do you have any text on the TOTAL and SUBTOTAL rows to help provide logic to include or exclude the row? Would it be possible to provide a nonsensitive representation o ... Read More  
Can someone help me put the following into a formula for me?
If D2>E2, then D2, If D2<E2, then E2
No matter what I try, I can't seem to get it to work.
Thanks everyone!  
Hey y'all. I've got two very large, different sized arrays that I need to find common elements between. For example, consider that I have the two following, arbitrary, madeup arrays:
I have common names between the two arrays, and I have noncommon names ... Read MoreHi Peter,
I have an alternative robust approach to solve this problem away from VBA. This can be done by using Power Query which is also known in Excel 2016 as (Get & Tran ... Read More
I need a simple function that just does the following . if a4  b4 is less than or = to 0 then show NIL . can anyone help please
 
Hello, I need to create a function/formula for conditional formatting. First i need to match a value from first sheet to second sheet row and want to check that, match value column stock is 0 or not for highlight value in first sheet.
example i h ... Read MoreHi,
So if you type any value in "May 2018" Workbook, "Received Items" Sheet, In Column B and If that code is found in the "Bin Card" Workbook, "Items List" Sheet, In Colu ... Read More  
I need an excell spreadsheet that can fill information based on given information.
For example column A in each cell will have text as below sales order number '' sales order number '' sales order number '' sales order number '' sales order number ''
Ok with t ... Read MoreHi Ross,
Supposing that the order numbers are in column B next to order labels, so please use this formula in column C: =REPLACE(A1,SEARCH("'",A1)+1,0,B1) And find it in th ... Read More  
I have a simple spread sheet containing customer numbers which variously range from 4 to 968 (ie up to 3 digits), and in Excel, normally, no leading zeroes are retained. By changing the column format to CUSTOM with 000, all leading zeroes are now shown ... Read More
That's magic. And yes, works like a dream. Thank you.
Hi Ross,
Supposing that the customer number is in cell A1, please use this formula: =TEXT(A1,"000")&"/1000"
Regards Read More  
Hi I have a list (File B) where I want to fill in the location area in a column right next to the suburb, depending on the suburb. Means there are several suburbs with the same location area. The reference list (File A) is on a separate excel file and I'm n ... Read MoreHi,
No matter if the lookup table is in the same workbook, or in another one. For now, you can depend on this formula to lookup the location from the File A: =INDEX('[File... Read More  
I would like to use divide formula between cells but my numbers in my cells has like $ sign or ''Ct.'' letters next to numbers. what should i write into formula to get rid of the letters or signs which is next to numbers and make excel get just number and ... Read MoreIt depends. If you have cells with applied currency format, you shall do nothing, values are numbers. Did you try to do any ariphmetic with your cells?  
Hello,
The following formula searches the last cell with text in the sheet called '500 Amy' in column A. =LOOKUP(2,1/('500 Amy'!$A:$A<>""),'500 Amy'!$A:$A)
I would like to replace '500 Amy' with some sort like an INDIRECT formula so that I can reference th ... Read MoreINDIRECT for '500 Amy'!$A:$A will be INDIRECT("'" & A1 & " " & B1 & "'!$A:$:A")
Best Response confirmed by Queenie Lai (Occasional Visitor)
 
Good day,
I'm looking for a way to convert numbers(which inside a single cell) into words. I search the net and found this code: ============================================================== Option Explicit Sub SetNu ... Read More
I suggest you use built in formulas instead of user defined function. I have written a long formula that achieves converting numeric to words. Will it work for you?
 
Hello,  
I have 2 sheets. Sheet 1 is Transactions and Sheet 2 is stock summary.
Under Transactions, I have entries that look like
A B C D E F 1 Action Sto ... Read MoreHi Minghao,
Please try this formula and find it in the attached file: =IF(ISNUMBER(MATCH(A2,Transactions!$B$2:B4,0)),INDEX(Transactions!$F$2:$F$4,MATCH(MAX(IF(A2=Transacti... Read More Best Response confirmed by Minghao Liao (Occasional Contributor)
 
Hi, I am using TEXTJOIN to being into one cell entries from a separate column, where I am ignoring empty cells and separately by &. This works fine. However, I can have duplicate values in an individual cell in the column I am using in my TEXTJOIN and I'd li ... Read MoreSo sorry I got there in the end, with help from @Haytham Amairah I went back to the video you linked and used the following formula, remembering to use Ctrl+Shift+Enter to ... Read More  
Hi,
I have a spreadsheet that I need help adjusting to formulas on to ensure the accuracy of the data reporting. I currently have it set up to remove "N/A" from the denominator calculations but if I have an entire row of "N/A" it returns a "#DIV/0!" error ... Read Morenull null
Try something like this setup. See the example file for reference. You could also use a formula that sets the N/A to 0 i.e.
=IFERROR(B3/C3,0)
Read More  
Hi! I need to create a new routine so that it will highlight in the "Summary" sheet in Hi Ong Zhen Find the explanation in the code comments
Option Explicit Sub checkAttendance() Dim sWs As Worksheet  
I am having trouble using the formula I have found for excel, on excel online to sum the same cell from 30 different sheets. I tried: =SUM(1!E47,2!E47,3!E47) what am i doing wrong? does this function work online? thank you Read More Yes, it works with Excel Online if your sheets have names "1","2" and "3". =SUM('1'!E47,'2'!E47,'3'!E47) or =SUM('1:3'!E47) Hi Try this one =SUM(Sheet1:Sheet2!B1) Change the range as per your requirement.  
Good afternoon,
I need help with the following IF statement as it is not working as I want it to. =IF(AND(E1=”Carbon”,OR((F1<0.5,F1>2.1,G1<0,G1>1.5,H1<0,H1>0.5,I<=0,I1>0.3,J<0,J15>0.25,K1<0,K1>1.5)),”FAIL”,IF(AND(E15=”Alloy”,OR((F1<0.5,F1>1.5,G1<0,G1>5,H1 ... Read MoreHi
Try the following =IF(OR(E1="Carbon",E1="God"),"Pass",IF(AND(F1<0.5,F1>2.1,G1<0,G1>1.5,H1<0,H1>0.5,I<=0,I1>0.3,J<0,J15>0.25,K1<0,K1>1.5),"Pass","Fail")) Read MoreHi Adam,
Not sure about your OR logic, if it is correct the formula could be =IF(AND((E1="Carbon"),OR(F1<0.5,F1>2.1,G1<0,G1>1.5,H1<0,H1>0.5,I1<=0,I1>0.3,J1<0,J1>0.25,K1... Read More  
I have 3 separate pivot tables (fed by 3 data sources) on 3 separate worksheets within a single workbook that are all linked to 1 slicer. Based on the slicer selection there can only be data populated in 1 of the pivot tables, the other 2 will be blank. I ... Read MoreCan you please post an example file with nonsensitive data It's often easier to address the issue with a sample file or a more concrete example of the data representatio ... Read More  
Hi, I am having a difficult time trying to figure out what is wrong with conditional formatting. I want it to be: green over 95%, yellow 85% to 94.99%, and red below 85%. However, the highlights in the attached picture show it's not working as intended.
Read More Try setting the Type to Number rather than Percent and then using 0.95 and 0.85:
You can reference the attached Excel Workbook for an example.
Read More Best Response confirmed by Walter Richards (New Contributor)
 
Hi, hoping you can help me, I have created a spread sheet in sheet 1 for names and info for entry to a judo competition and in sheet 2 I have created a weigh in card, what I need to do is get the relevant information to auto add to the weigh in cards, I'v ... Read MoreEmma,
I replaced the direct references in the first card with INDEX() formulas and used a helper column for the row numbers. Then I copied the first card to the right whic ... Read MoreBest Response confirmed by Emma Jones (New Contributor)
 
I have 52 names that form 26 teams (of 2). Each week, I need another set of 26 teams, but they must be unique from the last 25 weeks. Ideally, the final output should be similar to the "Monthly Team Rotation" calendar below or easily pasted into the shee ... Read More  
Some Background
We provide a software system to organisation to manage their grant making processes. Our online system removes laborious paper centric practices and moves this all online.
When a client signs up with us, we need to import a history (3 yea ... Read More
I expect the Fuzzy lookup addin might help:
https://www.microsoft.com/enus/download/details.aspx?id=15011 Read More  
Preemptive apology for bumbling through this question. I have a master table with two columns containing (A) codes and then (B) street names that match each code, i.e.  275197_5  MAIN ST . I have reports that are generated containing various codes fr ... Read MoreElliot,
it's either VLOOKUP or INDEX()/MATCH(). Look up values with VLOOKUP, INDEX, or MATCH Read More  
Hi  the graphic visual I wish to display is eluding me...  
I have a monthly expense sheet with the dates of the week autopopulating. However, I would like to create a header field that automatically lists the first and last day of that week in the following format: Week of 4/30 to 5/6. Can anyone tell me how I c ... Read MoreHi Ivan,
Could be ="Week of " & TEXT(C2,"M/D") & " to " & TEXT(D2,"M/D") where in C2 and D2 start and end dates Read More Best Response confirmed by Ivan Rodriguez (New Contributor)
