0
votes
1answer
23 views

Spreadsheet data checking columns

I am trying to separate datas from columns(A,B&C) based on datas from an index table at columns (E&F) I need to list only those data from columns A/B/C when column B data has no corresponding ...
0
votes
1answer
20 views

Combining Two Differently Formatted Spreadsheets

I'm relatively new to excel and am having some issues comparing two sheets of data. We're looking at the benefits over the fiscal year by month. One spreadsheet has the project ID on the left, and the ...
0
votes
1answer
16 views

I want to create a format in Excel to trigger a colour code?

Currently I have a drop down list on certain cells, connected to a list of statuses. 'Complete' which marks as green, 'Pending' which marks as orange, 'Outstanding' which marks as Red. I want each ...
0
votes
2answers
50 views

How to sort columns in Excel so that only one value appears in each row?

Say I have a a spreadsheet with the following data: A B C D --------------------- 2 1 0 2 3 2 1 4 5 3 3 6 7 5 6 8 11 8 10 10 ...
1
vote
1answer
38 views

How to perform a function based on surrounding cells in Excel?

My rows on my spreadsheet look like the following: foo | cherry | 400 bar | lemon | 300 foo | cherry | 500 bar | cherry | 70 I want another row to make calculations in column C based on the ...
1
vote
1answer
50 views

Sumproduct, Sumif, Indirect and named ranges issue

I have this formula that works: =SUMPRODUCT(SUMIF(INDIRECT(Financials&"!$B3:$B10000"),'Information PG1'!$K$2,INDIRECT(Financials&"!d3:d10000"))) where Financials is a named range. If I ...
1
vote
2answers
39 views

add same value to range of cells in microsoft excel

I want to add the same word from cell B1 to B40000 I do not want to use the click and drag box method to apply it across multiple cells/rows. I know I have to type in the formula bar B1:B40000 but I ...
0
votes
1answer
42 views

Rolling Averages sheets in excel

I'm collating a document for a Daily Production report at work, in which every day, a new tab is added and the information on part production from each department for the previous day is recorded. The ...
0
votes
0answers
6 views

How can I select that element from a vector of Gnumerc / Excel of which the difference of a given cell is minimal?

I would like to do a goal seek in gnumeric / Excel. I would also like to make a column of data, which values are selected from a vector (range) based on minimal difference to a given cell. What ...
2
votes
0answers
27 views

In Excel, Can I have two rows of (worksheet) tabs for my workbook? [duplicate]

I'm using Excel (2013). I have a workbook with numerous different worksheets, and would like to have them take up two rows of worksheet tabs rather than just one. Can I achieve this somehow? I can't ...
0
votes
2answers
48 views

Pulling out data from a spreadsheet

I need to add information of clients who have visited the office, which department, who they were referred by, who they were referred to etc. I have linked a screenshot of what I have currently made ...
0
votes
1answer
12 views

Remove symbols while appending in excel

I'm having some parsing issues here. I have a column of Twitter handles, with '@' in front of them, and I'm trying to auto-append them in a URL on the next column. Example: | @Bob | ...
0
votes
2answers
70 views

Google Spreadsheet/Excel filtering

As you can see in this image I have spreadsheet of many athlets, that will participate in competition. My task is using groups (or ranges) DISCIPLINAS (en. Disciplines), which contains 100m run, ...
1
vote
1answer
67 views

Automatically sorting a range of cells after one cell within the range has been modified?

I have a number of named vertical ranges on a spreadsheet, containing lists of names. New names are usually manually added at the very end of each list. Obviously, this means that unless manually ...
-4
votes
2answers
332 views

Custom Data Validation in Excel 2010 [closed]

I'm trying to make error messages appear when someone tries to enter the wrong data type in a certain cell with data validation in Excel 2010. Is there a feature or some sort of formula I can use? ...
0
votes
2answers
671 views

Cannot delete created excel table

I have created an excel table. However, I now want to delete or change it because my range changed. However I can't delete it in the excel Name Manager: Any suggestion why I cannot delete it? ...
0
votes
1answer
50 views

How to prevent all-filtered-out row label values from appearing in a pivot table?

I have a pivot table in MS Excel. For the same of example, suppose the basic data has 3 columns: Name: A string. CountMe valued either 0 or 1. Year an integral number. Tokens the data we're ...
0
votes
0answers
21 views

export marked rows in Excel

How can I export the subset of all rows marked by an x in column A? Example data set: A B ---------- 1 apple 2 x orange 3 x lime 4 cherry 5 x lemon Exported data set: A B ---------- 1 ...
0
votes
1answer
138 views

Count instances of a letter in a row if a certain cell in that row contains a specified date

I have a spreadsheet containing a column of dates (week commencing) with columns of data to the right of it, and a conditional formatting rule that highlights this week's row based on the date. I ...
-1
votes
1answer
105 views

Share cost of activities between participants in spreadsheet

Say you have a spreadsheet you use to fairly share the cost of activities between people (example google doc): A B C D E 1 Activity Cost User1 User2 User3 2 1 ...
0
votes
2answers
575 views

How to format excel cells that show duration in years and months?

I can't figure out how to do custom formating that would format input "1/5" as "1 year, 5 months", or "5,0" as "5 years". I do want the cell to retain original input of "1/5" but visually display "1 ...
0
votes
1answer
656 views

Disable prompts in Microsoft Excel when saving a CSV file [duplicate]

I have to download multiple csv reports from the Internet throughout the day, make a tiny change to them, and save the change. Excel adds 3 completely unnecessary dialogs to what should really be a ...
0
votes
1answer
78 views

How do i change value in filtered cells?

Please could someone help me? I have a spreadsheet full of different vacancies and they go in order of the date advertised but have different departments within that and I need them numbered. I only ...
0
votes
1answer
49 views

In Excel, how do I access the {value in col B} of the same row whose {value in col A} equals the {{value in row x of col A} minus 365}?

In Excel, how do I access the {value in col B} of the same row whose {value in col A} equals the {{value in row x of col A} minus 365}? My intention is to calculate a vehicle's running average ...
0
votes
1answer
52 views

IF (AND () function does not work as it should be

When I write the following simplified command in a cell, =IF(AND(C6="A";D6="30");D2;0) it does not appear the value of the cell D2 but zero, depsite the fact that the 2 assuptions of IF(AND) are ...
4
votes
1answer
290 views

Allowing users to unhide (format) only chosen columns

TL;DR, what I want to achieve: Hide some columns which I do not want to show to anyone (check) Hide some columns which I want to show only to some users (check) Set separate passwords for both ...
0
votes
0answers
17 views

Software to manage service commitments?

I work with an organization that does a lot of volunteer service work and need a solution to the following problems that I considered using a spreadsheet for but would like additional suggestions. *2 ...
0
votes
2answers
55 views

Define a range based on the result of the INDEX function

In Excel it is possible to write a function looking like that: =SUM(A1:INDEX(...)) Where the end of the range of the data to sum is dynamically defined by the result of the INDEX function. The ...
-1
votes
1answer
89 views

How can I do this in spreadsheets?

Let's say you have a table like this: | A | B | C | ------------------------------------- 1| sex | age | answer q1 | ------------------------------------- 2| ...
1
vote
1answer
576 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.
0
votes
1answer
81 views

Excel: Copy identical data sorted by date-time to future dates

I apologize in advance if this has been answered before but I couldn't find anything using similar wording to my question. I have an Excel sheet of one year of data, with a value for every 15 ...
0
votes
0answers
30 views

how can I merge in master record details to each row in excel or openoffice?

Say I have sheet 1 with: company1 90230 CA company2 80014 CO company3 15090 PA And sheet 2 with: company1 Bob Smith company1 Sue Smith company3 John Doe How can I produce sheet 3 with: company1 ...
0
votes
1answer
142 views

Using SQL to join spreadsheets in excel

Based on the explanation here: How do I join two worksheets in Excel as I would in SQL? I tried to join two excel sheets from different files into the same sheet. However, I keep getting this error ...
0
votes
0answers
94 views

Indirect lookup across spreadsheets

I have a spreadsheet that has information about all products for every week of the year. Currently I have a second spreadsheet that is setup to look at the first one for each field (currently has 52 ...
0
votes
0answers
56 views

Don't want to include blank rows in Excel table

I have a table in excel which contains the following columns: Brands: Cell B Open Closed: Cell C Case Number: Cell E Case Summary: Cell G At the moment these are the following formulas I am using ...
-1
votes
1answer
161 views

How to get data from separate worksheets displayed in a single separate worksheet

I am working on a spreadsheet in Microsoft excel 2010. In the spreadsheet I have 3 worksheets. These are known as: 1st 2nd Cases Now in the 1st and 2nd Worksheets, there is a table at the bottom ...
0
votes
1answer
77 views

Are spreadsheet programs inherently unsuitable for “one row=one case” data?

Often when I'm working with large data sets, I structure the data in a way where every row equals one specific case. For example, in a psychological experiment, I input the data so that all data ...
1
vote
3answers
87 views

Collect Row # of Matching Elements in Excel

I'm trying to collect the row numbers that match a certain value in multiple columns of an excel worksheet. The behavior I'm looking for reflects the table below: |---|---------|---------|---------| ...
0
votes
1answer
2k views

Excel Spreadsheets: If A:A does not equal C:C, how do i list columns A, B and C somewhere else?

I have an issue with referencing an excel spreadsheet: I have data in columns A and C that's (mostly) equal, I want to isolate and create a separate list for the rows that are not equal. For ...
2
votes
1answer
3k views

Split Text into Columns Function in Excel

I have an excel spreadsheet with rows of data as shown below. There is a code with the product name and then a number. QA32 Product One (Large) 23 AZ63 Product Two 96 PR65 Product Three 149 How do ...
0
votes
1answer
69 views

Excel: How to efficiently check whether a selected cell is referenced in any other cell

I want to check whether a selected cell ( say A1) is referenced in any other cell in the sheet I’m working in? Is there a similar tool that shows you arrows from the selected formula to referenced ...
1
vote
1answer
113 views

excel: convert transaction list to balance

If I have transcation data like this: (date,spend,balance) 2013-01-01, ,$60 2013-01-03,-$10,$50 2013-01-04,-$10,$40 2013-01-04,-$10,$30 2013-01-06,+$20,$50 How can I convert it to a list of ...
0
votes
2answers
99 views

Excel/Spreadsheet - formula for last value in a row?

I'm trying to forecast increasing vendor costs in a spreadsheet. My sheet looks something like this col | A | B | C | D | E | F vendor | 2013-09 | ...
0
votes
2answers
65 views

Excel Equations for Spreadsheet

I have a excel spreadsheet, and while I like to think of myself as quite the excel wizard, this one has me stumped, it's probably something ridiculous but I really can't get it too work. I have a ...
0
votes
2answers
415 views

Merging multiple versions of same excel spreadsheet

So here's the situation: I have multiple versions of the same spreadsheet-- each one has the exact same row and column labels. The difference between any two given spreadsheets is that data in one ...
0
votes
2answers
4k 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 ...
2
votes
2answers
7k 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
1answer
42 views

Excel/Calc: Pivot table with (almost) identical row: how to identify them?

I need to examine via pivot a datasource from a sheet. This datasource sheet is something like this: | Field 1 | Field 1 | Field 3 | | AAAA | BBBB | CCCC | | DDDD | EEEE | FFFF | | GGGG ...
1
vote
1answer
3k views

How do I add values in column B if column A contains the same employee surname more than once?

State of Data: I have column A which has names of employes and column B has number of hours. How do I add the hours if column A contains same employee surname more than once? I have been using what I ...
0
votes
1answer
166 views

Vlookup not working as expected for soccer data

i have a vlookup function that is supposed to give me some data from another worksheet. the worksheet contains soccer data, specifically about the european cups of the past 3 years (9-10, 10-11,11-12, ...