All Questions
58
questions
0
votes
2
answers
170
views
Can an Excel Filter function base its include on a whole row?
I have a Table from which I pull a list of unique values, then I want to pull all their keys and merge them using textjoin. But the Filter function the criteria to be compared against a single value, ...
0
votes
0
answers
51
views
Identifying "bad" data combinations in Excel 365
I have a spreadsheet of employee data (time charged to projects and customers) that is tens of thousands of rows long. Each month, the file is updated to include the most recent month data. Each row ...
0
votes
0
answers
20
views
Dynamic Dropdown, MS Excel, Use on Multiple Rows (MS365)
Goal
Using Excel table ("Claims") to track claim statuses, have dropdown values in certain columns (in Columns C, D, F, G) in which the dropdown values for Columns D, F and G are Dynamically ...
0
votes
2
answers
185
views
Calculate when volume counts are cleared based on daily amount received and daily amount processed
I have this data table containing the number of returns per day (Ret Volume) and the number of returns that have been processed per day (Volume Cleared).
What I'm after is the date displayed on the ...
0
votes
0
answers
236
views
How to copy the format of locked cells in Excel?
after the latest update of the Microsoft 365 package to the version 16.0.16227.20202 (32-bit), I am no longer able to copy paste the format from and to locked cells. In the previous version (16.0....
0
votes
1
answer
60
views
is it possible to logical AND two values from two different columns in COUNTIFS?
I have the following formula:
=COUNTIFS(Transactions2023[Result], "Win", Transactions2023[League], "NBA", Transactions2023[Date], "<=" & "4/11/2023")
I ...
0
votes
0
answers
289
views
Why doesn't my Excel 365 does not cover the "LET()" worksheet function?
My Excel account information shows me I'm working with Office 365:
However, the LET() worksheet function is not covered, as you can see:
What do I need to do in order to get this function to work? (...
0
votes
2
answers
130
views
How do I convert a custom function to handle spill arrays (excel)
I need a function that produces a list of sheet names, I already have one that can turn sheet numbers into sheet names but it doesnt handle arrays
The function I would like to convert is
Function ...
0
votes
1
answer
63
views
IF Function Not Populating Correct Data in Excel 365
I am completely stumped as to why this formula is not working properly. To provide backstory, I am trying to randomize a DMS grid coordinate, for example: 2339**01**N05005**51**W. I only want to ...
0
votes
2
answers
3k
views
How to drag a cell reference formula down a column, but move the cell reference across a row
I am trying to drag a cell reference formula down a column, but have the row value increase as the formula is dragged down.
For instance, in the shot below, cell B3 has the formula "=I3". I ...
1
vote
1
answer
612
views
Getting unique items from comma separated lists in Excel
I have the following setup (which is shared by a number of users through office 365 and changed frequently):
I need to collect all unique items from a number of comma-separated lists in a range and ...
0
votes
2
answers
73
views
Excel formula gives unexpected result
Each year I create a workbook within which there are several worksheets corresponding to each month of the year. I copy and paste the same table with formulas into each new month as the year ...
0
votes
1
answer
5k
views
Use FILTER function to "Include" match against an Array and not one value if possible
I'm currently trying to filter some data. I have two sheets, one with the users name, e-mail and their function within the company and another with the same e-mail and then which mailboxes they have ...
0
votes
0
answers
467
views
Excel Data Validation: If any cell in this row isn't blank there must be a value in this cell
I'm trying to set up data validation in a cell so that if any cell in a range in that row has a non-blank cell, the cell with the validation must have a value from its drop-down list. As data is ...
1
vote
1
answer
378
views
=DATEVALUE() behaving different in Excel 365 online and Excel 365 local
I noticed that =DATEVALUE() behaves different in the online version of Excel 365 (i.e. it does not work) than in the local version of Excel 365 on my PC. Below are two screenshots of the two Excel ...
0
votes
1
answer
763
views
Create a Table that Filters Data and Sorts Unique Values
I am trying to generate a list of unique values in excel based on a unique list of items.
Basically, I need to create a list that Sorts and Assigns a Part Name Based on Width and Length. Then counts ...
0
votes
1
answer
349
views
Sum a range of cells in Excel according to a range (criteria)
cordial greetings
I am trying to obtain the sum of the number of males taking into account an age range that I set previously.
For example, I want to know how many males, between the ages of 3 and 12,...
0
votes
2
answers
1k
views
Excel wildcards not finding the matches they should in VLOOKUP function
I have multiple product families I'm working with, and each family has multiple products within. Some families have more products than others, all product names are unique. On one sheet named "...
0
votes
1
answer
382
views
Extracting all matching (or close to) values between given START n-th and END n-th values / using Power Query
Consider having two tables. Table1(T1) has Date/Time and Pressure value columns and Table2(T2) has also Date/Time and Info values columns.
T1 is showing pressure change through time, for instance: 21....
0
votes
1
answer
461
views
Excel - How to reference a cell, within a formula, located immediatley to the right of a value returned by a MIN Function
New to the community. I am attempting to use within a formula, a value within one of two possible cells that are located immediately to the right of two cells, the contents of one of which will be ...
0
votes
0
answers
68
views
In excel, how to select every column of a table with a specific header
I would like to select only a repetitive set of column from a structured table to apply data validation or conditional formatting.
I tried to create named range with a formula but without success. ...
0
votes
1
answer
88
views
Is there a way to manipulate multiple data values in one cell without splitting the values apart?
I have multiple "Mixes" that I want to create a cost for. Currently each mix is shown in component form in a cell for clarity, the number of units is in a separate column within the table ...
0
votes
1
answer
41
views
Automated Table Of Contents - Adding Details from Last row of related page
Good Day,
Does anyone have recommendations on how to reference a cell with the worksheet name in it and add it to a formula that determines the last row with data in it on that worksheet.
I've used ...
-1
votes
3
answers
98
views
order two columns off another two columns
I would like to order two columns off another two columns.
Consider the image:
I would like to order column C and D based off column J and K. For example, the order should go something like John Brew,...
0
votes
1
answer
275
views
How to multiply every combination of elements of two vectors and output in a third vector?
I need a function or combination of functions which would be able to perform the following operation:
Given two vectors containing booleans (might be integers as well):
V1 = {0;1}
V2 = {1;0;1}
I want ...
0
votes
3
answers
1k
views
How to increment a cell reference by one inside a hyperlink equation to apply to a whole column?
So I have a column in (Sheet1) that I want to lead to the same cell number in another sheet (Sheet2) when I click each cell.
When I tried to use the hyperlink function, I typed the first equation as ...
0
votes
2
answers
70
views
Is there a formula to count dates as the approach?
I created a sheet to track projects that my team is working on. it contains it has a few columns which are conditionally formatted. One of those columns is for the date equipment needs to ship out to ...
0
votes
2
answers
2k
views
Formula to extract only unique values from horizontal range into a single cell, separated by comma
I have a spreadsheet where users are asked to place a value in their allocated column across many rows.
There are a range of other columns used by an Admin area and one of these is supposed to list ...
1
vote
2
answers
304
views
Excel 365: Array formula based on value above (compounding)
I am trying to perform an operation in a cell based on the value of the cell above it. Doing this in the "normal" Excel way is easy enough. However, I am playing with the array functionality ...
0
votes
1
answer
161
views
Confusion with data validation (list)
Hello cordial greetings
I am a bit confused, I need to perform a data validation in cell C7, so that the days correspond to the month set in cell C6 and the year found in cell C8. The data is recorded ...
1
vote
2
answers
53
views
Counting elements based on three criteria located in two columns
I wanted to ask you if there is a way to perform a count by applying formulas directly, without having to resort to the filter option, through the COUNTIF or COUNTIFS function.
From two criteria found ...
0
votes
1
answer
55
views
Is there an excel formula to calculate all "like" entries from two criteria across two cells?
I'm putting together a personal budget and have added a tab to track daily expenses. I've allocated a "code" against a type of expenditure, and then a "sub-code" for more specific ...
0
votes
1
answer
9k
views
XLOOKUP Function Disappeared
I have been using xlookup for several months across several files just fine but today it stopped working across all Excel files. I invested a lot of effort moving from vlookup to xlookup and now it's ...
1
vote
1
answer
921
views
How to return the value of one cell based on the max of multiple ranges of other cells in excel?
I am a bit new to excel and have a spreadsheet where I am trying to use the MAX of a range of cells in order to return the name of the row that that MAX belongs to. So, I have a cell returning the MAX ...
1
vote
2
answers
72
views
Please help me stop Excel 365 enterprise from changing my text formatted cells into formulas
I have a spreadsheet where I need to use strings that contain a couple of dash/ minus ( - ) signs in cells, but it will not stop truing to recognize them as formulas! I have already formatted the ...
0
votes
2
answers
135
views
VBA or Function to see if a serial number has been used
I have a workbook that contains the issued serial numbers for components. Column A has the serial number and Column B has the part number. I want to have a verification in column C if the serial ...
0
votes
2
answers
7k
views
Cell referencing change with filter
I am quite new to Excel and just figuring out how to do things. I have a question about cell referencing between sheets.
Say I have on Sheet1 this table:
DIMENSION VALUE
X 0
Y 1
Z ...
0
votes
2
answers
268
views
Filter columns based on starting string, read number from filtered cells and perform arithmetic operation to generate summary of two rows in Excel
I am having a Project and resource allocation report. I need help in generating the formula to display summary on the right section of the report.
Here're the specifications of my report:
Column A ...
0
votes
3
answers
432
views
Excel - Filter rows based on starting string, read number from filtered rows and perform arithmetic operation to generate column summary
I am having a Project and resource allocation report. I need help in generating the formula to display summary in the bottom section of my report.
Here're the specifications of my report:
Column A ...
1
vote
3
answers
309
views
Microsoft Excel: Use OR Logic and multiple ranges with COUNTIF
I am analysing some form responses and want to count if someone has put a specific answer in a range of different boxes. I have attached an example to explain the logic.
When filling out address ...
0
votes
1
answer
125
views
Need Help for Inventory Tracking System with XLOOKUP
Sorry for not wording the question better, but I'm a bit confused how to put it. Please feel free to edit or suggest a better title!
I am making an inventory system for a collection of rental ...
0
votes
1
answer
164
views
Excel: How to change column to average based upon unique identifier
I have a situation where for a certain daily date range I have 5 different columns of data which are for 5 different pieces of equipment. I'm trying to set up a calculator where I can select the piece ...
0
votes
3
answers
165
views
IFS Formula With Exact Digits
I am trying to create an "IFS" formula where if the number of a cell has 2 digits, then x, if 3 digits, then y, etc. The numbers are coming from another table with VLOOKUP.
So, if the ...
0
votes
1
answer
50
views
Look for Date values in embedded in strings
I have a value in a cell that might have a 2-digit month and 2-digit year value embedded in it, and I need a function that would return TRUE if that is found.
Sample input data: sCa!1220LB6qHZbCnsEE ...
1
vote
1
answer
2k
views
Filter function with IF statement in 1st parameter to perform fill down multiplication OR division
Link to my previous related question for more background if needed: Transpose unique rows to columns while IF statement conditions are met but do not consolidate
My table headers start on row 20 ...
0
votes
0
answers
2k
views
Auto-populate a cell based on text input of one cell and the presence of text in another cell
I'm trying to reduce the amount of manual input on a Drawing Log I'm presently creating.
Basically, I've got 5 columns: Drawing No., Client, Project, Date, and Description (in that order, though Date ...
0
votes
1
answer
511
views
Want the same cell to either display the value based on a formula or else allow user input, depending on the radio button chosen
I have cell (C17) whose value should depend on the choices made via radio buttons. One button is called 'defaults' and other button is 'custom'. The thing is, when I select the 'custom' button, I want ...
0
votes
1
answer
6k
views
Extract multiple columns with single dynamic array formula
In Excel 365, how can I select non-contiguous columns from a range using a single dynamic array formula? Consider data in columns A:E as follows:
Entering =XLOOKUP(G1,A1:E1,A2:E11) into cell G2 ...
1
vote
1
answer
232
views
formulas in another column that reference spill range not updating when sheet is recalculated
I have a a column that is a spill range that uses the filter and sort_by formulas. This spill range is dynamic and I have an IF statement that references the spill range but upon recalculation of the ...
1
vote
0
answers
64
views
Excel - Select range depending on cell
I want to select a row range, which I've been doing manually until now. However as data grows, I've tried to select the row according to the relative worksheet, but don't know how to. Here's an ...