1,542 questions
2
votes
3
answers
128
views
Excel - How to return cell value in a Column C in sheet2 to Column C in sheet1 when values in Column A of sheet1 & 2 match
I'm new to nested formulas and VBA and was hoping someone may be able to help. I am limited to using excel 2016.
I am trying to workout how to dynamically populate a cell in sheet1 column U (Due date) ...
- 21
0
votes
0
answers
58
views
Next criteria when sumifs returns 0
I'm running into an issue with reliable calculating some production data.
I want to calculate the total amount of produced units per year.
I already split all data from the database to different ...
- 3
0
votes
0
answers
48
views
Excel 2016 - INDEX MATCH SMALL incorrectly returns same text for each numeric instance of 0 [duplicate]
I'm using Excel 2016 and am trying to find the 5-most scheduled widgets and the 5-least scheduled widgets for a given year.
I can utilize INDEX MATCH LARGE with accurate results for the most scheduled....
- 417
-3
votes
2
answers
49
views
Finding a value by two criteria: by value and range in Exel
I am trying to find value "profile" in Excel 2016 formula on sheet 2 from the table on sheet 1 by two criteria:
Frame number and elevations range.
Is it possible to use the command Lookup or ...
0
votes
2
answers
116
views
How to Extract the Actual Value from &8&P in VBA?
What I tried:
I attempted to retrieve the value from the RightHeader in VBA using the following code:
Sub GetRightHeaderValue()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)
Dim ...
- 1
0
votes
1
answer
41
views
How to I use VBA in excel 2016 to fill blank cells with same values as in the cells above?
I created a form that collects data from 2 different sheets, and in the output sheet if there are blank cells they need to be filled with values as written above
Sub FillBlanksProtocol()
Dim ...
1
vote
2
answers
80
views
Sorting issue when consolidating monthly data into a year to date file. Using query, sorting by day, time, and letter
I am collecting data from each month on measured parts into a single file for the whole year. When using a "query from folder" I am able to get all the data together, formatted, and sorted ...
3
votes
2
answers
135
views
Excel: Counting cells which contain one (or more) specific strings
I'm using Excel 2016, and I have a dataset with a large number of strings, and I need to find out how many cells in a given range ('Geotech Data'!G:G in this case) there are that contain one of 29 ...
- 33
0
votes
1
answer
71
views
Summing data from specific rows in power query
I made a mini query here to represent the issue I'm having. I'm trying to find a measure or function I can use in power query that will give me the sum of the sales amount but only for rows where &...
- 1
1
vote
0
answers
89
views
How adding a digital signature in a workbook Marked As Final - Excel 2016
I'm trying to insert a signature in a received workbook requiring that. But it is Marked As Final. A MARKED AS FINAL banner appears on top of the sheet when the workbook is open. Pressing Edit Anyway ...
- 42.3k
3
votes
1
answer
722
views
Is there a way to create a simple custom formula in Excel without VBA?
My organization uses Excel 2016 but plan to remove the ability to use VBA macros or user defined formula soon. We are trying to find ways to adjust to the change.
Question:
Can we replace the ...
- 53
1
vote
2
answers
82
views
How do I filter data to only include Item Numbers where there is a negative value present in Projected Quantity column?
I need to only return rows that have Item Number 67, as there is negative Projected Quantity values here. I need to return all rows with Item Number 67, not just rows with a negative Projected ...
1
vote
1
answer
68
views
How do I populate empty cells with different values depending on what category the empty cells are in?
I need to fill the blank cells in column C with the number that is already there for each ITEM NUMBER. For example for ITEM NUMBER 19951034, there should be a value of 222,434 in column C for each ...
0
votes
1
answer
93
views
Another way to copy an Excel cell range in email body?
I have an issue while copying a range from Excel to email body.
I already have a pretty much working script.
My issue is that the tool is crashing due to building too many temp_documents. I had the ...
- 45
0
votes
2
answers
379
views
Why is this excel macro failing to run non-interactively via task scheduler since a windows update
I have a bat file, that executes a vbscript, that runs an excel macro (I'm using Excel 2016). This is running on a Windows Server. Everything worked ok for years until the server was upgraded from ...
- 71
3
votes
2
answers
132
views
Trying to Stack 2 Columns into one Excel
I am currently attempting to combine two columns into one but have encountered an error that prevents me from completing this task. Additionally, the data from the second column appears to be pasting ...
- 611
0
votes
1
answer
48
views
Updating Workday.intl formula with vba userform value
I have a workday formula that calculates a date based on a month duration. I would like the user to set that duration in the userform and update the formula to that. I am receiving a Run-time error: '...
- 1
0
votes
2
answers
50
views
I want to find out which IDs have both visit 2 and visit 4, e.g. My dataset looks like the following and the excel I have available is from 2015
I want to find out which IDs have both visit 2 and visit 4, e.g. My dataset looks like the following and the excel I have available is from 2015.This is an example of how my dataset looks:
ID
Visit
...
-2
votes
1
answer
235
views
Sort Via Index Match
I am using this formula in Excel 2016 so it doesn't have the sort function.
=IFERROR(INDEX($F$41:$F$500, MATCH(SMALL(IF(COUNTIF($J$1:J1,$F$41:$F$500)=0,COUNTIF($F$41:$F$500,"<"&$F$41:$...
- 81
0
votes
0
answers
62
views
Need guidance with Excel Add-in Edits - Changes not saved
I am working on a custom excel add-in abc.xlam. I have created several forms and modules in addition to the existing ones in the add-in. I am unable to edit any changes by clicking save.
A pop-up ...
3
votes
2
answers
402
views
How to Separate Text and Numbers Using Formulas?
I need help with an Excel formula to extract text and numbers into separate columns. I'm using Excel 2016 and have data in cells A1 to A4, as follows:
A1: abc
A2: 123
A3: def
A4: 456
A5: ghi
A6: 789
...
- 109
1
vote
1
answer
23
views
Why is my Grading not working if the there are only two scores?
Im writing a grading report system in VBA excel. Im trying to grade one, or two or three papers.
But my code only works if all the three papers are entered. But some subjects have only two, and some ...
- 11
0
votes
0
answers
27
views
Active X Controls change position on worksheet despite being software-fixed [duplicate]
the position of my controls change after clicking on them, despite my attempt to fix position by the code below. Is there anything else I should do? Please help.
Private Sub CalculateMe_Click()
Dim ...
- 75
2
votes
4
answers
222
views
Compare a number array with existing records in Excel or VBA
I have a sheet with 3k+ lines and 15 columns. Each column is filled with a random number from 1 to 25. Very much like lottery results, where each column is a number drawn from the lottery. (from 1-25)
...
- 149
0
votes
2
answers
114
views
How to compare two lists when one list is the same repeated in Excel 2016
I am currently working with a large a series of data sheets of various lengths (1500-15000 lines) that record training records for employees. The current system built by a previous employee has no ...
2
votes
1
answer
567
views
Excel 2016 - INDEX MATCH to return value based on smallest of multiple match results
Sorry, this is my first time posting. Hope I am able to explain clearly. Using Excel 2016. Hi, I have data as seen below, and I want to return the date based on the smallest of the matches returned.
1
...
1
vote
0
answers
104
views
Need to create employee roster on excel - when number of logins at a particular hour is provided for each day
I have a peculiar problem where despite being moderately good with Excel, I am stuck.
I have figured out number of logins required on each day of the week at different hours basis my demand pattern. ...
- 13
1
vote
1
answer
436
views
How to copy merged cells with VBA
I have 2 Excel file with same format but one is Master file (for calculating) and one file is extracted data from system. I want to copy extracted file's data to Master file with VBA but it didn't ...
- 1,793
0
votes
1
answer
89
views
Excel calculating different dates using same formula, between server and local installation
I work with Excel files which I prepare on a server and then copy locally so that they can be emailed out. I've found that after I copy the finished files to my local machine, the installation of ...
1
vote
1
answer
643
views
How to create a table that gets data from multiple tables in separate worksheets and consolidate them
I have an excel workbook with three worksheets, and have 9 tables in each worksheet, the column headings and the first column all have the same value across all tables,
I am looking for a way to ...
- 13
0
votes
1
answer
49
views
"Autocorrect format" for all values in a column (instead of line by line)
[In Excel 2016] I received worksheets with dates arranged like MM/DD/YYYY, but I need it in YYYY/MM/DD order. I use formulas to rearrange them into the correct order in a string, then paste all as ...
- 3
2
votes
1
answer
1k
views
Create Vertical Scrolling Text in UserForm
Trying to create a userform in VBA excel where in the text can be scrolled vertically to show what I add in specific cells of excel. I am using version 2016 of excel.
Have taken VBA code from google ...
- 21
0
votes
1
answer
115
views
How to check if the first 3 cells are greater than the other first 3 cells in two tables
I basically have two tables that track scores. All I am wanting is to add up the number of 'Sweeps' or number of times in one table (will use the same formula for the other table) where the first 3 ...
- 109
0
votes
2
answers
119
views
How can I control column number in Excel INDEX function based on criteria?
I have a table like this:
A
B
C
D
apple
banana
0
0
boat
0
0
orange
0
1
car
lemon
1
1
cat
0
0
dog
0
0
goat
1
0
red
1
1
blue
green
0
0
lamp
0
0
garden
0
0
Using an array formula, I want to list ...
- 3
1
vote
1
answer
374
views
How to filter only first row in sets of data on Excel 2016
I have sets of data like this on Excel 2016
Can I filter only first row by these sets of time? e.g. I want to select only first row of February 1st on 12AM and February 1st on 6AM.
Thank you for your ...
- 33
2
votes
4
answers
862
views
Sum values(column C) based on unique value(column A) after filtering(column B)
I need some help creating a formula.
I have an excel file with 3 columns and I need to sum values of column C, based on the unique values of column A even if I filter something in column B.
Col A
Col ...
1
vote
3
answers
362
views
Excel column A same value and column B value combine in Column D
Please help looking for the excel formula:
The column A have the same value, column B is qty, combine in text with '+' in column D, the column C is a,b,c, sample picture below, the Column D is in text....
- 21
1
vote
1
answer
148
views
MS excel 2016 formula for booking calendar
I have sheet with calendar, where row (B5-H5) is day of the month and column (A6-A11) is room number. I want to see names of People that has reservation that day and for that room number in each cell.
...
- 13
0
votes
2
answers
625
views
Only Return 1 Duplicate Value Based on If The Lowest Row# Duplicate Has Been Returned
The title might be confusing. Long and short, I'm referencing dates in a specific column from sheet1 in comparison to the date on the calendar in sheet2. This is returning relative information from ...
-1
votes
1
answer
36
views
Office 2016 installing different build numbers on different PC's from same install file [closed]
I'm at a loss. When I install Office 2016 (we use a volume license) depending on the machine, I get build #16.04XXXX and some machines I get build #16.05XXXX. both copies work fine, but in #16.04XX ...
- 11
0
votes
2
answers
133
views
MATCH single cell in multiple columns for INDEX
I know this may seem simple, but I'm going insane at this point.
Say I have the value 123456 (a cell in a sheet), and I want to search for it in the table below in another sheet and return the ...
- 21
0
votes
1
answer
186
views
How to get the number of records that match all conditions in a filtered, external worksheet?
I have a simple and small file which does some counting in an external workbook. The data in this external workbook is filtered using autofilter (for example: a specific time range).
Let's name the ...
- 359
0
votes
0
answers
776
views
How to create a Excel file using OfficeOpenXML in ASP.NET Core 7 Web API and open that file in Excel 2016
I have created an Excel file using OfficeOpenXML, the file was successfully created, but when I try to open it in Excel 2016, some of the data shows as a black screen, but it is opening properly in ...
- 17
1
vote
0
answers
43
views
Getting a table of prices paid for stock purchases with shares remaining, ordered by lowest basis
I have a table of stock trades, purchases and sales, ordered by date. Each row is a trade. If the transaction is a purchase the shares are positive values, if a sale the shares are negative. Col A ...
0
votes
1
answer
231
views
Excel number in column
I have a problem to be solved in Excel.
I have three columns in Excel.
The DX column is made up of numbers or sections.
The DW column must be the result of a revaluation of the first number available ...
-1
votes
1
answer
120
views
FullCategoryCollection works only on first chart
So, this code does what I want, to reset the categories in a graph, on the first. It runs through all charts, I get the index right, use the name of the graph, at this point I'm not sure what to try ...
0
votes
1
answer
132
views
In Excel 2016 Copy data from column A:A to Column B:B without duplicates
I have excel 2016, so I can't use the unique function. In Column A:A, I have several names but some are duplicated. I need a formula that automatically copies the names in A:A to B:B without ...
- 11
1
vote
3
answers
481
views
Problems with Index Match Array returning duplicates
I'm attempting to return the column name for the three lowest values in a set. At the moment if two values are the same it is duplicating and returning the first value, which I don't want. I need it ...
1
vote
1
answer
105
views
Power Querying numerous tables to combine all information into 1 table, then combine duplicate text and add up corresponding counts together
I am new to power query so please excuse my lack of knowledge if the answer is relatively straight forward.
The table below is my initial testing with transforming data from tables into my workbook ...
- 61
0
votes
0
answers
56
views
Lookup Column headings and return corresponding column value in another row
I would like to create a formula that will look across columns for a particular value in the headings, and then return a corresponding row value within that same column. I think this is best explained ...
- 334