Questions tagged [microsoft-excel]
A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function. Also add the the specific version tag (e.g. [microsoft-excel-2019] or [microsoft-excel-365]) as features and functions may vary between them.
25,845
questions
1
vote
1
answer
102
views
Excel formula to search a cell value in a row and fill it with an equivalent value?
I have this example excel file:
I want to write a formula in B (column) to search names in A (column) in second table and if it exists, fill it with it's ID value. Is it possible? Final result should ...
0
votes
1
answer
203
views
Any way to allow multiple people to edit a coauthored, password protected workbook, at the same time?
I am currently working on a sheet that I want select people to have full access to, while everyone else without the password for the sheet cannot. I can do mediocre and overly complicated solutions ...
-1
votes
1
answer
32
views
How to filter out some columns in a table?
Hi I have a large dataset containing data from:
A1, A2, ..., A12, B1, ..., B12, C1 ... ... H12.
for each column, there are many rows of numbers. I want to sort the data as the following:
A1, A2, A3, ...
2
votes
0
answers
38
views
Cell showing decimal but Formula Bar showing Percentage
I have never seen this before.
I have decimals entered into the cells. I changed the formatting of the cells to PERCENTAGE, however the cells remain decimals, but it now shows percentages in the ...
0
votes
0
answers
36
views
Referencing and evaluating another cell's array formula to create master array using VSTACK, in Excel without VBA/Macros
I'm trying to accomplish this task in Excel without VBA, Macros, or Power Query.
Here is a workbook with example sheets of data, an intermediate sheet, and a sheet showing the desired outcome: https://...
0
votes
1
answer
39
views
Excel: Referencing a cell from another workbook using a dynamic filepath/filename, inside a formula
I have 50 students, each with a gradebook with many scores I am compiling in a master tracker workbook. The data points come from about 60 different worksheets within each student workbook. Rather ...
0
votes
2
answers
59
views
Use a Macro or Formula to replace a single letter with a string of text
This should be pretty simple. I want to use a formula or macro that will automatically replace a letter with a string of text within the same cell. Ex.: If I type the letter "r" in Cell A1 I ...
0
votes
1
answer
90
views
VBA - Copy Visible cells not selecting everything
I have a very long macro that I've written that looks at a report sheet, breaks it down by many different criteria (putting some of the results on different sheets), and does hundreds of calculations. ...
0
votes
0
answers
25
views
Automatically split one Excel row into two for pivot tabling purposes
I have a series of accounting entries where each row has entries for multiple years. Think:
Entry # | 2021 | 2022 | 2023 | 2024
xxxx $1.50 $2.50 $3.50 $2.50
yyyy $1.50 $2.25 $7.50 ...
1
vote
1
answer
49
views
Effective Way to Count Days In Range In a Month
Good day,
I'm working on a schedule to determine the number of days a invoice applies in a calendar month. Some invoices cover a month, some 2 and other multiple. I've came up with an ugly formula ...
-1
votes
1
answer
112
views
Convert columns into a single row in notepad++
I want to make .srt file for the subtitles. for that i had the text in excel file. I copied the text into notepad++. Below is the text i have, and under that the formatting i want for the .srt file. I ...
0
votes
0
answers
23
views
Formula to identify where there is a value for '0' and '1' per date in Excel pivot table
I am analysing camera trap data looking at nocturnal denning sites. I have formatted my data so that I have a '0' value and an '1' per date when there is an animal entering and leaving a den site on ...
0
votes
1
answer
72
views
FILTER function with checks on multiple columns and values
I have two sheets: one with raw data and one where I want to filter out only rows of interest. In the raw data sheet, I have two columns that need to be filtered (say, Columns B and C). Column B is ...
0
votes
0
answers
34
views
My windows excel icons are same for .xlsx and macro enabled .xlsm files
I'm running Microsoft Office Professional Plus 2016.
The macro enabled .xlsm files, used to have an exclamation mark in their excel icon.
For some time now, all type of excel files have the same ...
0
votes
1
answer
36
views
Find highest number in range and subtract a selected cell
I want to find the highest number in a cell range and subtract the number from a different cell.
eg
900
1000
800
850
and subtract value in different cell
Thanks for any help
1
vote
1
answer
33
views
Put numbers in Ascending order then label them (highest to lowest) 1,2,3 etc in next cell
I have a small list of numbers in no particular order in a column
900
465
650
1125
800
1212
545
in the cell next to each I want to show were each number comes in ascending order 1,2,3 etc (highest to ...
0
votes
2
answers
48
views
How to count the empty cells above specific values?
This is going to be an odd request, apologies! I would like to be able to count the number of blank cells that are directly above a "Banana" cell. This only needs to happen in one column. In ...
0
votes
1
answer
41
views
How to use conditional formatting on a specific formula added?
I want to add a conditional formatting to a cell. In that cell I have a formula that provides 2 dates. The dates in the cell look like this 01/12/2024 and 01/24/2024.
Originally when I had 1 date in ...
0
votes
0
answers
25
views
I need to compare two excel sheets and then merge the demographics into one of the sheets
I have two sheets:
Sheet AA has 250 Names in column A and a dollar Amount in column C.
Sheet BB has 2000 names in column A and sheet BB has most of the addresses in columns h,I,j,k. Some are missing.
...
-1
votes
1
answer
55
views
Copy Excel row of information being pulled from a tab into a second row, but only changing the tab it's being pulled from
I currently have an Excel row on a Summary Tab that is pulling information from a separate tab. (="Hood 1'!G12) is an example of one of the cells. Since there are over 800 cells in the row, I'm ...
0
votes
1
answer
39
views
IF AND FORMULA using conditional formatting?
Im not sure if the IF and AND formula would work in this scenario but Im trying to use conditional formatting in 1 cell referencing off two other cells.
So in cell H13 has a certain date 1/14/2024 and ...
0
votes
1
answer
32
views
If formula not working for hospitality catering manager
I am creating a spreadsheet for my Catering Manager. For example, she can select one of three options for breakfast. All she does is enter the quantity beside the option that the customer has ...
2
votes
2
answers
274
views
How to nest 2 different formulas in one cell
I'm trying to combine these 2 formulas in one cell. I'm trying to get 2 different dates in one cell. To show in the end (e.g. 2/15/2024 / 2/24/2024.
My 2 formulas
=($C$1-DAY($C$1)+1)+14
=($C$1-DAY($C$...
0
votes
0
answers
61
views
Copy and paste cells in a workbook does not produce the expected result
I have a workbook where copy & paste is not working as I would expect.
If I copy, for example, (C3:C5) and paste it into (F3) then the result is that (F3:F5) contains the content of (G3:G5). i.e. ...
0
votes
1
answer
64
views
How could we check if a column of a table is filtered or not, by formulas
I need to check whether a column of a table is filtered or not. The data type of the column is Text. I want to use worksheet Formulas, not VBA macros.
Something like this:
=If(IsFiltered(Table1[Field1]...
1
vote
0
answers
17
views
Why does adding an entry into a table result in the font size changing in a cell that is populated using Xlookup to extract entries from that table?
I created a table that is updated with new entries, thus changing size with each new table entry. Xlookup is used to pull entries from the table. The cells where the entries are stored is set to ...
2
votes
1
answer
199
views
How do I do a simple select on tabular 2D array?
How do I do a simple select on tabular 2D array?
Let's say, I have a named range called "Testy", and that is all the information I have.
I know the Columns (dimensions) of the data are in ...
1
vote
2
answers
56
views
SUM select ranges of varying lengths in a single column using null/non-null values in second column to decide SUM indexes in an Excel formula
Example of format for the issue, I need to generate the third row that is a sum of each color block:
I have an odd set of columns in a very large dataset, and I am not great at Excel. I could be way ...
1
vote
1
answer
34
views
Formulas corresponding to the correct table in my sheet, vba. Multiple tables across multiple sheets
I am setting up a cycle count sheet using VBA. I am having trouble with the formulas in my tables only referring to the first table in the sheet for the input values.
For example, I have 4 tables per ...
-1
votes
1
answer
30
views
Map a column of cells to hyperlink of flies = to cell value in Excel
I have a column of MD5 hashes (string value) and I'd like to make each be a hyperlink to the file with the same name as the cell value. The files are in the same folder as the spreadsheet.
The Excel ...
0
votes
0
answers
53
views
Getting details of why a file won't save after changes to a table
I have a macro-enabled Excel-file which fails to save with the error message
Errors were detected while saving '[filepath]'. Microsoft Excel may be able to save the file by removing or repairing some ...
1
vote
3
answers
128
views
Convert an Excel table from one structure to the other
Original table
I have this table in Excel or LibreOffice.
Unit number
Type
Name
1
Object
Top
1
Object
Bottom
1
Object
Left
1
Object
Right
1
Object
Back
1
Object
Front
1
Property
Right-Fixed
1
...
0
votes
0
answers
81
views
Unable to Print in Landscape Mode from Excel Document
I'm encountering difficulty when attempting to print an Excel page in landscape mode. Despite trying various adjustments, such as scaling, changing paper formats, and playing with file and printer ...
0
votes
1
answer
66
views
Excel: Using COUNTIF + UNIQUE gives generic error "There's a prblem with this formula"
I'm trying to create an excel function that counts the number of unique entries in a range of (potentially merged) cells. The UNIQUE function returns two values for merged cell, one of which is zero. ...
0
votes
1
answer
46
views
Excel Reference next row in array when logic is false
I want tabs in my Excel sheet to reference fields in a different page if criteria is met; if the criteria is not met, I want to skip that row, and move to the next row where the criteria is met.
My ...
0
votes
1
answer
29
views
Looking up a value and putting a different value in a cell in the same row
All the cells I reference can be found in the image below. I'm trying to figure out how to search for a name that I enter in F3 and then put the date I have in E3 into the corresponding cell in column ...
0
votes
0
answers
34
views
Combining two sheets based on 3 columns
I have two different sheets with 4 columns in each:
USERNAME
ARCHIVE
ACTION
TOTAL
Archive might be empty or a text.
Same username might have several rows.
Action can have one of two values (OP1 / ...
-1
votes
2
answers
53
views
Check if previous lines have same value in a specific column in Excel [closed]
I want to create an auxiliary column with the following value:
If column M is not empty, and column M of some previos line has same value -> 1
Otherwise -> 0
Any simple way to do so?
0
votes
0
answers
42
views
How could I add a value from text that could be across multiple sheets?
I have a calendar that I put people down for time off. This calendar has a sheet for each month where I put something like John-V. What I want to do is get a count for each John-V across every sheet ...
1
vote
1
answer
76
views
MS Excel: How to Find & Replace 15+ character text preventing becoming Scientific Notation
Problem Statement
We have an Excel Sheet with a column of data like below:
+-----------------------+
| XYZ Data |
+-----------------------+
| XYZ:12345678912345678 |
| XYZ:...
0
votes
0
answers
12
views
Generating PDF sheets from an Excel sheet
I populate an Excel spread sheet then generate A4 size sheets which has info on 2 sheets the 2 sheet has 1 line of name signature and date. I want to move that line onto the first sheet permanently. ...
-1
votes
1
answer
35
views
What is causing the xcopy file from having an error in it?
I am new to xcopy. I am trying to move a file that is in excel format to a new destination and as a csv file. I have to do this so that the DMExpress ETL job can move the file and then read it. This ...
0
votes
1
answer
78
views
I need to sum morning shift for 8AM till 1:00PM in Excel
I want to sum for month of January 2023 from time 8:00AM (08:00) till 1:00PM (13:00) only.
I used the following formula:
=SUMPRODUCT((Sheet1!$A$4:$A$151>=DATE(2023,1,1)+TIME(8,0,0))*(Sheet1!$A$4:$...
0
votes
1
answer
33
views
How can I perform a VLOOKUP by updating only the newly added data each month in a main database while preserving the previously looked-up information?
I have 2 tables, the main data base with data that gets added monthly, with a name, date and corresponding rating. I also have a lookup table that has names and rating of that person, the rating of ...
0
votes
1
answer
74
views
Excel for Microsoft 365: Obtaining the Default Column Width
I am using VBA in Excel for Microsoft 365.
I wish to obtain the default column width and store it in a Double variable. This is driven by the desire to not hardcode it. I have attempted to obtain this ...
0
votes
0
answers
32
views
How can I easily make a chart showing what weeks a task was completed or not?
I'm trying to make a chart that shows who completed an inspection for each week. At my work, we run Sunday to Saturday and within that time period, we expect our guys to submit a truck inspection.
I ...
0
votes
0
answers
29
views
Vacation calendar in Excel with restrictions
I am trying to make a vacation calendar in Excel that will prevent a user from entering more than the allowed number of people off per department per day.
I have a workbook that lists all employees ...
0
votes
1
answer
52
views
EXCEL Index match not showing results for other date
The project is a reservation calendar.
I have a table for input (dates, wish house, and morning or afternoon).
Then based on this table it shows in the calendar if it is free or reserved with the name ...
0
votes
0
answers
53
views
Sync Google Sheet with Microsoft Form Responses
I have a Microsoft form and the responses are recorded in an Excel file. I want to sync this Excel file with a Google sheet to do some calculations there. Sometimes, some formulas break the connection ...
0
votes
1
answer
71
views
In Excel 365, how to remove the green "sharing" check indicator from the bottom-right of a workbook
At some point, Excel added a green checkbox inside a very small window that floats in the bottom right of the workbook. It only shows when Excel is active and can be dragged to new locations (as was ...