All Questions
138
questions with no upvoted or accepted answers
3
votes
2
answers
2k
views
TEXTJOIN() on an array incorrectly returns a blank
TL;DR: TEXTJOIN() on an array returns a blank - can't use VBA to solve the issue in an alternative way.
I've created several sheets with heavy dependence on Array functions due to limitations ...
3
votes
1
answer
750
views
Define Name for Worksheet Scope in Microsoft Excel for Mac V15.38
I am trying to create named references that have a Worksheet scope in Microsoft Excel for Mac V15.38.
I want to set up a template sheet that gets duplicated multiple times (example: a template sheet ...
2
votes
0
answers
66
views
I am unable to enter 6E029 in a cell in Excel
I am unable to enter the text from 6E000 to 6E999 in Excel.
Every time I enter this it is taking exponential form.
How can I format this to actual text?
2
votes
2
answers
102
views
Excel: Formula that supports more than 15 digits
My formula is follows:
{=TRUNC(SUM(IFERROR(LEFT(A1:A600,FIND(" ",A1:A600)-1)*10^(MATCH(RIGHT(A1:A600,2),{"KB","MB","GB","TB","PB","EB","ZB","YB","BB"},0)*3),0))/(10^(INT((LEN(SUM(IFERROR(LEFT(A1:A600,...
2
votes
1
answer
706
views
Need Auto Increment number beginning with Alphabet in Excel
Suppose I have a number ED000071, I want the next numbers to be ED000072, ED000073, ED000074.........ED999999, EE000001. Could someone please help me to write a formula for this in Excel?
2
votes
1
answer
2k
views
Auto-populate cells in Excel
As you can see in my screenshot,
I have a table called "Payment Details Form" that needs to be filled and
printed for each individual of 100 employees:
In another worksheet, I have a data table of ...
2
votes
2
answers
435
views
Creating a ranking in Excel by matching values in non-contigous cells
I have an excel file that looks like this:
A B C D E F G H I J K L M N O
1 Alice Bob Charles
2 10 35 54 9 21 71 15 43 75 98 13 ...
2
votes
1
answer
1k
views
Show MS Excel Syntax as you type Formula
I have Office 2013 on my work computer and Office 2016 on my personal computer. On my work computer in Excel 2013 when you type a formula you get the suggested syntax for formulas as you type, but on ...
1
vote
0
answers
257
views
How to make an excel sheet dynamically reference another sheet
I am trying to make an excel sheet that generates material costs for construction. My plan is to have one sheet where I can enter the material and its quantity, and another that stores the prices of ...
1
vote
1
answer
467
views
Formulas - Conditional Formatting
I need the cells from Aug to Dec to be formatted automatically based on the "plan numbers" if actual numbers are higher (green); if lower (red)- I'm trying to set it up so when data comes (...
1
vote
1
answer
505
views
COUNTIF across multiple sheets does not work with SUMPRODUCT
I have the two sheets below.
(The example table above is highly simplified for this question)
I'm trying to write a formula that adds the total occurrences of A under the year 2017 (Answer is 3).
...
1
vote
0
answers
17
views
Excel table.combine({Sheet1},{Sheet2}) erases data on an append
I have a power query combination that looks like this:
= Table.Combine({#"Sheet1", #"Sheet2"})
Now the tables combine, and most of the data appends, but some rows are missing in ...
1
vote
1
answer
298
views
How to merge two formulas in one cell
How can I merge these two formulas in one cell?
= IF(D2="TRUE",C2+C2*20%, C2)
= IF(E2="TRUE",C2+30000, C2)
I have created two dropdown menu in excel, but can't merge them ...
1
vote
1
answer
41
views
Excel: Dependent drop-down + calculations through multiple sheets (with different columns position)
What I am trying to do is doing some calculations (Main sheet) through different sheets with similar structure in the same Workbook, so once when I pick something from drop-down it should calculate ...
1
vote
4
answers
13k
views
How to combine SUBTOTAL and SUMIFS in Excel?
I am struggling to combine my SUMIFS formula with SUBTOTAL. As you can see in the picture, my SUMIFS formula (highlighted in yellow, formulate pasted as text below it) sums up Column C when values in ...