# Questions tagged [worksheet-function]

Questions about using in-cell functions/formulas in spreadsheet software.

6,368
questions

**0**

votes

**1**answer

16 views

### Excel: Is it possible to select multiple values in Drop-down list? (without VBA)

Is there any possibility to mark more values in drop-down list and to have them in one single row?
This is the way I found but still it is not inside a single row...
My DDT
And this is actually ...

**0**

votes

**1**answer

45 views

### Excel - How to “combine” columns for a row, and create duplicate rows for the values in the combined columns?

I have a table like below:
Account# Code1 Code2 Code3
123 A B C
And I would like to removed columns "Code2" and "Code3", and create new rows like below:
Account# Code
123 ...

**1**

vote

**1**answer

48 views

### Reference result from Excel Filter function to be used in drop-down

I use the Filter function in Excel and successfully get a resulting list of values:
Now I want to reference that list to be used as the content of a drop down list - something like this:
As the ...

**0**

votes

**1**answer

39 views

### Excel formula needed: counts days elapsed over varied row lengths and N/A cells

I am trying to create a formula that will tell me the number of weekdays that has elapsed between the date entered into column M and the last date to be entered into columns N-Q for each row. Ideally ...

**0**

votes

**1**answer

26 views

### Split a large excel workbook with multiple worksheets into multiple files based on a column

I have a workbook with multiple worksheets with about 200000 rows. Each worksheet has a state column, and the states are common in all worksheets. I need separate files created based on the state ...

**0**

votes

**1**answer

24 views

### How do I add the value beside the matched object?

I want to find how do I add all the value beside the matching names?
The excel picture that I am trying to solve1
At first, I tried to use Vlookup to find the value. However, it only gives 1 value. ...

**0**

votes

**0**answers

26 views

### How do I access full editing features across multiple computers for a shared excel doc? (Not co author)

I have a shared excel spreadsheet (no co-author as my work is still in the dark ages)
My issue is I can’t fully edit it on a computer other than the one I first created it on and neither can the ...

**0**

votes

**1**answer

36 views

### Check if Value comes exactly 2 times in given selections

I need to check that in the given selections, my value should be there only 2 times.
]Image showing it all?
Item1 Item2 Item3 Item4
22 24 3 15
26 21 18 19
...

**0**

votes

**2**answers

56 views

### How to group similar rows in Excel, and total the values of each group?

I have a spreadsheet with similar items in different rows. Each item has a unique value. For example:
I want to create a table that has one row for each of the similar rows (e.g., /ca/motion-type/...

**0**

votes

**0**answers

57 views

### One req to many candidates - Transposing data from one row to multiple rows based on # of positions

I am struggling with finding the right excel formula. Trying to avoid writing script in VBA as user will not be able to maintain it going forward and I found with formulas it is a bit easier. We have ...

**0**

votes

**0**answers

23 views

### FILTER Function error

I have a dataset in this format.
When I write the filter function, there is an error that the function is not valid.
=FILTER(A2:L26263,E2:E26263=A26266,"")
I trim the E column and change date ...

**0**

votes

**0**answers

19 views

### Dynamic Scatter Plot From Multiple Ranges Trouble

I have made a line chart where I can plot data in between four ranges that I can change using name managaer and the INDIRECT() function. When I try to do the same thing with a scatter plot, I get an ...

**1**

vote

**1**answer

21 views

### Asking Excel To pick the value from the first row

Lets say you have two rows in excel
S1 S2 S3 S4 S5 S6
ROW1 1 2 3 4 5 6
ROW2 7 8 9 10 11 12
I wish to have excel go through the numbers in the second row and give the name from the ...

**0**

votes

**4**answers

55 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 ...

**0**

votes

**1**answer

16 views

### How to Add rows to a table based on the number input from a single cell

I am new to VBA and while I can typically search out an answer for what I am looking for, I have not been able to find anything for the scenario I need that works.
Scenario: I have a workbook where “...

**0**

votes

**0**answers

22 views

### XML to Excel and vice-versa

First of all, I am really not quite familiar with XML data, I mean I didn't work with them. I just got a question if I can do something with them using Excel, try to automate some things.
I could ...

**1**

vote

**1**answer

64 views

### Using the WEEKNUM function in Excel to calculate week numbers on the basis of 7 day blocks, primarily for Australian Financial Years

TLDR
Is there a way to use the WEEKNUM function in Excel to simply calculate week numbers on the basis of 7 day blocks without any regard whatsoever to what weekday the first date falls on?
Context
...

**1**

vote

**1**answer

56 views

### Try to Spill Down a Dynamic Formula

Using Excel 365.
I have some text data in columns A and B like:
101r MC
101r TU
101r VV
101r VL
w257 HU
w257 QI
w257 JJ
w257 YW
315L GD
315L OO
315L NL
w257 BHU
...

**0**

votes

**1**answer

49 views

### Inputting several values, found from a VLOOKUP function, into a COUNTIF function

This question is about a single function (in a single cell to later extrapolate over a whole table) in Microsoft Excel.
I want to find a unique id on another page (amongst other id's in the first ...

**0**

votes

**1**answer

121 views

### EXCEL : Hyperlinked dynamic index

I found below formula to generate a dynamic index in spreadsheet.
=IFERROR(INDEX(Sheet!$A:$A,SMALL(IF(ISTEXT(Sheet!$A:$A),ROW(Sheet!$A:$A),""),ROW(Sheet!A1))),"")
Is there a way i could add ...

**0**

votes

**0**answers

32 views

### Excel: how can I find the closest date in 3 distinct lists, where all 3 lists have the same date present

I'm trying to automate a reporting process where by we summarize data from 3 separate reports for the most recent Friday. Sometimes for whatever reason one or more of these 3 reports won't run; at ...

**0**

votes

**1**answer

23 views

### Alternative to TEXTJOIN

Currently working on building an Excel form within Microsoft Teams, but it will eventually need to be downloaded to a user computer to be filled out. TEXTJOIN works online within Microsoft Teams, but ...

**0**

votes

**1**answer

26 views

### Understanding the logic of an Excel INDEX formula which incorporates the MATCH function

I'm doing an online Excel course and I'm trying to wrap my head around the internal logic of the formulas I'm being taught. So far, so good, for the most part. However, I'm trying to figure out the ...

**0**

votes

**1**answer

52 views

### Excel: Frequency of articles ordered together

I am looking for help, with an analysis I would like to perform in Excel.
Available data:
Sales history including the Order number & Article number.
Example of the available data:
Order number ...

**1**

vote

**2**answers

36 views

### Excel SUMIF - If condition is met then SUM two cells in same row

I have a table like this:
TABLE A
NAME VALUE 1 VALUE 2 RESULT
Daniel 1 4
John 3 5
Daniel 5 8
I’d like to run a SUMIF within the RESULT column, and if a ...

**0**

votes

**1**answer

29 views

### Counting rows in a sheet which match conditions across columns

Let's say I've got an Excel sheet, the first few rows of which looks like this:
In another sheet in this notebook, I want to count the number of instances where pep is YES or NO. This is easily done ...

**-1**

votes

**2**answers

44 views

### How can I unify these two formulas in the destination table?

I have a source table with tens of columns and tens of thousands of rows. I have a destination table with half a dozen rows. I want to count how many people are on a project and only that project. ...

**0**

votes

**1**answer

40 views

### Enter time duration in Excel in mm ss format (without hours and colons)

Enter time date in excel in mm:ss format(just buy inputting numbers without the colon : and 0: hours in front)
Hi, I'm working on a project and need to calculate the number of minutes and seconds ...

**0**

votes

**1**answer

35 views

### Finding missing data in Excel without having xlookup function

I know how to do this using the XLOOKUP function, but if I don't have access to that function how would this be done?
I have columns with names and ID #'s. I have created another column with a few ...

**0**

votes

**1**answer

28 views

### Excel - #VALUE! error message

I type $1000 in cell D31. I type =D31+50 in cell D32 because I want D32 to show $1050. I then want to copy that formula into the next 100 cells in row D so that the value in each cell is $50 higher ...

**0**

votes

**1**answer

60 views

### NOW() within IF Formula stopped working

One of the formulas in my Excel tracker stopped working yesterday. The formula is:
=IF(J2="Done",IF(L2<>"",L2,NOW()),"")
I want to capture the time I finished a task in L2 when I choose "Done" ...

**0**

votes

**1**answer

31 views

### Nested IF not working properly in worksheet; How to fix?

I'm trying to do nested IF between two variables in a worksheet, but I'm not getting the desired results. Is it because the cells I'm trying to compare are in the formula?
The result I want is:
When ...

**0**

votes

**0**answers

19 views

### When referencing a cell in another (unopen) workbook, is it possible to have the file path used in the formula from another cell?

I have an excel file with a bunch references to various other files which share the same path. However, I need this path to be able update for all of them without manually changing each one.
So ...

**1**

vote

**2**answers

65 views

### Excel MEDIAN formula for values based on date

I got stuck with a formula again if you can help :)
I need to find the MEDIAN for a range of dates that have a criteria, for example:
code date time
5X 08 Feb 5115
5P 08 Feb 5225
...

**1**

vote

**2**answers

47 views

### Excel Pivot Table Formula

I have an excel sheet that contains data like that shown below. I have the pivot table that shows departmental time and percent and an external value of total cost.
How do I create a formula that ...

**0**

votes

**0**answers

19 views

### How to reference a value from the n th in worksheet and move to the m th row in different worksheet

I have a group of data in worksheet 1 which is under a similar category every 4 rows 9 (i.e category A)
I need a copy/Reference of the group/blocks data in a worksheet which as a pattern for every 25 ...

**0**

votes

**0**answers

19 views

### How do you refer to a specific column in a pivot table in a formula outside of the pivot table to find various summary stats for that column?

So I have a pivot table with different columns of numerical data. If I want to find summary statistics of one of the columns (i.e. average, standard deviation, etc.), how would I refer to it in a ...

**0**

votes

**2**answers

48 views

### How to extract a number in between square brackets to another column in Excel

I hope someone out there can assist me with my query below.
I need to extract data from one column in excel to another, I am trying to get the numbers in the square brackets [] on the list below to ...

**0**

votes

**1**answer

36 views

### Excel vba method, “source.copy destination” results in something mysterious

This is the macro:
Sub dddExport()
Dim sFile As String
Dim rSource As Range
Dim rDest As Range
Set rSource = ThisWorkbook.ActiveSheet.Range("a1")
sFile = "C:\dann\dan.xlsx" ''''...

**0**

votes

**1**answer

29 views

### IF (AND) Statement

I'm new doing IF & And statements and am wondering why this line doesn't work?
=IF(OR(AND(D15<=2, P15>= TIMEVALUE ("0:03:01")), AND(D15>=3, P15>= TIMEVALUE ("0:20:01")), "Over 3 mins", "Over ...

**1**

vote

**3**answers

61 views

### Excel MATCH function seems to be broken. Issue description and investigation details. Any explanation or solution to this?

I'm a fairly intensive and experienced Excel user, used to work with INDEX and MATCH functions and with strings, so I am also used to the typical issues with errors with the MATCH function due to ...

**0**

votes

**2**answers

119 views

### Populate an array of table[column] names from a column of another table and return the max number

I have a table with a list of manufacturers (Manufacturer 1, Manufacturer 2, Manufacturer 3). Then for each manufacturer listed in that table there is a corresponding table that list all the items ...

**0**

votes

**1**answer

38 views

### Excel rank values but ignore some on condition

I want to add a rank to the following columns:
The rank value should be based on col2, but only if a few conditions are met.
value of col1 must be >= 0.1
value in col2 must be numeric
Given ...

**1**

vote

**1**answer

18 views

### Refer to Table Column by Name rather than Table Column Address

I have 2 sheets.
Sheet 1 is a formatted as a table and is where the user pastes data in.
Sheet 2 takes part of that table data and prepares it for pivot table analysis.
The problem is that the input ...

**0**

votes

**1**answer

35 views

### Excel 2016 Chart > Date and Text on the same Axis

I received an interesting question yesterday during at work. Is it possible to have Date and Text values on the same Axis in Excel Chart, but that this "text" is actually formatted or somehow ...

**0**

votes

**0**answers

27 views

### Excel - Formula in shape recalculates on open

I have a shape in Excel which is assigned to macro which resets filters. When filters are set, there's a formula in the shape which makes it say: "Filter set". The formula goes like "=G1", not more. ...

**0**

votes

**0**answers

28 views

### How can I add data in a sheet in one sheet and the entire row is added to others?

I'm actually trying to do the following:
1. Sort a sheet automatically when there is a new entry (which the script already achieves, script is shown below) lets call this Master sheet. The master ...

**0**

votes

**1**answer

23 views

### Copy a cell contents inc background from one sheet to another

I have a master records sheet with a list of names.
These names are all copied from absolute values in 10 other individual group sheets using formulae like;
='C3'!A19
Sheet names are;
Master Sheet, ...

**0**

votes

**0**answers

17 views

### How to move specific cells in a row from one worksheet to another based on Month in Month Due column

I have been googling for a while trying to find a solution for how to copy specific cells in a row from one sheet to another. I have only been finding ways to copy entire rows. I have a workbook and ...

**-3**

votes

**1**answer

61 views

### Finding the year in another table in Excel when the value isn't the same

I have two tables. I would like to get the data from Blue to the Orange table with following steps:
The first column of Orange table has both positive and negative values as percentage, and I need to ...