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

**0**

votes

**1**answer

175 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

300 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

5k 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

461 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

**3**answers

6k 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

495 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

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

**2**

votes

**4**answers

8k 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

4k 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

323 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

3k 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

251 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

2k 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

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

**0**

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

52 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

2k 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

381 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

309 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

749 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

55 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

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

**1**

vote

**3**answers

94 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

185 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

545 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

56 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

4k 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

812 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

419 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

127 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

190 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

156 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

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

**-1**

votes

**1**answer

104 views

### Create an event in excel - add value to cell X after input on cell Y -> clean cell Y

Basically, what i want to do is:
Whenever i insert values in column B, it add the value to column A and then sets the column B to 0.
A B
23 0
I tried this function(passing A as uni and B as ...

**1**

vote

**1**answer

2k views

### How can I get the row number from a vlookup?

So I have two spreadsheets where there is some messy data in one of them that needs to be in another.
What I have done so far is copy the columns I want from the messy spreadsheet into a new tab on ...

**0**

votes

**2**answers

66 views

### formula writing for excel if/then

Column D is a list of states. There are 3000 lines.
If column D is AL, GA, SC, FL, NC, TN, or MS then I want column G to say to say GA.
If column D is LA, TX, NM, OK, AR or NE column G should show ...

**2**

votes

**1**answer

1k views

### Excel: Reverse VLOOKUP with comparaison

I have an original table (the value column is sorted):
name | id | value
--------+-------+-------
abc | 1 | 12
def | 2 | 15
ghi | 3 | 23
jkl | 4 | 29
...

**1**

vote

**1**answer

68 views

### Excel fomularbar shows correct Value, Sheet shows VALUE error

Here is the data I have been using, data format all General:
Name Address
Adam 250
Mohame 150
Adam 220
Adam 423
Yasir 240
Ibrahim 236
Adam 242
Index formula cell B11 contains the ...

**0**

votes

**1**answer

35 views

### Transpose data by checking similar cell data

I have a Excel sheet like below..
Color Size Qty
Red L 182
Red M 223
Red S 155
Red XS 30
Blue L 700
Blue M 845
Blue S 665
Blue ...

**3**

votes

**1**answer

191 views

### Input the same formula in excel across multiple worksheets to follow a sequence

I need to put the same Excel formula in Excel so that it follows on in the sequence. So sheet 1 needs to have ='Booking Sheet'!F1 then sheet 2 ='Booking Sheet'!F2 and so on.
Is there a way I can do ...

**0**

votes

**2**answers

2k views

### Parse data in excel — space delimited — right to left

1 Exxon Mobil 452,926.0 41,060.0
2 Wal-Mart Stores 446,950.0 15,699.0
3 Chevron 245,621.0 26,895.0
4 ConocoPhillips 237,272.0 12,436.0
5 General ...

**6**

votes

**2**answers

17k views

### Using find() in Excel to remove text from string

I'm using find to remove some of the test string in lookup cell value
For instance I look-up the following in cell A2 Application: sales_rep_support and use find =RIGHT(a2,FIND(" ",a2)+4) to change ...

**2**

votes

**2**answers

3k views

### Excel reference named range with text and formula

Is it possible to refer to a named range by combining text with a formula? Say I get a name for a range via INDEX which returns a text but I also want to add something after the text like so:
...

**0**

votes

**2**answers

2k views

### How do I mark a cell if another cell contains data from a list in Excel?

I am working on a spreadsheet and I need to have a way to mark items that have been recently ordered.
Each row is an item in inventory. I have a separate list of item numbers that have been recently ...

**2**

votes

**2**answers

443 views

### Excel - How to build pick lists dynamically

Here's worksheet A:
Parent Kid Age
------ --- ---
John Scott 5
John Lucas 7
John Elisabeth 12
Victoria Jason 3
Victoria Amy 5
Jenifer ...

**1**

vote

**1**answer

121 views

### Transposing columnar record data to rows in Excel

Can I reference a cell in another worksheet like this?
Example:
Worksheet A
A1 Data1
A2
A3 Data2
A4
A5 Data3
Worksheet B (I want to copy by referencing the cells in Worksheet A)
B1 ...

**0**

votes

**2**answers

307 views

### Excel: Use of spaces in filenames/sheetnames with INDIRECT()

Formula is:
=INDIRECT("'Z:\ADMIN PROJECTS\[FORWARD ORDERS.xlsx]"&A1&"'!C$2")
Value of A1 is SEPT 13 in text (to be replaced by another formula soon).
Excel keeps giving me #REF. I'm pretty ...