All Questions
50
questions
0
votes
0
answers
135
views
Excel, reference header row instead of column in xlookup formula?
I need to make an Excel spreadsheet template that merges customer data from two other Excel files that are downloaded from a vendor application each morning. The files have a customer ID number for a ...
- 1
0
votes
1
answer
541
views
Creating a VSTACK'd array from a changing list of spilled references
I am working on a project that requires a large 'pool' of data to pull from and sort from. To keep the workbook efficient, the data is kept in sets of SPILL's, as pulling a raw selected range for the ...
0
votes
1
answer
297
views
Dynamic Array Subtotal that changes its column sizes without using VBA or volatile functions
I need to make a dynamic array subtotal that changes its column size by another dynamic array. The example sheet, challenge, conditions, and attempts I've made are below.
[Example Sheet]
Challenge:
...
- 3
0
votes
1
answer
440
views
Dynamically reference table row depending on cell input
I've found a couple of questions that touch on what I want to do, but not quite.
I have a table, say Table1
Segment
tot_bad_1at3
tot_bad_2at6
tot_bad_3at6
tot_bad_4at6
A
10
25
22
15
B
1
11
9
4
C
3
...
- 115
4
votes
1
answer
18k
views
How to change a cells color depending on the value of the cell above it/before it for hundreds of lines
I have hundreds of lines with varying values (currency) and want the cell to change color depending on the value above it eg. Greater than = Green, Less than = Red = Equal to = Yellow. ie:
A1 is $100
...
- 43
2
votes
1
answer
66
views
Proper Syntax for AND OR NOT Excel Formulas
I was practicing some basic formulas in excel. The last 3 I worked on are as follows:
=IF((OR(C4="Hourly",C4="Contract")),"Yes","No")
=IF((AND(B4=&...
- 23
0
votes
1
answer
1k
views
Excel summary page that auto populates from sheets
I have a problem with excel that may not be possible. I have a workbook that contains a list of sheets with various projects e.g. 'project 1' is a tab, 'project 2' is a tab etc. I now need to ...
- 1
2
votes
0
answers
305
views
Excel - Lambda's helper MAP() function not valid
Ever since 2021 I've had acces to the newer lambda's helper functions among which was the MAP() function. All other helpers like BYROW(), BYCOL(), SCAN() and REDUCE() amongst others are working fine. ...
- 2,237
0
votes
0
answers
70
views
How to Insert an autofilter if cell in row range contains value
I'm trying to create a function that inserts a filter within a cell range if a cell contains a value
Here is my code:
Sub FilterFunc()
Dim i As Long, lastCol As Long
Dim rng As Range, cell As ...
0
votes
1
answer
28
views
In excel, how do I fill in a column with a distrubution key when only one value is known? Calculate heating usage for a whole year based on one month
I need to figure out how I can fill a column of twelve know percentages (each month) based on the usage of one month. We have a certain distribution key (january is 19% of the total, february is 14%, ...
- 1
0
votes
1
answer
260
views
How do I find the cell value in workbook sheet1 using a created address in the second sheet
I have an Excel (latest Windows 10 Office 365 version) workbook with 2 sheets.
Sheet "Roster"has a column with last names.
Sheet "CheckStatus" has a column with last names and the ...
0
votes
1
answer
54
views
Excel Degree formula based on a right angle tringle
I have a formula we use at work to calculate degree for our metal, right now we use a calculator to do this but I feel like if we could just plug the numbers into excel and have it do the hard work ...
0
votes
1
answer
411
views
Excel formula to add variable number of lines
I have many large excel files that I need an easy way to combine daily totals for each person (a pivot table is not going to work for me on this one). My example is one of the smaller files, and it ...
- 1
0
votes
1
answer
38
views
Search array formula returns too high a value
I'm trying to count the number of times a certain due date is missed in a summary sheet on my workbook. On the parent sheet when something is overdue we label it as "LT". Something like this:...
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
1
answer
349
views
Merging Two Tables Including INDIRECT Function in Excel and Displaying New Table Data
Dynamic Variables Explained
Data is pulled at the end of each month and is stored in the format of YYYY.MM.DD -- Monthly Data Obtained.xlsx and Excel calculated the date range.
=TEXT(EOMONTH((EOMONTH(...
0
votes
0
answers
42
views
Count of Unique Records with criteria from other Column
Good Day,
I have a schedule people populate where they are on tasks. There is a due date and date completed. Some of these tasks are there multiple times as they are done over several days. I'm trying ...
- 672
0
votes
2
answers
160
views
Excel- Add sequential names to column based off of named range but skip names if duplicate name exists in adjacent column
I am creating a simple schedule for our engineering rotation. Some background information, our team currently has 6 engineers that must go on a weekly 5 am rotation. There is also a 2nd shift rotation ...
11
votes
4
answers
8k
views
Spurious 0.0000000000001 added to formula result
My wife and I have a spreadsheet to work out our finances at the beginning of each month and this month has a strange figure in cell F9.
The formula in cell F9 is ="+ £"&-'Sue''s ...
- 1,490
0
votes
1
answer
217
views
Get cells from another sheet based on criteria
I'm trying to build a simple helper to control my expenses.
One sheet has a list of all financial movements (income and payments), with these details:
Date
Amount
Reason
02/02/21
-1000.00
Rent
02/...
- 285
0
votes
0
answers
2k
views
EXCEL: Can I reference text within a cell, for a file path, in a function?
I have the following VBA module in Excel which allows me to do check if a file exists in a given folder:
Function FileExists(FilePath As String) As Boolean
'Declare variables
Dim FileName As ...
- 1
1
vote
2
answers
2k
views
Formulas `A1:A` and `A1:Z` give invalid name error in Excel?
Selecting a range of definite size (e.g. A1:A2) works just fine, but selecting a range to the last row does not. If relevant, I am using Office 365.
- 11
0
votes
1
answer
74
views
How to delete a column of one sheet based on value in other sheet by ignoring N/A value?
I am working with an excel file where in Sheet1, I need to compare column C and Column E. If in any row of sheet 1, Column C value> Column E value, then I need to delete certain column in sheet 2.
...
- 5
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 ...
- 45
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 ...
- 1
0
votes
1
answer
229
views
Excel unstacking data into multiple columns
I am struggling with unstacking, there are many easy Power Query and Pivot solutions , however i would like to have the solution in formula's so people understand the power of formulas.
Marketing, PR, ...
- 1
5
votes
2
answers
1k
views
Excel - Live filter of list based on another column?
In Excel, I'm looking to get a breakdown of the values in one column (IDs) by the values in another (status).
Input
| ID | Status |
|----|----------|
| 1 | Complete |
| 2 | Active |
| 3 | New ...
- 6,525
0
votes
2
answers
64
views
Excel CF: Matching (2) columns of text that begin the same, end different
Looking for assistance with the following:
A & B are a list of companies. The goal is to highlight in B companies that match A
Conditional Formatting: =MATCH(LEFT(A2,8)&"*", $B$2:$B$...
- 3
3
votes
2
answers
4k
views
How to sum non numbers by number value
My question was nearly answered by How do I assign a number value to a non-numerical value in Excel
We use a RAG status (red, amber green), and I'd like to average a group of these values.
EG, if ...
- 1,943
0
votes
1
answer
56
views
Need to make restriction in the VBA function
I want the below function to pick data from Row till Column H, however this function is picking details up to the last used cell.
Below are the VBA code I'm using, help me to fix this.
Function ...
- 1
1
vote
1
answer
1k
views
Excel column auto-populates in newly inserted rows
Hopefully this makes sense.
In Excel, I have a table with 12 columns. Column A is a file number hyperlink that takes you directly to the specified job folder on our server. When I insert new rows ...
- 11
0
votes
1
answer
112
views
Re-order data in a large excel file
I need to re-order data in a large excel file. Please suggest how can I get the required output.
2
votes
1
answer
310
views
How would you count the number of matches between a comma-separated list and a reference column in Excel?
I have a set of numbers separated by commas. Each set represents a series of unique numbers that relate to a project. How can I write a formula to search a reference sheet for any number of the items ...
- 21
3
votes
0
answers
49
views
Changes to User Defined Functions in Office 365 Subscription based
I've seen issues a couple of times when using User Defined Functions with Excel from subscription-based Office 365.
When a workbook is opened with these formulas, they are automatically updated. ...
0
votes
1
answer
271
views
"Too many arguments" error message in SUMPRODUCT
I'm somewhat newer at this, so I apologize if this is a dumb question.
I want to count each name I have listed in Column A once while ignoring blank spaces and excluding two different text values ("...
- 1
0
votes
0
answers
164
views
How to export excel file to csv file without the semicolon outside the inserted values?
I have inserted values into excel file in "order_status" sheet. These values are shown in below picture:
In this sheet I added data validations like: dynamic drop down list, checking if in cell is ...
- 113
0
votes
1
answer
48
views
After changing text in a cell, it fails to evaluate last step of a formula
I'm facing a really odd and annoying issue with formulas that I'm not succeeding in fix it.
It happens the following: I received an Excel .xlsx via Outlook (but I also tried using USB).
When I open ...
0
votes
0
answers
39
views
Copying Data into another sheet when a condition is meet
Does anyone have any idea of a solution or know if what i would like to happen is even possible. I am a complete computer novice and know nothing about ExcelVB, if that's the path to take.
I've a ...
- 1
1
vote
0
answers
44
views
Last filled row Function behaves strangely based on column it's in
Function is acting very strange depending on column it's in.
Data start at row 7 (headers in row 6)
Column A has item name B Max Qty. C and D have simple functions and everything afterwards is the ...
- 11
1
vote
1
answer
268
views
autofill a reference of a column to a row in steps
I have the formula =IF(Jan!$L6="w",Jan!$L6,"") in the cell A2.
in the cell right next (B2) to it I'd like to have the formula 3 rows further down so
=IF(Jan!$L9="w",Jan!$L9,"") and then in C2 ...
- 115
0
votes
1
answer
1k
views
Excel - How can I probe all the precedents of a given cell?
I'm a relatively naive excel user who's been dropped in the middle of an ocean of cells. I've been tasked with coding the value of one cell in a workbook into python. Of course, this cell has multiple ...
- 101
-2
votes
1
answer
102
views
How to remove duplicated entries, and only keeping one entry per user per course
Needed help here!
Here, for example, the title (the title of a course), required date (the date they required to retake the course), and the completion date (the completion dates that a employee ...
0
votes
1
answer
300
views
How to specify a column range according to a formula in Excel?
I'm trying to calculate a formula based on a column of data, and I want to choose the column based on a number in an adjacent cell. Here's a simplified example of what I want to do:
Say I was trying ...
- 645
0
votes
0
answers
58
views
In Excel, how can I get use built in functions and/or write a VBA function to result in a picture being displayed as the formula result?
See this question, which got little attention.
Imagine that I have a workbook with images of all of the playing cards somewhere, like a lookup list. I want to randomly shuffle the deck (...
- 25
0
votes
1
answer
227
views
Linking formula and standard cells across columns in a table?
I have a workbook W1 which contains a table W1T1. One column (by entering the letter 'x') signals, that this row W1T1Rx has to be copied into another workbook W2.
W2 contains a table W2T1 that has ...
- 101
1
vote
2
answers
1k
views
Excel how to deactivate "Most Recently used function" suggestion from drop down list
I am currently using Office 365 on Mac.
For the "Drop-down Function-suggestion list", Excel have started suggesting the "most recently used" function, as opposed to JUST suggesting it in alphabetical ...
1
vote
0
answers
70
views
How to have Excel return column and row headings based on / alongside an ordered list of values?
A measurement of ion transport numbers went wrong when an unorthodox concentration was accidentally used. An analysis of what could have happened yielded the following table. The values have been ...
- 111
0
votes
1
answer
78
views
Find matches between multiple columns in microsoft excel
I am trying to find matches between multiple columns and if matched then I want to highlight all the matched rows.
The columns can be like this --
AAA
BBB
CCC
Sample columns/rows
AAA BBB ...
0
votes
2
answers
2k
views
Updating a stock count between two Excel worksheets
Ok guys, I'm sure this is dead simple but it's got me flummoxed. Two sheets inside a workbook
I have an inventory list on the first sheet with 272 lines. This list has 7 columns and the first column ...
0
votes
1
answer
93
views
How Can I Streamline Modifications to Multiple Identical Excel Tables?
We use Excel for Mac to track our business' daily services rendered. We have 31 identical tables on one worksheet, each of them for a single day. Another worksheet then totals the entire month's ...
- 1