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

**0**

votes

**1**answer

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

**1**answer

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

**1**answer

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

**3**answers

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

**1**answer

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

**1**answer

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

**5**answers

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

**1**answer

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

**0**answers

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

**0**answers

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

**1**answer

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

**3**answers

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

**0**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**4**answers

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

**1**answer

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

**2**answers

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

**4**answers

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

**2**answers

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

**1**answer

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

**3**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**3**answers

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

**2**answers

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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

**1**answer

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

**3**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**1**answer

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

**2**answers

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

**1**answer

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

**1**answer

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

**1**answer

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

**0**answers

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

**1**answer

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
...