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

learn more… | top users | synonyms

3
votes
1answer
6k views

Excel: #NAME? appearing when linking/referencing other spreadsheets/workbook cells

I'm updating a spreadsheet that refers to data in another worksheet =NameofWorksheet for some reason I get #NAME? in some of the cells, even when the actual path is correct. The only way to overcome ...
2
votes
3answers
2k views

Turn Excel spreadsheet into a formula

I have an Excel spreadsheet that has a complex computation that is not trivial to turn into a macro or a single-cell formula. The spreadsheet has about 10 different inputs (values a human enters in ...
-1
votes
0answers
11 views

working with dates in google spreadsheet [migrated]

I just started working with google spreadsheet i need to know how to compare in date in a row to the current date, if true then the next steps must be executed. if anyone can help me with this
0
votes
0answers
19 views

Comparing date in google spreadsheet

I am trying to compare dates in a column to the current date using the script below but it gives me undefined status var toDay = new Date(); if (row[8] = toDay) {Department == "CRM";} ...
0
votes
1answer
17 views

Merge/concatenate/append spreadsheet documents, not spreadsheet contents

There is much information here and on the web about merging/combining the data of several spreadsheets into a single one. I want to do something much simpler but cannot find how. I have several files,...
0
votes
1answer
14 views

Comparing dates using logical operator

I have this excel sheet which I will be used to send notification The first 3 columns contain date of expiry and next 3 column notify 1 , notify 2, notify 3 contain a date which is 30 days prior to ...
2
votes
2answers
26k views

How do I calculate the total difference between two columns of figures in Google spreadsheet/Excel?

Usually I would just calculate the difference of each row like this: =B1-A1 Then I would sum up the values of the column like this =SUM(C1:C10) and get my desired value. I don't want the C-column. ...
2
votes
3answers
6k views

In a spreadsheet, is it possible to separate rows into groups that will not change order when filtered?

I am trying to make row subgroups in my spreadsheet rows so that I can sort my data but have rows in any particular subgroup stay together. This is probably easiest to show with a picture. Sorting ...
0
votes
1answer
3k views

Syncing two sheets, while being able to hide different data

I'm pretty new to excel- so please bear with me. I have created a spreadsheet to organize gear by serial numbers and by who has it. This list is getting updated multiple times daily as gear shuffles ...
0
votes
1answer
42 views

Create graph of values, mean per day, grouped by variable

I have a spreadsheet containing the following values (slightly simplified): Date - day in number - Temperature - Windspeed - Hours of sun. Data example: 01-09-2000 - 01 - 20 - 15 - 9 02-09-2000 - 02 -...
0
votes
1answer
34 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 ...
0
votes
2answers
174 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 ...
3
votes
2answers
27k views

Dynamically filtering data before importing from SQL Server in Excel

I'm trying to figure out whether there is a way to filter the data from an SQL Server connection before it is imported into Excel. The initial import doesn't matter (when the connection is set up), ...
0
votes
2answers
17 views

Highlighting Groups of Alternating Names in Excel

I have been trying to find a good way to do this but it seems like I can not find a good Conditional Formatting formula to work with what I need. I am trying to do something along the lines of this ...
1
vote
5answers
2k 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 ...
2
votes
2answers
926 views

Numbers imported into Ubuntu Libreoffice calc from text or csv files are coming in as text

I working in Libreoffice 3.6.0.2 in Ubuntu. I have a text file that has some columns of text and columns of numbers. When I import them into Calc either by copying and pasting or saving as a csv and ...
1
vote
2answers
23 views

Merge / glue exported pdf from spreadsheet application

Spreadsheet applications offer to export tables as pdf. Often, the table is to wide to fit on a single page (without scaling), so several pdf pages need to cover the full width of the table. Let us ...
0
votes
3answers
43 views

Transpose data from row/column that matches certain criteria

I conducted a survey that asked for a Score and a Priority for each question. The response data lists each component in individual rows, so that it's treating Score and Priority as different ...
1
vote
1answer
33 views

AVERAGEIFS values that are not NA

I have the following AVERAGEIFS that averages column I grouped by the values in column B = AVERAGEIFS(B:B, A:A, A:A) Problem is, sometimes, the column B sometimes contains an NA() error. How do I ...
0
votes
2answers
74 views

#VALUE! Error while trying to use a referencing cell in a formula

I would like to use the value inside a cell which continuously gets updated from a web page. The cell called C20 linked to another sheet called ratedatasheet and that sheet displays a page and table ...
0
votes
2answers
47 views

Date stamp when editing rows in spreadsheet

I have a spreadsheet which needs date stamp for each row as soon as there are changes being made in cells within that row. For example, I change something on cell B14 or E14 or G14 and today's date ...
1
vote
2answers
2k views

Create shortcut key to insert new row using Excel 2010

Tried to create a macro in Excel to insert a row: Sub InsertRow() ' ' InsertRow Macro ' ' Keyboard Shortcut: Ctrl+Shift+I ' Selection.EntireRow.Insert End Sub However, this does not insert a ...
0
votes
4answers
25k 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 ...
0
votes
1answer
22 views

Categorize spreadsheet lines - ala SQL GROUP

I want to consolidate a series of name-value pairs by name and sum the values. The pairs are in two columns of a LibreOffice Calc spreadsheet, about a hundred rows. Input: Output, to another part ...
0
votes
0answers
14 views

Cut and paste large amounts of text into ActiveX text box with scrollbar

I learned in another post on this site how to add an ActiveX text box to a cell with a scroll bar. Thanks! The only problem is, for the scroll bar to appear, I have to retype all the text manually. If ...
4
votes
4answers
36k 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
31 views

Show Excel Cell Only If Another Has Data

I am trying to format the below Excel sheet so that when a date is entered into cell J1, cells G24 - G37 populate with a date a specific to what I need. I currently have the cells formatted as follows:...
1
vote
1answer
20 views

Can I (and If so How?) use the MATCH function to search two (or more) different spreadsheets for a value?

First I'll explain what I already have (which works), then explain my question, which is about expanding it: I have two different spreadsheets that will be worked on at the same time, I needed to see ...
0
votes
0answers
24 views

Excel 2007 Monthly total

I have a spreadsheet keeping track of changes needed to update a parts list. I want to keep a monthly total of who submitted the change & how many they did each month. Column "G" has the date ...
0
votes
0answers
23 views

Google Sheets - Selecting latest date across multiple columns for a row that matches a criteria

Here is the spreadsheet setup Now I'm creating a report where I can enter the Member_# in cell K1 and the latest date from columns D, F, and H appears in cell K2 as well as the test label from the ...
9
votes
4answers
22k views

How to Label / Tag Items in a Spreadsheet

I'm a novice at spreadsheets, so I hope you'll forgive me if I'm asking a super obvious question. Is it possible to use tags in Excel/Google Spreadsheet? I'm creating a spreadsheet to log all the ...
0
votes
2answers
42 views

Excel sorted heading won't sort data in whole spreadsheet

So I would like to be able sort all my data using the little drop down arrow created by the filter function, all the way from F to JE. At the moment it will only sort from F to AK. Any idea how I ...
1
vote
1answer
109 views

In Excel for Mac (2008), how do you display characters that are converted to “#” symbols?

I am using Excel 2008 for Mac (I am also trying to work using the newest version of Excel on my computer, but it keeps crashing). When I open the spreadsheet that I need to work from, some of the ...
0
votes
1answer
80 views

Extracting data from an excel sheet into another excel sheet

I have a sheet with certain data arranged like this: X8 - something - something2 X12 - somethingelse - asdfg X8 - asft - sastaryraavt X8 - random - random2 I need to extract only and all of the ...
2
votes
2answers
47 views

Is there a way to merge tabs in Excel without using a macro?

I'm needing to combine worksheet tabs together to allow me to see all of my data on one page. Can it be done without using a macro?
4
votes
4answers
5k views

Excel annoyance: How to maintain formulas in a ledger?

A common use of Excel is to create a spreadsheet which functions as a transaction ledger of some kind, where one or more fields in each row is calculated by adding a value or values from the current ...
1
vote
1answer
56 views

Excel: Sort by result of comparison

I think it'll be easier to elaborate by explaining with the rules and example. In a soccer competition (UEFA Euro), there are several groups of four teams each. Top two teams from each group qualify ...
0
votes
0answers
71 views

Copying data from a mixture of protected and unprotected workbooks in Excel

I've been given 16 productivity-tracking spreadsheets from a team, and tasked with pulling the numbers from all the sheets into one, for a 'team average' type deal. Two of the 16 workbooks are ...
3
votes
1answer
38 views

How can I look up the 2 axis values by inputting a number?

I'm a graphic designer and not very familiar with Excel but I'm fairly sure it's the simplest solution to my problem. I have a table (see below) with 2 axis going from A to L. Inside that table are a ...
0
votes
1answer
41 views

Is There an .xml Format File that Excel Can Read

I'd like to use C++ to generate an Excel file. I know that Excel can read .csv files, but I was looking for something with more power. For example, the ability to create separate sheets and insert ...
5
votes
8answers
5k views

Excel spreadsheet bogged down, Is there an alternative software? [closed]

I work for an organization with a yearly budget that is an Excel spreadsheet consisting of about 25 large sheets that are linked to one another. There is a big summary sheet that draws it's numbers ...
0
votes
1answer
28 views

Excel TODAY() function

I have two tables in excel The first table is called Oranges, it shows where the oranges were produced and where they were exported to: The second table is supposed to show the total oranges ...
2
votes
2answers
65 views

Creating an Invoice in Excel

I have only dabbled with Excel and not much in quite some time now. I have an Excel Invoice laid out so Column A is a name of a job and each row in column A can be a different job. Now column B ...
4
votes
10answers
44k views

Can't insert cells in Excel 2010 - “operation not allowed” error message

I was working on a spreadsheet in Excel 2010, and all of a sudden when I attempted to insert a new row of cells, I saw that the insert and delete options were grayed out. I attempted to copy a ...
2
votes
2answers
132 views

Copy paste from spreadsheet to browser list

I have a browser based form like the one shown below. The Student No and Name of the Student fields are not editable. I need to key-in numbers to the Total field. The Student No field is numeric. ...
1
vote
1answer
61 views

Merge 2 excel spreadsheets both containing updates in rows and columns

I have 2 spreadsheets and need to merge and update the data combination of both. Spreadsheet A userid, name, phone1, phone2, city 1, mister T, 123 123, 123 124, paris 2, mister A, , 123 124, tanger 3,...
0
votes
0answers
11 views

how can I clear a form but leave formulas intact

I have a spreadsheet I use to track expenses. At the end of the year I'd like to use this for 2016. Is there any way to clear the data I added over the year but still leave all the formulas in tact?
4
votes
1answer
5k views

OpenOffice Calc - how to insert BLANK in a formula?

In Calc, as in most spreadsheet applications, an IF() function exists that can be used to control behaviour, for example inserting this formula in cell C1. =IF(A1>B1;"A";"B") will display A in ...
0
votes
1answer
42 views

Is it possible to automatically separate line/scatter plot data into different series based on an additional column?

My data looks something like this: H, 2015-12-11T19:44:34, 3270 W, 2015-12-11T19:44:34, 2150 H, 2015-12-11T19:49:35, 3370 W, 2015-12-11T19:49:35, 2290 ... As a one-off, I can get ...