Custom List Message Item  

Hello, Hi Roy
If you remove Sum of PO amount and Sum of Ledger amount do you get the answer you want?
Also, are you using Power Pivot or just the relationship buttons on the ... Read More  
Hi there,
I need some help with some tricky excel formulas. Would greatly appreciate some help as I have nearly 7000 rows of data to sort out. The developer I was hoping to get help from has handed back to me and it's beyond what I can do.
The first 2 col ... Read MoreHi Nic,
It could be for the row #2
Column C =A2 Column B =IFERROR(LOOKUP(2,1/(($B21)=$B$2:$B2),$A$2:$A2),"") Column D =IF($B2=$B1,$C1,"") and drag them down. Attach ... Read MoreGreetings! As understood, please find formula for 4th column:=IF(AND(B3>B2,C2<>""),CELL("contents",C2),"") for 5th column:=IF(AND(B3=B2,C2<>""),CELL("contents",C2),"") Thanks ... Read More  
 
I have a sum formula in excel that I am using to calculate the total $ in column but filtering using an IF statement and a ISNUMBER(SEARCH) to look up a corresponding name a ... Read MoreHi Peter,
That shall be array formula, entering it into D29 use Ctrl+Shift+Enter instead of Enter (press Ctrl and Shift, hit Enter, release all 3). Read MoreBest Response confirmed by Peter Lang (New Contributor)
 
I need to frame a questionnaire for a survey in Excel; and I need to insert a "multiple textbox" typed question which is shown in the attached image. The link provided below that can exactly explain my requirement. Please anyone let me know if it is possi ... Read More  
hello,
My problem is; Every ... Read More  
Hi there
I'm looking for some help with an excel formula. Have tried myself but is beyond my capabilities.
In the table below, I need a formula for Column D. If I were to characterize the rule for this column, it would be:
Look up at all the Node values a ... Read MoreIt looks like duplicated post https://techcommunity.microsoft.com/t5/Excel/Helpwithtrickyformulas/mp/224608#M16794 Read MoreI think i can do it, but only with a couple of helper columns. I've attached a workbook that does what you wanted..
The helper cells do the following.
Row Number: an arra ... Read More  
formula to convert uppercase to lowercase and spaces to hyphens Example Read More That's duplication of https://techcommunity.microsoft.com/t5/Excel/formulatoconvertuppercasetolowercaseandspacestohyphens/mp/224926#M16822, I attached the file ... Read More  
formula to convert uppercase to lowercase and spaces to hyphens Example
Read More
=LOWER(SUBSTITUTE(A1," ",""))  
How can I put a data identification number in Exel ???? I have a problem... I have a database, where I have different products, and I need to assign an identification number to these products to be able to identify them more easily and at the same time tr ... Read More=IF(COUNTIF($B$1:B2, B2) >1,INDEX($A$1:A1,MATCH(B2,$B$1:B1,0)),MAX($A$1:A1)+1)
Check if we have already seen this item, if we have look up what its ID is, if not get tha ... Read MoreBest Response confirmed by Rogelio Emmanuel Valtierra Ayala (Occasional Contributor)
 
I am trying to embed a word document in Excel, but when i send the file to other, they cannot open the embedded word document. Please advise!
 
Hi All,
I could really use some help before I go jump off a bridge. Jk.
The issue I am currently having it related to using relative references in the GETPIVOTDATA function and cascading slicers. I have a PivotTable set up that has a list of students and ... Read MoreI'm not really sure what you are trying to do.
In your Example 2, you can use an IF to get rid of the 0s or #Refs
=IF(OR(A5="Grand Total",A5=""),"",A5) =IF(OR(A5="Grand To ... Read MoreBest Response confirmed by Anthony Smith (Contributor)
 
I am trying to write a formula to count a singular "1" if any number of No's appears within 2 distinct ranges.
So if no in B30, and/or B33:37 need to count as 1 what I have now is.
=IF(Countif('Sheet 18'!B33:B37,"no"),"1","0")+IF(Countif('Sheet 18'!B30, ... Read MoreHi Jordan,
As for formula question you may use =(Countif('Sheet 18'!B33:B37,"no")>0)*('Sheet 18'!B30="no")  
Hi everyone! Maybe someone can help me with this problem...
I'm using a software to manage telemarketing campaigns. This software allows me to create several informs and one of them is to know of much time an operator is logged in a campaign.
The problem ... Read MoreWow!
Amazing guys. Thank you so much for your answers, both of them valid.
I owe you one! ;) Another way could be with using of EVALUATE
Let define in Name Manager new name, let say TransformToSeconds, with value =EVALUATE("="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S... Read More Best Response confirmed by Daniel Martínez León (New Contributor)
Hi Daniel,
In such a complex situation, the best approach is to create some helper columns to extract days, hours, minutes, and seconds from each string.
Once we get each ... Read More  
Hi
I'm trying to figure out a formula in Excel. I don't know if this is possible but I can't get it to work correctly for me. I have two sections in a sheet. One section is this months sales, the other section is the grand total for the all time sales.
I tr ... Read MoreWhy not record each months total in a seperte table? You can then very easily use that table to keep running total.  
Hello !
After opening *.csv , saving changes with the combination of keys ctrl+ 1, is saved as * .txt , what is the problem ?
Office 365  
Hello, I came here because I find something wrong with Excel 2016 and Excel Online. It's impossible to make changes in Excel 2016 and Excel online at the same time. When one of the files is open in Excel Online,I got an error message in Excel 2016. We need t ... Read MoreHi Claire
In my understanding, this is by design. You cannot coauthor documents across Excel 2016 app and Excel Online at the same time, it has to be one or the other ap ... Read MoreHi Claire,
In general they work together. Which error do you have?  
I have 2 sheets, sheet 2 has Part description and Part Number in 2 columns. Part 1 Part No 1 Part 2 Part No 2 Part 3 Part No 3 Part 4 Part No 4
Sheet 1 I have used Data Validation ... Read MoreSimply reference the sheet in your lookup formula =VLOOKUP(A2,Sheet2!A:B,2,FALSE)  
I am trying to figure out how to select the average of the 10 largest numbers from among the group of numbers represented between C2 and V2. In other words how do you determine the AVERAGE of the 10 largest numbers in an array. Assuming you have at least 10numbers to average this would work
=AVERAGE(LARGE(C2:V2,ROW(1:10)))
enter it as an array. it wont work if there are less than 10numbers in c2 ... Read More  
Why this formula
=VLOOKUP(B2,table1[#All],table1[[#Headers],[column3]],FALSE)
Do I have to use a column number instead of a column heading? Read MoreBest Response confirmed by שלמה זלמן ויסמן (New Contributor)
 
Hello,
I have Windows 10 Pro (April 2018 update) and Office 356. Both are in German language.
I create a new excel file and select the template "Checkliste für Urlaub" (the checklist for holidays). Hello,
the problem is still existing, even with the actual version of Office 365. I created a new file right now but have the same problems.
Best regards
OLLI Read More  
I have a multisheet workbook that I am trying to use TextJoin. I have success using the TextJoin function like this =TEXTJOIN(", ",TRUE,Matrix!E2:E3000). I am trying to copy the columns into rows in another sheet. However, click and dragging does not ... Read MoreIf I understand you want to be able to drag your textjoin down, and have it join text from +1 column over on your matrix worksheet?
I think you were on the right track wi ... Read More  
Hey folks,
I'd really like to use excel for my graphs for a paper that I'm working on. I think that I'm very close to meeting the Journal standards, but there are a few more changes that I need to make, shown in the image below. Does anyone know if these ... Read MoreHow about the attached?
I think that does everything you asked for..
This was mostly just adding a 'dummy' series on a secondary axis to trick excel into adding the mirro ... Read MoreBest Response confirmed by Thomas Williams (New Contributor)
 
I am a newbie (hehehehe) I wrote the date (day="dddd") formula but if the cell is blank, it automatically fills "saturday"! How do i write a formula that if blank, will stay blank? thank you. Hi you can use =IF(F8=""," ",TEXT(F8,"dddd"))  
I'm trying to have a formula in a cell to calculate if another cell states "yes" but if it states "no" (from a dropdown list) then a text of "NF" will result. I tried by using this formula but it's not working. Can you help solve? =IF(H2="No", ["NF"], ... Read MoreHi Mike,
This is the correct syntax of the formula: =IF(H2="No", "NF", SUM(D2+E2+F2)/C2)
Hope that helps  
Hi everyone. Can you be so kind and generous to help me solving this issue:
I need excel to extract letters from a certain cell containing text, and give it to me in a certain column. Let say I copy/paste (I love you) in a certain cell, I need excel to put ... Read More  
I need information on how to reset the end cell in Excel 365. My current worksheet has 28K lines of data, but the end cell is at 1048576. I've tried the obvious select all open rows outside my data, then clear and/or delete, then save workbook, close ... Read MoreI've seen this too and the best way I've found to consistently fix it is to copy the desired range and paste to a new sheet. I am usually helping a coworker with this an ... Read More
Hi Joanna
Are you actually deleting the rows or just pressing delete on the keyboard? Normally deleting the rows and saving / reopening fixes it HI Joanna
Sorry I'm not quite understanding you (may just be me), what do you mean by resetting the last cell? Are you attempting to delete the cells you aren't using o ... Read More  
Hi, Thanks for reading my questions. May I know the userfriendly ways to input 4 different items that are the same price a, b, c, d within a certain period of time. However, the amount we order each time is different. It should have a date column, an item ... Read MoreHi,
You may create your price list first, and pickup price from it for your orders
In orders items better to select using dropdown list
Price will be calculated as ... Read MoreBest Response confirmed by tintin ting (Occasional Visitor)
 
I have 1 list of unsubscribe email ids and lots of lists of subscribers. I want to remove emails contained in Unsubscribed list from all of my Subscribed list.
Is there any formula/option/sorting/filter through which i can directly remove emails contained ... Read MoreMaybe try something like this based on the description you've given (See attached .xlsx file for reference):
Read More Best Response confirmed by CS Pooja Arora (New Contributor)
Hi,
You may add helper column to each of your sheets starting from second one, like =(COUNTIF(Sheet1!A:A,A1)>0) if in column A your ID:s (emails), filter that column on ... Read More  
I recently set up a workbook with many formulas in it and locked it owing to people that would be in touch with the spreadsheet. On reopening it today owing to a seizure I had yesterday I seemed to have forgotten password. How can I reset or retrieve the ... Read More  
I am helping someone with their compiled time sheet for their employees and would like to know how do you add the hours each worker worked for the week while also including absences in the time sheet?
I've already done up a formula to get the total hours ... Read More
I was having this same issue as well, but this seems like a quick fix. Thanks!
Also, I am experiencing a lot of inconsistencies when using the IF(B2<A2,B2+1,B2)A2 formula... Read More Hi Maya,
Please update the formula in cell P8 as follow: =IF(OR(O8="ABSENT",N8="ABSENT"),"0",IF(O8<N8,O8+1,O8)N8)
The idea of this update is to check whether cell O8 or c ... Read More
Greetings! Just add a small modification to the work you have already done, =iferror(b4a4,0) Attaching file for your reference. Thanks,
Best Response confirmed by Maya Bereaux (New Contributor)
 
I have two spreadsheets right next to each other on Excel. I am trying to add rows to the first one without consequently adding rows to the other. Is there a way to do this easily? Thanks! Hey Sarah
It sounds like you have two windows (views) open that reference the same worksheet. I think you want to insert cells not rows. Refer to the Insert Cells po ... Read More  
Like the picture a want to make a query to do it automatically this process 1. First I want to take the blue horizontal cells (PF7,PF8, S1, S4 and S6) and transpose it to vertical as I show you with orange color 2. Second, how can I do to transpose and the ... Read MoreTry looking at the steps in the attached example file.
Directions:  
Hi,
I currently have a spreadsheet where each employee has a separate worksheet, I then have a master sheet showing the overall totals for each project. What I would like to know is if it's possible to look up the data from the employee tabs and enter it ... Read MoreI think I can do it with a couple of 'helper' tables. First table is a list of worksheet names to be used in the following formula: =SUMPRODUCT(SUMIF(INDIRECT("'"&worksheet ... Read MoreBest Response confirmed by Machala Sentance (Occasional Contributor)
 
I have a simple spreadsheet in which I was trying to list "codes" for keeping track of data for a game I'm working on. (Table top dice game) I find that the codes 01P2 or 01G2 can be found and replaced with 02 or 03, etc. However, my code 01E2 etc. chan ... Read MoreHi John,
You have an option to preset the Text format on empty cells before you enter the code. Select the targeted cells, and press Ctrl+1 to open the Format Cells dialog ... Read More  
Hello all:
I have a column in Excel that contains person's title and company name separated by " at ". I tried to split with text to column with left most and right most occurrence but could not succeed as title or company name may also have at in their w ... Read MoreHi Gaurav,
The idea is here https://exceljet.net/formula/splittextwithdelimiter. However, if you have only one or no " at " you may use formulas like =IFERROR(LEFT(A... Read More  
Hi, all I'm trying to understand how the template below works to make a new one that fits my need. https://templates.office.com/enus/EmployeeattendancetrackerTM02780235 In the conditional formatting, how does the formula work so that the days off are hig ... Read MoreHi Cheng
Here is the discussion https://techcommunity.microsoft.com/t5/Excel/Employeeattendancetrackerusingexcel/mp/124045/highlight/false#M2681 if help Read More  
I'm looking for a way for a formula to multiply the value of a cell based on the count of the numbers within it.
For example, the formula now takes the dollar value in one cell X quantity of another cell (i.e, $1200.00 x 2) to produce the result in a thir ... Read MoreHi,
If I understood correctly the task is to calculate the number of digits in the text where could be from 1 to 4 digits separated by commas, like this
That could be ... Read MoreBest Response confirmed by highspeedlane (New Contributor)
 
I don't wanna lose my time with copypaste. I need to fill an excel sheet automatically from another one but the columns are different.
As you see the photos below, I need to fill the columns in first sheet with the same colour in second sheet. I used col ... Read More  
Hello all,
I have an excel file and the file has lots of sheets. Each sheets contains following information of a product. (sizes, weights, ship out date and so on) What I need to do is I want to know how much left by sizes in total. (Each size with "shipou ... Read MoreGreetings! As understood, have put in data in separate tabs, Col A (Products), Col B,C,D in Sheet 2 are the criteria's,Sheet 3 consists of data of Qty of Items ordered. If ... Read More  
I have 2 worksheets, my source has a unique value and I want to bring in >1 values in my vlookup. If that isn't possible, is there another solution? Thanks for your time. :) Greetings! Please find attached formula to source >1 values from a different sheet using VLookup. Thanks,  
Oops. Duplicate conversation. Thank you all who helped me. Hi Jaime
Hopefully I have understood right.
If you are using Office 365 then you can try this formula I've drafted into cell J2 and autofill down. It should do the trick. ... Read MoreI suggest to introduce two more columns for simplifying the formula. The formula "CHOOSE" will help. As the column H contains error message (#N/A), I don't have ideas to ... Read More  
I apologize but, I am far from any novice user of Excel. Basically what I'm trying to accomplish. Is we have multiple projects that we do. That we need to verify sewer lines. Our first attempt to do so is by accessing the sewer from the street. If we are ... Read More  
I am having trouble writing a formula to get the outcome I want.
I want column J to look at two data sources (column F and column H) and determine if they agree. If they do I want it to say "Match" and if they don't, say "No." In those columns the follow ...
Read More
In general formula works, couple of points  to check if the cell returns #N/A error you shall by ISNA() function, not by comparing with error text;  IFS checks conditio ... Read MoreBest Response confirmed by Jaime Kinslow (New Contributor)
Here you go: I had the Pass/Fail/Missing data in cell A1 and the Yes/No/NA data in cell B1
=IF(AND(A1="Passed",B1="Yes"),"Match",IF(AND(A1="Failed",B1="No"),"Match",IF(AN ... Read More  
Hi everyone!
I have a feeling I did something dumb and can't figure out what option I might have accidentally clicked off and my coffee supply isn't helping me figure it out. I have built this workbook to filter out columns of data I don't want and to run ... Read MoreHi,
That's job for Power Query  import your csv file, transform columns as necessary and land resulting table into the Excel sheet.  
Im trying to create a calendar that is for a year long, and can be changed with just entering the start date in a specific cell. I need the calendar to show the Month, Date, and Day in seperate rows. Im having trouble changing the Day and as of now have t ... Read MoreThat could be =MID("SMTWTFS",<your week day number>,1)  
Hello everyone! I just started in the VBA world, but I get stuck with this problem. Hope anyone here got the solution at hand.
I have a book whit two sheets, one it's DATA, the other is GRAPHICS. In DATA I got a list of URL of which I have to import some t... Read More  
Morning guys,
I am having trouble with a vlookup, when I use the vlookup below the date comes through as a string of numbers like so 43543, I have tried formatting the column to show date in both the output column and the input column but to no avail, coul... Read More Please help fix my date issue. If I put 52418 or 5/24/18 it shows as 43244. The only way it is recognized as a date is if I put 5.24.18. It is even formatted as a da ... Read MoreHi Mike,
If formula returns the number 43543 that is March 19, 2019 if you format that cell as date. Or it returns you the text '43543 ? You may check with ISTEXT() if ... Read More  
Good morning,
please excuse if the terms I use are not correct, Excel is not running in English, so all terms I use are translated.
I've got an Excel sheet with some data links to other Excel sheets. Let's call this sheet A. I access the other sheets (let ... Read MoreHi Konrad
I will assume that Sheet means that they are stored in a Workbook in this instance.
In my understanding, you cannot grant access to sheets BF as they are owned ... Read More  
Hi all, Greetings! As understood, pls find attached "Compare Data1.xlsx" For Criteria 1: =IF(AND(ISNUMBER($E2),$C2>=$L$6,$C2<=$L$7),"Meets Criteria 1","") For Criteria 2:=IF(AND(ISE ... Read More
Found this, perhaps it helps? https://www.ablebits.com/compareexcelfiles/index.php
Read More
