The Excel-2019 tag is used for referencing the spreadsheet component of the Microsoft Office 2019 suite released 1 Oct 2018 (Commercial preview released 27 April 2018). The version independent Tag is "excel". If it is about an Excel formula or worksheet function, then tag it "worksheet-function". If the question pertains to VBA code, the "vba" tag should be used in addition to this one.
Questions tagged [excel-2019]
183 questions
0
votes
3 answers
How to hide 0's when there is no data to calculate without using custom number formats
Below is part of a financial worksheet to calculate profit and loss.
Currently C19 shows £ 0.00 as there is no data in the next to calculate an answer.
I want cell C19 to return blank until such a point there is data in the next sheet to calculate…
Insider
- 95
- 12
0
votes
1 answer
Excel formula - column changes based on result of another column
I would like to know how to change the column of a formula based on the result of another formula:
I have two columns:
Column Letter
Transaction Count
A
=countif(sheet1!$A$1:$$5, "Apple")
B
=countif(sheet1!$B$1:$B$5,…
ageee9
- 1
0
votes
1 answer
Code To Add INDEX-MATCH Dynamic Range Formula To Workbook Named Ranges?
I have the following named ranges (some of which are INDEX/MATCH dynamic ranges) and their accompanying formulas that are present in my workbook's Name Manager:
Q1_BusinessWorkd_ID ='Q1 Data'!$C$35:INDEX('Q1 Data'!$G$35:$G$1048576,MATCH("zzzzz",'Q1…
Bill Vallance
- 471
- 3
- 19
0
votes
0 answers
Combining MID and LEFT Functions in Excel 2019
I currently have data in a cell/column of a course name/number ex.: 2019FA Microbiology (BIO-214-S104A). I am currently using a mid function to extract the data within the parenthesis to narrow it down to BIO-214-S104A in a separate column and using…
Timothy Bryant
- 1
- 1
0
votes
0 answers
Is there a way to calculate a ratio using SUMIFs?
I am trying to get cell I13 on sheet MONTHLY STATS - SPREADS to populate a 0 if there have been no L's to count between the dates of 1 Oct 21 and 31 Oct 21 on sheet SPREADS LOG.
But I only want this 0 to populate if there is data present in cell…
Insider
- 95
- 12
0
votes
2 answers
How can I use COUNTIF to count specific text between a date range?
I am trying to make cell G5 SUM or COUNTIF (not sure which is better) the number of OPEN's and CLOSED's in column A on sheet SPREADS LOG between the date of 1 June 21 and 30 June 21.
However, if there is no OPEN or CLOSED between this date range I…
Insider
- 95
- 12
0
votes
0 answers
How can i use SUMIF to return no value instead of 0
I am trying to make this formula shown below return a blank cell instead of 0 if there is no data to be counted thus yet.
For awareness this worksheet is calculating average profit per day in each different month, obviously, as we are not yet in…
Insider
- 95
- 12
0
votes
1 answer
How to use SUMIF to generate a blank if criteria is not met
I am trying to work out average profit per day. The below formula works, however, if there is not data for a month then I would like it to return a blank, NOT a 0 as it is currently.
Current formula :
=SUMIFS('SHARES LOG'!L:L,'SHARES…
Insider
- 95
- 12
0
votes
0 answers
How can I divide by a number if the criteria meets a date range?
I am trying to work out the average profit or loss per day in any given month.
However my formula is only averaging number between the days currently present, I know that somehow I need the total amount (at any point so far) during the month to be…
Insider
- 95
- 12
0
votes
1 answer
How to resolve this problem with excel-filter that it doesn't take me to the top cell after applying?
I applied a filter on a range of cells after selecting them in MS Excel. Say the range has 100 rows. After selecting the 100 rows and applying the filter, I am supposed to be taken to the top row of the selection automatically. right? But my excel…
Gowtham
- 3
- 3
0
votes
1 answer
How can i use COUNTIFS only when there is data present in an adjacent cell?
I am trying to get G19 in sheet STATISTICS to only populate with data calculated from the COUNTIFS formula shown below when F19 of sheet STATISTICS has data within. If there is no data calculated for F19(i.e. an empty cell returned) (separate…
Insider
- 95
- 12
0
votes
0 answers
Try to modify code to loop through list of files in folder to remove duplicates openpyxl
I did go through some posts here to remove duplicates from list of files in folder using Python openpyxl. Did not find suitable one to my needs. Here is the script which I have and want to modify below script which can loop through list of files in…
kumar
- 1
- 2
0
votes
1 answer
VBA for filtering pivot table using dynamic array
I have the VB code below
Sub CountWordFrequencies()
Dim InputSheet As Worksheet
Dim WordListSheet As Worksheet
Dim PuncChars As Variant, x As Variant
Dim i As Long, r As Long, b As Long
Dim txt As String
Dim wordCnt As Long
Dim AllWords As Range
Dim…
Long N
- 21
- 5
0
votes
1 answer
Microsoft Excel keeps repairing my .xlsm file for no apparent reason and eliminates data validations on a sheet
I recently created an automated Excel utility (using Microsoft Office 2019), in which I've extensively used data validations, VBA code, named ranges and formatting. It was working well until one day I received an Excel prompt message that…
Nik L
- 39
- 1
- 7
0
votes
1 answer
Excel Hide/unhide textbox based on Slicer selection
I'm trying to hide/unhide multiple text boxes depending on what is selected on a pivot table slicer;
IF UK is selected then show TextBox 21 but hide TextBox 22 and TextBox 23
IF DE is selected then show TextBox 22 but hide TextBox 21 and TextBox…
