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

learn more… | top users | synonyms (5)

2
votes
1answer
226 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
3k 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
380 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
2answers
4k 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
431 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
131 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
4answers
5k 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
3k 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
3answers
242 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
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
1answer
168 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
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
3answers
1k 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
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
50 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
1k 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
308 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
236 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
599 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
52 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
176 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
3answers
86 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
137 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
511 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
55 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
3k 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 ...
2
votes
2answers
372 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
383 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
97 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
170 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
132 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
125 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
1answer
94 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 ...
0
votes
1answer
706 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
2answers
62 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
1answer
988 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
1answer
62 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
1answer
34 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
1answer
151 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
2answers
1k 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 ...
5
votes
2answers
9k 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
2answers
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
2answers
1k 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
2answers
390 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
1answer
103 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
2answers
270 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 ...
1
vote
1answer
107 views

How can I use excel to create a multiple IF function?

I have a spreadsheet comparing numbers of programmes sold by my company. I want to create a formula that will total several variables. In one column I have the month then whether it is a "Finance" ...
1
vote
1answer
235 views

Displaying Other Values Via COUNTIF in Excel

Basically I have a sheet like this: A B C D Name (From File 1) Name (From File 2) Phone Number Countif Walt ...