Questions about using in-cell functions/formulas in spreadsheet software.

learn more… | top users | synonyms (5)

0
votes
1answer
1k views

Use 'if' to check a condition. If condition is not met, then do not change the value in the cell. Keep the previous value as it is

Cell B1 has value 1 Cell B2 has value 1 Cell B10 has no value. I write 'if' formula: B10 = if(b2=b1, b2,) The value in B10 changes to 1. Now, I change B2 value to 2. The cell B10 changes to 0. ...
2
votes
1answer
3k views

Excel array formulas to find smallest date with multiple criteria

The array formula I have will give me the smallest date from a Service table, based on the following criteria: 1) The value in the item column matches the item # in cell A1 2) The value in the ...
0
votes
1answer
611 views

How to create a formula that can count values across multiple columns in specified rows

I have an Excel sheet where I have multiple tasks and multiple products. Each task for each product is rated red. amber or green depending on progress so far. Each task also has an owner. I want to ...
0
votes
3answers
97 views

Excel formula with multiple conditions

I want to tell certain combinations of genes that make bacteria species. So: sample number, gene A, gene B, gene C. The formula should be: If all 3 are negative then it is a rest group If A and ...
0
votes
1answer
512 views

Excel - value of the last changed cell in row

I hope someone can help, I have a number of cells in a row which is filled automatically by another application, I need to read the last changed value in that row into one cell. I know I can create ...
3
votes
1answer
683 views

Calculating difference between 2 times, excluding weekends, then averaging the results

I have an Excel 2007 worksheet where I need to calculate the difference between 2 times, excluding weekends, then at the end of a week or month, be able to find the average of the amounts of time in ...
2
votes
5answers
30k views

How do I make Excel recognise TRUE or FALSE text as boolean values?

I have been using the INT formula to convert boolean values TRUE and FALSE into 1s and 0s. However, I have copied some data I have been given into my spreadsheet and Excel hasn't recognised the text ...
0
votes
1answer
299 views

Move rows to new sheets in excel

I'm having a problem. At my work we often get spreadsheets with locations. We need to spread out each of the locations to a different worksheet. Because then we list nearby businesses to advertise to. ...
1
vote
0answers
148 views

How to create a formula which finds a cell that matches a range of other cells then produces a number if the cell appears.

am having trouble with finding a suitable formula for my spreadsheet in excel. In column A i have a list of postcodes (439 postcodes). In column B-BL i also have postcodes (these postcodes are in ...
0
votes
0answers
51 views

MIcrosoft excel 2003 auto populate cells

What I would like is input text into a cell say A1 and I want that text to then be duplicated in cell D1. But I can not type the formula into D1 itself i.e. no =a1. Is this possible? Thanks
1
vote
1answer
37 views

Zero's before Store Numbers in excel

I recently got a spreadsheet from a vendor with a list of store numbers that I need to use for a crystal report that I am running. Store numbers are 5 digits, most with at least one leading zero, and ...
1
vote
3answers
136 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: |---|---------|---------|---------| ...
2
votes
0answers
2k views

How to copy a column with conditional format by “value” - Libreoffice 4.1

I have a column that contains the data and a set of rules for the conditional format of the cells. i.e. the conditional format changes the background colour of some cells if they are negative. I ...
0
votes
2answers
325 views

Return a column value based on looking at 3 other columns

I have a pivot table that has 3 columns with values. I need to perform a search of these columns and return a value from another column. The issue is this - I need excel to look at the 3 columns and ...
0
votes
1answer
216 views

Best way for bidirectional referenced table rows for double-entry booking

I want to move to a Google Spreadsheets setup to handle my private booking. Currently I have four tables: Overview Accounts Income Outcome Except of "Overview" all share following columns: Date ...
2
votes
1answer
353 views

How do make an Excel cell reference a particular sheet according to the current date?

I have an Excel spreadsheet for tracking sales with the first page being for totals, then a separate page for each month's sales, which has subtotals on each page. Can I make the "this month's total" ...
0
votes
2answers
7k views

Excel - how to either have direct data input or cell value from formula

I would like to either populate a cell value from direct key input or have the cell value derived from a formula. Example: either directly input a number into cell A4 or have the value for A4 = A2 + ...
3
votes
1answer
595 views

How to plot coordinates in Excel?

I have a list of goals scored by Ronaldo and Messi and have created a manual heat map of where they have scored goals from. I want to write coordinates in a cell in a list and for the heat map to ...
2
votes
4answers
9k views

Excel-MATCH function doesn't work on pasted values

I'm trying to find the positions of the values(text) in one column in another column. I ran the function: =MATCH(B1, A:A, 0) and I get a #N/A result. But this result is incorrect...as I clearly see ...
0
votes
1answer
554 views

Find and Replace VBA/Macro in Excel 2010

Another Excel VBA question. I'm using Excel 2010 I want to create a Macro that will remove the following 3 symbols in any cell for a selected range: [ ] " For example; If I select a range of ...
0
votes
2answers
194 views

Is there a way to compare two tabs of data and place Difference at end of one of the datasets

I need to find a way to compare two spreadsheets and if there is a Differences, pull data from one sheet to the other. Sheet1 has a list of all Dealers in my Zone. Other Tabs in my workbook have ...
3
votes
4answers
11k views

Generate a comma-separated list of cell contents, excluding blanks

To concatenate a certain four cells, I'd use: =CONCATENATE(A2,",",C2",",D2,",",F2) This would make it so that... A2 = "Matthew" C2 = "Mark" D2 = "Luke" F2 = "John" would result in ...
2
votes
2answers
5k views

Copy cell reference with multiple row increment

I've exported delivery addresses from Pastel into an Excel Spreadsheet. Each address record that Pastel spat out has a lot of data for each customer that I don't need. Given the screenshot here, I ...
0
votes
1answer
3k views

Lookup list dependant on the contents of another cell

hope someone can help to crack this one? I've got a table on my data entry tab (sheet 1) where each row includes two data validation drop down lists (source is on sheet2). I want the options in the ...
0
votes
3answers
405 views

Can I use an IF statement as a parameter in a COUNTIF function?

If someone can help improve the title, by all means. I've been struggling with it for a while... I'm using Excel to analyze a list of filenames for potential issues...special characters, excessive ...
0
votes
1answer
4k 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
348 views

Get specific value from table with table name, row and column index

I am trying to create some sort of manual filter. If you look at the image below, you can see two tables named ONE and TWO. The table to the right contains 3 dropdown lists. The idea is that you ...
-2
votes
1answer
3k views

Match up 2 columns in excel from one worksheet to another worksheet

I'm having trouble comparing two worksheets. I have worksheet A and worksheet B. In each worksheet, column A have the customer number and column B have the date. The same client and same date can ...
4
votes
1answer
3k views

Linking Excel 2013 cell to Word 2013 table cell adds unwanted line breaks

I have a problem where I am trying to copy an excel cell that contains content using a formula, into a word document table cell and link them such that the formatting is used in word. What happens is ...
3
votes
3answers
3k views

Multiplying values in Excel 2010 (Same cell)

Another excel question. I'm using Excel 2010. If for example I have a value in cell A1, lets say 2.59 What I want to do is multiply that cell by 1.333 but in the same cell I don't want to have to ...
-1
votes
2answers
2k views

can Excel take data from a Text Cell and place it into a Formula Cell?

I am tracking several stocks, and saving their chart data in separate spreadsheets titled by their ticker symbol (i.e. aapl.xlsx, goog.xlsx, etc...). I want to have a single spreadsheet that I can ...
-2
votes
2answers
59 views

Excel - make a sum with some criterias [duplicate]

-----A ------B------------C 1||Name ||31-03-13 ||1-04-13 2||test1 ||>>>> 150 ||>>>> 10 3||test2 ||>>>> 750 ||>>>> 11 4||test3 ||>>>> 800 ||>>>> 12 5||test3 ||>>>> 900 ...
2
votes
1answer
3k views

Is there an Excel formula that can conditionally sum rows based on column headers?

I need to be able to put a formula into the last cell of a row (shown by = in the image) that totals the values in that row that don't have a cell with the text 'Done' at the top of the relevant ...
1
vote
1answer
444 views

Change Excel Worksheet background colour based on values

Hopefully some one can help, but not sure if its possible. I'm using Excel 2010. I want to change the background colour of a whole worksheet to green if the value in cell c20 is equal to or greater ...
1
vote
1answer
370 views

Highlight field which has a passed due date in google drive spreadsheet

I calculated a due date based on Column A with EOMONTH (Column A, 3). The resulted dates are in Column B. What I cannot achieve is to highlight the fields which are older than the current day. In ...
3
votes
1answer
859 views

Find last value in a cell range that meets a condition

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data: A B C D E F ...
0
votes
0answers
58 views

Formula to find after how many cells a value repeats itself

I've been looking around like crazy for this: I.m looking for a formula to count after how many cells does the value 'O' occur. For example, in the below: xyz 7 O 7 8 ...
0
votes
1answer
1k views

Counting and comparing rows for same values in excel 2007

I want to compare two columns of different sizes and count the number of rows in the two columns that are a match and report this in a separate cell. I have one column that is a list with the day of ...
3
votes
1answer
253 views

Excel macro copy same entries once in another sheet paper

I'm really new to VBA Excel. Let's say I have paper 1 with in column A many alert types, and column B the place where it happened (I'm working for a security company). What I need to do is read ...
2
votes
3answers
99 views

How can I avoid typing in a value which already exists in another range in Excel 2010?

I am working up a basic project tracking spreadsheet in Excel and would like to have two sheets - one which shows my active projects and one which shows my completed/cancelled/archived projects. I ...
1
vote
1answer
230 views

How do I lookup matching values in two Excel tables?

I have two tables, each with one common index column. One table has multiple entries of the index value with other information in adjacent columns; the other table has one entry of the index value ...
1
vote
1answer
602 views

Count duplicate cells (values, text & empty) when you compare two columns

I have two worksheets with the following data structures: sheet a) Id Name abn address sheet b) Id Name abn address 1 AA 123 ac 25 ad 124 ab 015 ...
1
vote
1answer
57 views

Excel: grab the non-N/A contents from columns

In Excel, I have two columns with formulas that produce either text or #N/A. COLUMN_1 COLUMN_2 text1 #N/A #N/A text2 #N/A #N/A text3 text4 I have a third column, which ...
2
votes
1answer
5k views

Dynamic Sheet Reference in Excel Formulas

This is my spreadsheet: I tried replacing the term Benco in the formula with the cell reference hoping I could drag the formula down and it would use the contents of the row containing the dealer ...
3
votes
2answers
1k views

Excel: searching for a maximum lower than X

From the sorted list of values, I'm looking for a maximum that is lower than X Example: A B C 1 1 X=7 2 3 3 5 4 8 ANS=5 Do you have any smart ideas for this problem?
1
vote
1answer
456 views

excel macro with column criteria to copy two rows in one sheet to another

I need a macro that searches for a string in a column; on finding that string it copies the row in which that string is found as well as the row above that. For example: Search for "boy" in column ...
1
vote
1answer
171 views

Matching Dates and Moving the rows to correspond with correct dates

Column A has dates over a given 10 year period (often a single date appears more than once). Column B has each date only once and Columns C-G have values corresponding to each date in Column B. I ...
1
vote
1answer
222 views

Adding a condition to a SUM formula results in #N/A error

I am trying to sum a range based on a few conditions. When I enter ...
1
vote
0answers
187 views

Conditional Formatting a date/time comparison

The function I'm working on specifically compares & colors a daily recurring time range set in 2 different cells. A specific date in the target cells (produced by a formula) compare to that daily ...
0
votes
1answer
217 views

Move every first line of a pair of cells in a column to the same row to the left of the second line

How to move every first line (digits) of a pair of cells in a column to the same row to the left of the second line (names)? The pairs of cells are at a random distance in this column. 0117-000-00 ...