All Questions
Tagged with worksheet-function spreadsheet
223
questions
0
votes
2
answers
65
views
How can I create a formula that counts the number of duplicates, returns the value in a cell of my choosing, and then deletes the duplicates?
I've got a list that, after I sort it, looks like this:
rough list
I need a formula that will return the number of times each entry is listed and delete all the duplicates. Typically, I simply count ...
- 43.2k
0
votes
1
answer
51
views
How can I combine two Excel formulas into one?
In a spreadsheet, I have five columns (total) and four of them have the components of the students' grade. The fifth has the final grade.
Now, I need to combine two formulas so that when the four ...
0
votes
1
answer
36
views
Count working days between dates
I am seeking a formula that can calculate the number of weekdays between two specified dates, or if the end date is not provided, calculate the number of weekdays from the start date to the current ...
- 43.2k
0
votes
1
answer
8k
views
matching names from one Excel Spreadsheet to another
I have two separate spreadsheets, both with several hundreds of employees names. My task at the moment is to go through one of the spreadsheets to see if any employees are matching on both, which will ...
CommunityBot
- 1
-2
votes
1
answer
92
views
How to count occurrences of rows that have certain value only if preceding by certain value, and count how many rows of that
I have some data spread over one column and 120 rows
there is a repetition of the value 1 and 0 corresponding to certain operation mode
so it looks as per the image attached
My question is how to ...
- 13.2k
0
votes
0
answers
39
views
Excel SUM function and + operator different for blank text
The + operator in Excel gives an error when adding two cells whose values are empty text, while the SUM function treats these cells as zero (as I expected). I am trying to understand if this is the ...
- 101
0
votes
1
answer
47
views
2 problems, complicated copying of data based on a "Number" and dates
The first problem I have is that I have a timestamp in one cell which includes the date and time and I would like to know if there is any automated way to separate them into 2 different columns (Date ...
- 43.2k
1
vote
2
answers
11k
views
How can I pull specific rows in Excel into a new spreadsheet
I have several spreadsheets containing data I need to analyse. I have a randomized list of numbers that dictate which rows in each spreadsheet I need to code for my data sample.
Is there a formula I ...
CommunityBot
- 1
1
vote
1
answer
31
views
How to vlookup all columns and sum
I have two table.
The first table contains menu and price:
Menu
Price
Pizza
20
Pie
10
Beer
5
Burger
10
The second one contains a buyer and menu. I want to get the sum of price based on menu like ...
- 1,661
0
votes
1
answer
70
views
How to count instances of repeated values in a cell range while ignoring blank cells
I have a dataset of subjects (one per row), each with scores taken over several days. Each day has multiple scores, but the number of scores varies such that there are many blank cells.
See a small ...
- 2,310
0
votes
0
answers
26
views
Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]
I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem:
After ...
- 2,283
1
vote
1
answer
70
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 ...
- 2,310
1
vote
3
answers
19k
views
Display real-time filtering data from another worksheet in Microsoft Excel
I want to display all rows that have a "Gender" column equal to "M" inside "sheet2", where the data is retrieved from "sheet1". The filtered data also has to be ...
- 109
0
votes
2
answers
314
views
Get unique IDs with date 1 or date 2. For IDs with both date, obtain row with most recent of the 2 dates
I have been trying to retrieve the row in the following sheet (sheet1) where the dates are matching dates in sheet2 without duplicating the IDs in Sheet1 (Column B)
Sheet1
Sheet2
In other words, I ...
- 2,310
0
votes
1
answer
1k
views
How can I evenly auto-distribute values across 12 columns in a spreadsheet so that their sums are as close to equal as possible?
I'm trying to distribute a bunch of annual recurring business costs across the span of 12 months so as to make each month's total expenditure as close to each other as possible. Doing this manually is ...
- 1,898
0
votes
1
answer
114
views
Is there a formula for retrieving data from multiple cells on a different spreadsheet and adding to one single cell on another spreadsheet?
I am working on a project in which I need to retrieve the Parent Items for a list of scrap material. The problem is that most of this material is used in multiple Items. When I run a separate report ...
- 9,494
1
vote
1
answer
939
views
Turn cell references in Excel hyperlinks into the text in the referenced cells
I have a column of bare URLs in Excel. Say it's Column A. I want to replace each URL with a friendly text link, like "here." Suppose A1 contains the link https://www.website.com/content/...
- 2,310
1
vote
1
answer
142
views
replace comma separated values in a sheet uses SORT formula to get sorted results from another sheet
I have a sheet called "Form Responses 1" connected with a Google Form, and I made another sheet/tab and used this formula:
=SORT('Form Responses 1'!A2:M, 4, TRUE)
to sort the responses with ...
- 665
0
votes
1
answer
231
views
Can I have excel automatically swap text in a column from one word to another without using another column?
I'm fairly new to Excel so excuse my noobish question. I am working on a roster spreadsheet and have a bunch of shop codes, essentially abbreviations of the shop name in a column, and I have been ...
- 9,494
2
votes
1
answer
686
views
How to set a whole row equal to a value, using ARRAYFORMULA() ? (in Google Sheets)
I would like to set the contents of all cells in a particular row to have the same value, sort of like having a banner that runs across the entire width of the spreadsheet. I'm working in Google ...
- 3,174
1
vote
1
answer
121
views
What's the mathematical, =SUM() equivalent of the =NPV() formula, in Excel or Google Sheets?
Is there an Excel/Google Sheet SUM formula equivalent to the NPV formula, ie. one that produces an exact same answer given the same input figures?
The question might be similar to these ones but the ...
- 3,174
1
vote
1
answer
207
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 ...
- 3,174
0
votes
1
answer
2k
views
Find next cell within column that is a number, starting from reference cell in another sheet
I think this sounds simple but I'm having a bit of trouble figuring it out.
I have 2 sheets, the Sample Sheet attached and an Input Sheet.
In the Input Sheet, I input one of the green shaded values (...
- 3,174
0
votes
1
answer
11k
views
Linking rows in Excel without merging
I would like to know if it is possible to link rows together in Excel without merging. For example, I would like A1 tied to B1 so when I move A1 around to different positions in Column A, B1 will ...
CommunityBot
- 1
0
votes
1
answer
88
views
Prepend text for spreadsheet
I want to have a string value prepended to a XL-style formating in a spreadsheet program (gnumeric). The current formatting is [$-f4f2]hh:mm which should take 1:00 PM as input and convert/display it ...
- 11
1
vote
1
answer
82
views
Custom Information Message for user based on another cell value
I am busy with an optimization spreadsheet which needs to display an information message, maybe similar to the Input Message for data validation (but I am open to suggestions). This message will be ...
- 21
0
votes
0
answers
16
views
Dynamically changing content of a cell dropdown for validation because of a huge list
I have a huge lookup list of items, each with a conversion factor that I wish to look up.
The lookup list is too big to be practical for a validation dropdown - this is how it looks:
Here's the ...
- 188
0
votes
1
answer
83
views
Print Excel Spreadsheet using Template?
So Let's say I have a spreadsheet
No
Name
Class
Gender
1
Jack
Science
Male
2
Rose
Science
Female
3
Kevin
Social
Male
4
Olive
Social
Female
and I have template
No
Name
Gender
1
2
3
4
How to ...
- 1,183
4
votes
1
answer
3k
views
Numbers conditional formatting based on a different cell in the same row
I have a Numbers spreadsheet, with "quantity required" and "quantity in stock" columns, then each part is on a row. I'm trying to use conditional formatting to turn a cell yellow when # required = # ...
CommunityBot
- 1
0
votes
1
answer
37
views
Microsoft excel formula to connect codes of smaller areas with those of the bigger ones and sum up their values
I am working with geographic information and I have bigger areas called Regso that concist of several smaller areas called Deso. The data I have is for the smaller Deso areas but I would like to ...
- 23.4k