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
261
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