Questions tagged [worksheet-function]

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

Filter by
Sorted by
Tagged with
0
votes
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
2answers
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
0answers
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
0answers
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
0answers
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
1answer
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
4answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
2answers
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
2answers
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
0answers
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
0answers
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
2answers
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
1answer
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
1answer
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
3answers
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
2answers
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
1answer
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 ...

1
2 3 4 5
128