Questions tagged [spreadsheet]

Questions about spreadsheet software in general and interoperability between spreadsheet programs.

Filter by
Sorted by
Tagged with
1
vote
1answer
10 views

Searching a value of partial text from another sheet

My scenario is, I have 2 worksheet: Sheet1 contains the name of the restaurants, and Sheet2 contains the name of restaurants to avoid. My issue is, I use a formula in conditional formatting that only ...
0
votes
0answers
7 views

Is there a way to return certain rows on Google Sheets that I have tagged?

I have a large list of books that I would like to tag and be able to filter out the row/book I am searching for without having to scroll through the entire sheet. For example: Book A humor, non-...
2
votes
1answer
46 views

CONCATENATE cells in a column IF condition in other colum is met

Using Google sheets (or Excel if Google sheets can't do it). I'm trying to concatenate any cell in B column, if its corresponding A cell is "include". The columns are very long and therefore ...
0
votes
1answer
21 views

Insert big text into multiply rows by keyword into Excel or google spreadsheets

If I have multiline text like Line: x1 = 10, y1 = 100 x2 = 30, y2 = 40 Line: x1 = 100, y1 = 120 x2 = 130, y2 = 140 And so on. Is it possible to insert it at once at Excel or google spreadsheets so ...
0
votes
0answers
14 views

Google SpreadSheets query: how to join “grouped by” cells in a list? [closed]

Suppose I have this table: | Name | Amount | Category | | name_1 | 10 | A | | name_2 | 2 | B | | name_3 | 3 | B | | name_4 | 6 | C | I know how to ...
0
votes
1answer
7k views

Making a cell mandatory based on previous cell entry

I am looking to achieve the following: Column H has 5 potential inputs from a data validation list: Pass, High, Medium, Low & N/A If Column H is either High, Medium or Low, I need Column I to ...
0
votes
1answer
18 views

Excel - sort one spreadsheet by exact order of second spreadsheet based on one column with same data

I trying to find a way how to sort one spreadsheet into the same order like second spreadsheet - based on their one same column for example. Here's an example what i need, i will make more sense like ...
3
votes
1answer
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 = # ...
0
votes
0answers
15 views

Excel on notebook

I want to change all numbers in column I to be - (negative) if the corresponding words in column B are SELL Example Col B row 1 “sell” then col I row 1 -123 Col B row 2 “buy” the col I no change Col B ...
0
votes
2answers
3k views

How to add prefix or suffix to range of cells in Excel?

I have cell A in excel full of images name without the extension I want to add ".jpg" as a suffix to these ranges of cells however I need to skip non text cells by not adding anything. Any help to ...
1
vote
1answer
15 views

Combine one set of time series data with another when some dates are missing in one data set

I have a LibreOffice Calc spreadsheet which contains two sets of time series data e.g. |Date-A |A | | | |Date-B |B | |2020-01-01|101| | | |2020-01-01|201| |2020-01-02|102| | | |2020-01-02|202| ...
1
vote
2answers
44 views

Excel: How can I find all the occurrences of the matching columns

Consider I have the following table (unsorted): Name ID Account John Doe 1 1234 John Doe 1 6524 Jane Doe 2 6346 Jane Doe 2 1241 Jane Doe 2 3634 And another one like this: Name Account 1 ...
0
votes
1answer
45 views

i want to return value( eg;- sum) only if the source is not blank,other wise return blank !!not zero!! in excel, wps

see this screenshot for reference see this screenshot.. I used SUM formula in the botton cells to get sum of all values from each row , the bottom cells display TOTAL and i will assign the TOTAL ...
0
votes
1answer
27 views

In LibreOffice Calc, why would sort buttons and menu items suddenly become disabled?

I was creating a spreadsheet in LibreOffice Calc. Several times, I used the "Sort Ascending" and "Sort Descending" toolbar buttons. All was well, and everything was working as ...
0
votes
1answer
308 views

Hyperlink to certain text in excel page?

I'm trying to create hyperlinks to certain points in a large data sheet. Each are separated by a block of text identifying what is there. I know I could create the hyperlink to certain cells in the ...
1
vote
3answers
11k views

Merge multiple columns into one

In Excel, I'd like to merge/combine the data of multiple, nonconsecutive columns into one (excluding the headers, preferably on another sheet). Example: I have a sheet containing rows of names which ...
2
votes
6answers
3k views

Good tools to visualize spreadsheets?

I have a few CSV and XLS files that I would like to analyse or "visualize" online (like in charts etc). I tried Google Fusion Tables, but I want to know if there are any alternatives. Also suggest ...
0
votes
2answers
71 views

Ascending order of Excel rows

I have the following rows in Excel: How can I put them in an ascending order (i.e. notice how the row starting with 12 comes before that starting with 118). Thanks.
3
votes
3answers
54k views

Put a checkbox in a cell in Excel v15 for Mac

I am trying to make a checklist in Excel. Basically I am trying to make a cell: Into a checkbox that can be checked or unchecked, and saved with the spreadsheet: It's super-duper easy to do it in ...
5
votes
1answer
32k views

Excel 2007 - changes on one sheet affecting other (without cell references)

Some strange behaviour I've encountered twice in Excel is that changes made to one sheet would be duplicated on another sheet in the same workbook. The first time, I typed values into a few cells on ...
3
votes
4answers
279 views

keyboard equivalent of dragging the little black square to generate sequence

What is the keyboard way of generating a sequence? The pic below shows libre office calc. In order to generate the sequence of numbers 1 to 10. I simply enter few numbers of the sequence (in ...
1
vote
3answers
7k views

How to filter a spreadsheet on multiple filter values

I've got a spreadsheet of addresses with the following columns: First Name, Last Name, Street Address, City, State, and zip code. I'd like to be able to filter or otherwise extract a subset of these ...
0
votes
1answer
25 views

How to copy rows of specific columns based on a criteria (and then continuously update)?

I have 2 sheets, let's call them sheet 1 and sheet 2. In sheet 1, I have some data where essentially at its core its a Scoring tally, something like this: Name Age Gender Score Jack 12 Male 155 ...
0
votes
1answer
34 views

How to get LibreOffice Calc to recognize dates in YYYY/MM/DD format?

I've been given a large tab-separated-values (TSV) text file that contains dates in the format YYYY/MM/DD. I tried copy and pasting the dates into LibreOffice Calc, but it didn't recognize the strings ...
0
votes
2answers
4k views

Referencing a single row to multiple rows in Excel

I have a worksheet with a list of sales, one sale per row. In order to import this data into my accounting software, I need to create a separate worksheet that formats the data differently. Each row ...
1
vote
1answer
35 views

How to calculate max sum held over a month?

I need help with a formula that needs to calculate the max sum held at one time for each month. In this example it would be 54 for March (12+13+14+15, held on the 29/03/2021). Month is determined by &...
1
vote
7answers
136k views

=if() function is not working like it should be

I am trying to use an IF function in a formula in MS Excel 2013, but every time the IF function ignores the condition I wrote and goes to the true value. I checked my IF statement over and over, and I ...
1
vote
2answers
33 views

Need to simplify date entry in Excel spreadsheet

Every month I need to do a large spreadsheet for billing purposes. Among other columns I have one start date and another for end date, formatted as dd-mmm. To enter this date in the spreadsheet for ...
3
votes
4answers
3k views

How do you find the maximum value in a column, skipping to every nth value?

Using LibreOffice's Calc program (nearly the same functionality as Microsoft Excel) I have a set of numbers in column T, which represent numbers for the week (with labels in S). Each week's worth of ...
8
votes
4answers
110k views

Too much text for an Excel cell — how to make the cell scrollable

What do I do when I have more text in an Excel cell than I want to show in the allocated space? Something making it "scrollable" would be preferred.
1
vote
0answers
8 views

Searching indexed spreadsheets from Windows 10 explorer “search”

I have a client that used to use Windows 7, and was able to search directly from the Windows explorer (start menu) to find spreadsheets by searching a number that was on column B within. Now that they'...
0
votes
3answers
128 views

Excel MAX IF Function To Ingore Zero Date (1/0/00)

=MAX(IF('[YTD -2021.xlsx]OB'!$H$2:$H$23967=$C5,IF('[YTD -2021.xlsx]OB'!$J$2:$J$23967=$B$1,'[YTD -2021.xlsx]OB'!$A$2:$A$23967))) I want to ignore Zero date and result should give me 0 or blank instead ...
2
votes
1answer
3k views

Excel - compare three cells, if same then something, if not then something

=IF(F9=G9=B9; H9*C9;"Problem") This is the formula I'm using, but it always results in "Problem", even when the three cells have same text in them. I want to compare the cells, and if they are the ...
0
votes
1answer
48 views

Based on cell formula, display another cell value

I want Excel to look into the formula of a cell and then display a different cell based on that. confused yet? Column A on sheet 1 has a size and column B on sheet 1 has a tolerance. Could be same ...
0
votes
2answers
5k 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 ...
0
votes
2answers
6k views

How to add multiple digital signatures to an Excel spreadsheet?

I have an Excel spreadsheet that lives on a shared folder, that everybody can access. On that spreadsheet, I have a worksheet with a list of tasks that have to be completed, but not necessarily by the ...
1
vote
1answer
41 views

Sum and lookup over range in spreadsheet

I have a google spreadsheet that looks like this Items Item Value A 10 B 12 C 15 D 20 Calc Total Items A A B A A C C C In Total I want to have the a formula that sums the value of the ...
0
votes
3answers
66 views

Is there a way to define a range with infinite row or column number in a spreadsheet program?

When defining a named range in a spreadsheet program, is there a way to define a range that covers for example A1:A[*infinity*], so I will be sure that the range will always include all rows, whether ...
0
votes
3answers
51 views

Distinguish between A* and A in excel

I’m creating a spreadsheet to be used in schools that use various countifs, subtotals etc to look up data. A big problem I have is that I need to distinguish and count separately students on an A* (A ...
2
votes
2answers
3k views

How to fix a corrupted LibreOffice spreadsheet

I have a substantial multi-sheet LibreOffice worksheet that seems to be throwing a lot of errors in LibreOffice, including crashes. Any idea of how to get it to work as expected? I'm thinking ...
3
votes
3answers
13k views

Decimating rows in spreadsheet

Is there such an option to preserve only every 10th row in my spreadsheet? It can be Excel or Calc. Quality standards want some noise to be added.
7
votes
1answer
6k views

Apple numbers: How can I create a filtered column (B) with only unique values from column (A)?

I'm using Apple Numbers. I have a table with data like: A | B ---------+--------- Spain | U.K | Spain | Germany | U.K | Italy | And I would like to set table B to include ...
0
votes
2answers
6k views

Simple pattern with Excel COUNTIF to match a substring?

How do I fix this formula: =COUNTIF(A1:A10,"*jim*") to match anything containing the sub-string of "jim" but from a cell, as: =COUNTIF(A1:A10,C11) where cell C11 contains the text "jim"? Perhaps ...
0
votes
1answer
42 views

How to group multiple date|data columns to one single date column on the left?

I've got a large set of data that looks like: and I've been trying to format it so that instead of having a column of dates for each item, there's only one column of dates on the left. The problem is ...
1
vote
1answer
7k views

Copy reference instead of value/formula in Excel?

I have some cells from one table I which I want to reproduce in another table, initially in the same order (but later, I'll mess this order up by moving cells around). For the sake of discussion ...
0
votes
0answers
29 views

How to isolate list of values in Excel spreadsheet

I have a randomly generated list of 5,000 filenames. I'd like to isolate this list from a seperate excel spreadsheet of 60,000 rows, in order to extract the respective values from other columns. How ...
0
votes
0answers
14 views

How to return the sheet name of a given cell of a query result

I have scrubbed the internet for answer to this and and either I am asking the question the wrong way or it isn't possible. Here is a example template of what I am wondering about: https://docs.google....
0
votes
1answer
3k views

How to get plain text, colon separated file into spreadsheet with data separated into columns

I received a digest of registrations for events. The fields are separated by colons and in plain text. Can I copy and paste this info into libreoffice spead and get it displayed in columns?
0
votes
1answer
15 views

Count number of rows with a specific MONTH in the date of a specific cell and a boolean condition in another cell

Let's say I have a number of dates and whether they occupy full or half a day... DATE | WHAT | HALF? ... 2021-4-1 | Something Something | false 2021-4-2 | Something Something | ...
3
votes
1answer
52 views

Filtering over merged downwards cells in MS Excel-2016

Note: This question has been asked back in 2010 on this site here. And yet, I would like to post it one more time, showing what I did and what didn't work. Besides, Excel has changed quite a bit since ...

1
2 3 4 5
13