All Questions
Tagged with microsoft-excel-2016 worksheet-function
443
questions
0
votes
2
answers
67
views
Calculate a moving average in excel where the lookback (window) duration is a variable that changes based on another cell's changing values
I am trying to calculate a moving average of a dataset where the moving window time duration is not fixed, but changes over time based on numbers in a different column. Using the screenshot I have ...
- 15
0
votes
1
answer
87
views
What is the formula for listing all non empty cells in a range of cells
I'm trying to gather all of the non empty cells in a n x n range of cells, then output them in a single column that I can drag the fill handle to automatically grab the next valid value. I'm running ...
- 3
0
votes
0
answers
66
views
Need Help Migrating A Drop Down List From One Workbook To Another
I need some help migrating a drop down list and the data selected from that list to another workbook.
I have a list of people that need to be contacted each week, with an updated file that is sent to ...
-1
votes
1
answer
307
views
Excel help - extract all unique values from column a in multiple sheets
I have a sheet called output, and a sheet for each month a jan, feb etc.
Without vba, is there a formula I can use to extract all information from column A in the month sheets, into column a in the ...
- 37
0
votes
2
answers
156
views
How to TEXTJOIN/CONCATENATE based on constraints for specific no of rows count
I need to concatenate certain attribute value based on the sequence number for specific material number and a formula to automate the process.
I have columns as listed in the below screen shot and I ...
- 13
1
vote
1
answer
69
views
Coloring cells across multiple excel sheets in an xls file based on a condition
I am new to excel. I have an xls file with 10 sheets. In each sheet I have two tables of size 10 x 10, one starting at position A4 and the other starting at N4. All the entries are numbers.
(Note that ...
- 113
0
votes
1
answer
45
views
Copy data values for another column if matched
Sheet 1 has Month, Day and Year while Sheet 2 has Year and Day. If the data of Year in Sheet 1 and 2 are matched I want to copy the Day values from Sheet 1 to Sheet 2. Is it done by VLookup and how?
- 3
0
votes
2
answers
313
views
How to group a column based on another column. Then write it into another column
How to group the animal name column based on the category column, and put it on the corresponding column (yellow area). For example in the figure, find the animal name (column A) with herbivore (...
- 1
1
vote
1
answer
328
views
Hyperlink does not change along with cell when filtered
I’m having a problem with hyperlink when using filters. So let’s say I have these table rows:
A
B
C
I have a hyperlink to cell C using the usual hyperlink option. But if I change the filter from “A to ...
- 11
0
votes
1
answer
387
views
2 Dates in 1 Cell with Formula in Date Format
I'm working between two tabs in Excel. One tab is for data entry and the other pulls the data using formulas. I'm trying to create a formula where it pulls 2 dates into 1 cell. The formula currently ...
- 3
0
votes
0
answers
112
views
Excel Formula to group similar values
I looking for help to solve my spreadsheet to return value of different cells being similar within say +/- 10%.
Say that I have in column values 20, 20.5, 21, 21.5, 23, 23.6, 24,24.8, 25.4..
These ...
- 1
0
votes
0
answers
35
views
Index/Match function output appears out of sync
I am attempting to use index/match in conjunction with an if/and statement that does the following.
If column C = Secondary
AND if column D = Information/Videos
Take value of column B and output it.
...
- 159
1
vote
2
answers
62
views
Excel formula help - date formula needed
Im hoping someone can help or advise.
I want a formula to count the number of days left in a month from the date of the first of that month. If that month has passed, I would like to it to stop at ...
- 37
0
votes
0
answers
71
views
Creating a technical excel data sheet to display efficient data
I am creating a table in excel that holds milestone achievement data with their respective amount. This table has multiple milestones that have only one amount, meaning that a set of milestone ...
0
votes
1
answer
70
views
Excel Sum Table based of criteria missing from another table
I have 3 tables. Table1 is a list of items w/ a sum attached to them.
Table 2 is a list of "official items" and a category.
Table 3 is a category that needs a sum.
i.e.
Table 1
A.
B
Parrot....
- 1
1
vote
2
answers
1k
views
Excel 2016 - How to count UNIQUE rows that are "assigned" to someone?
I am building a chart that contains the total amount of rows and accounts assigned to a user in Excel 2016. The first issue is easy and I resolved it with a COUNTIF function, however the second part ...
-2
votes
2
answers
11k
views
Conditional Formatting with greater than & less than parameters based on date in cell
Trying to conditionally format a cell based on the date in the cell. My ultimate goal is to try and track warranty expiration dates on some of the product we maintain. Within 30 days of expiration ...
- 1
0
votes
0
answers
208
views
Excel - Using VLOOKUP on grouped table records
I have an xlsx report from a finance tool which provides raw numbers but none of the formatting or formulae I wish to use to better read and analyse the numbers.
The report is structured like so:
...
- 279
-2
votes
1
answer
126
views
How to match and merge lookup results in Excel?
I'm a total newbie with Excel and I'm stuck at doing this. I'm guessing there must be some work around "lookup" functions but I don't know which one and how to start.
Let's say I have a ...
1
vote
1
answer
200
views
Drag to fill duplicates values
I want to create a sequence of dates that populate a column.
A
2023-12-31
2023-12-30
2023-12-29
...
Format Cell is set to Date.
Calculation Options is set to Automatic
Enable fill handle and ...
- 393
0
votes
1
answer
568
views
Is there a way to drag a SUMIF/(S) formula down vertically but pull information horizontally, and vice versa?
Essentially, I am trying to fill information into Sheet A (SA) using =SUMIFS but the information that it is pulling into the actual formula comes from Sheet B (SB) and is organised in horizontal form.
...
- 1
2
votes
4
answers
8k
views
How to paste only the formatted value not the actual value
As the title says, I want to copy and paste cells into the format that they are shown in.
Original value (actual value):
25
59,05
90,1
Formatted value (what is shown):
25,000
59,050
90,100
The ...
- 121
0
votes
2
answers
546
views
Transpose Group of Rows to Single Row | MS EXCEL
I need to Transform Some Uneven Group of multiple rows to single row
I Tried to Extract the Required data from multiple row to multiple Column. But all data placed under different Rows (same as input ...
- 115
0
votes
0
answers
39
views
need advice two excel formulas
#SOLVED -
=IF(AND(COUNTIF(b3:e3,"Yes")>=1,i3="Yes"),"Remove Online Module",IF(AND(COUNTIF(b3:e3,"No")=4,OR(i3={"Yes"})),"Complete Online ...
- 37
0
votes
0
answers
159
views
Call a sheet using a variable name
I have this code in wich I add a sheet with a certain name and then I need to call this sheet to get a cell value but it doesn't assume.
Dim nome1 As String
Dim tableName As ListObject
Dim addedRow As ...
1
vote
0
answers
251
views
How to make an excel sheet dynamically reference another sheet
I am trying to make an excel sheet that generates material costs for construction. My plan is to have one sheet where I can enter the material and its quantity, and another that stores the prices of ...
- 11
0
votes
2
answers
65
views
Find Multiple hits of the search value across all columns & rows of a table and return a list of the results from the 1st column of the table
I have a spreadsheet that holds a list of offices in the 1st column. Each column after this holds the list of Postcodes that this office covers (only the 1st part, ie se3). Some of the postcodes are ...
0
votes
1
answer
108
views
Microsoft Excel Conditional Formatting for Dates and Prices
I'm looking to conditionally format a table following this general idea; I have short dates in one column and years in the headers for several other columns, Id like to highlight the cell when the ...
- 1
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
0
votes
1
answer
56
views
Format of Excel drop down list
When choosing from a drop down list in Excel, is it possible for the cells to change the format when you choose a specific subject?
Each subject has a different format for the cells.
- 1
4
votes
2
answers
909
views
Create a formula that sums up values between equally spaced cells
I am trying to make my cells follow a continuous incremental pattern so that I can drag it down and it applies to all cells. I am creating the calculation in one sheet, but the data is in other sheet.
...
- 41
0
votes
1
answer
20k
views
Color another cell based on (if blank) by two different cells
I had this working at one point
Column A: Names
Column B: Date
Column C: Date
IF Column B cell is filled, Color Fill ColumnA-Cell GREEN
IF Column C is filled, Color Fill ColumnA-Cell is RED
...
0
votes
1
answer
396
views
Running several sets of assumptions through a model in Excel
Hello and thank you in advance for your help! This question is for excel.
I have created a dynamic model which outputs estimated annual interest revenue, and it depends on 5 inputs (average term ...
0
votes
0
answers
42
views
How do I use a series of quantities in a column of cells to populate set information for that many cells into another column?
I'm working with sensitive information, so I can't show too much, but I think my request will be clear.
I'm trying to use the below quantities to spit out values in a different sheet that number of ...
-1
votes
1
answer
86
views
Highlight rows when all values are for Order Number
I have a spreadsheet with two columns. One column is Order Number and the other one shows its status. As shown in the screenshot below:
Sample Data Sheet:
I want to highlight all the rows when the ...
-1
votes
1
answer
968
views
If a cell has a duplicate take value from another cell
As you can see cell A4 with the quantity of B4 has a duplicate in cell A24 with quantity of B24. There is an 8 and 4 in total.
How can I find this kind of duplicate and sum them together?
1
vote
2
answers
783
views
Why can't INDIRECT() take a dynamic array reference just after opening a file?
I have a formula containing INDIRECT() pointing to some range -- call it B4:F8.
If I specify the range explicitly, like this:
=INDIRECT("B4:F8")
then it works fine.
However, if the range in ...
- 464
0
votes
1
answer
829
views
Compass bearings on excel
Would like some help creating an excel formula:
Compass bearings are from 0 to 359. I have attached a picture of how it looks like here:
Basically, I am looking for a solution for when I input 2 ...
0
votes
1
answer
26
views
Time since previous order
I need to go through a big amount of data for the last 2 years in a company and calculated the time since previous orders for Users who have made multiple purchases. Which Excel function can be used ...
0
votes
1
answer
186
views
Excel SUMPRODUCT with OR and multiple criteria in { }
I would like with sumproduct to sum where:
name = {"john","an","hui","james"}
OR
Number= {"one","two","five","six","...
-3
votes
2
answers
219
views
Is it possible to display just the exponent value of a 5-digit number in Excel?
I have the following table and I am attempting to format data in a very specific format. In order to use the raw value for it's attended function, I must format the raw value, so there are 5 ...
- 43k
0
votes
1
answer
122
views
Stopping Excel Conditional Formatting rules changing between tabs
So I have set up a worksheet for my job and it is spread out between 3 different excel tabs. I created a number of different Conditional Formatting Rules to set up all the sheets in the same way to ...
- 1
0
votes
1
answer
435
views
Excel VBA How do I set active cell to be only a cell with X data?
Dim NA As String
NAFinder = 9
NA = ActiveSheet.Range("F9").Select
For i = 0 To NARows
NA = ActiveCell.Offset(i, 0).Value
If NA = &...
1
vote
1
answer
2k
views
Excel Formula to display date when cells are filled in
I have a table and I need a excel formula that says the following:
If Cells C1, E1 and F1 are filled out, than today's date will appear. However if Cells C1, E1 and F1 are not filled out, than the ...
0
votes
4
answers
906
views
How do I get VLOOKUP to return all results not just one (using formula not auto-filter)
I have a table full of several orders and the dates. On another page i want to be able to type in a date and for all orders to show from that date, like a VLOOKUP. But, a VLOOKUP would only display ...
- 1
0
votes
1
answer
25
views
How do I count how many times this category appeared only if the value in the column name is not equals to "N/A"
I have these sample data:https://docs.google.com/spreadsheets/d/1TSpuMRktIRAoACH5s1Dk9189NsNpJwjRtNaR5QROZg8/edit?usp=sharing
I wanted to count how many times a certain category has appeared only if ...
- 103
0
votes
0
answers
327
views
Excel for Web Data Validation List Not Working with FILTER() function
I am using a combination of a data validation list - which contains zip codes - and the =FILTER() function to try and create a drop down list of zip codes for the user to search from.
When I remove ...
- 13
0
votes
2
answers
2k
views
In Microsoft Excel, how to copy an entire entry to another sheet automatically?
I am currently extracting data for a research, and I want to automatically copy the data to multiple sheets when a condition is fulfilled, as the data keeps being updated over time. I am aware that ...
0
votes
2
answers
221
views
Index Function with error "You've entered too many arguments for this function"
=index(_70Rain,1+int((row(b4)-1/columns(_70Rain)),mod(row(b4)-1+columns(_70Rain),columns(_70Rain))+1)
I just followed the code given in https://www.extendoffice.com/documents/excel/1172-excel-...
0
votes
1
answer
483
views
In Excel my formula is skipping the first two cells of a table
Hi I have an Excel Spreadsheet and I'm trying to convert a table with multiple rows and columns into just one column for sorting and filtering. But the issue is that it doesn't start at cell A2 where ...
