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

**0**

votes

**1**answer

36 views

### Access table data by row index

I have a Table in Excel that is laid out as follows. What I require is a way of accessing a table row by index for a calculation.
To be precise, I have been asked to work out an overall coefficient ...

**0**

votes

**0**answers

262 views

### Getting data from closed files with concatenate formula

Each day a program is creating an excel file for me with some data for the current day.
Like what is the price for products, how many people are available today and things like that.
Based on all ...

**0**

votes

**0**answers

44 views

### How to link N*M cells in MS-excel? (F4 function for $ in a matrix)

I need to link several cells from a matrix.
How can I do that? Normally I use to clikc F4 to insert $ symbols for lines/columns, but in this case I have to put it in each cell. Is there a shortest ...

**1**

vote

**1**answer

64 views

### Conditionnal Formatting with Dynamic Custom Formatted Date

My problem is that I want to color in red a list Custom Formatted Dates when the date is overdue.
For instance, I got two actions:
Buy Bread : Must be finished before 27/01
Buy Milk : Must be ...

**1**

vote

**1**answer

35 views

### How to connect a date with data on a second tab in excel

I am wanting to know how to connect data with a date on another tab.
on the first tab I have the date using =TODAY() in cell A1, then in cell A3 I have Passenger numbers.. On the second tab I have ...

**3**

votes

**2**answers

78 views

### Define value of a column by another column in Excel

This seems like it should be so simple! I need to define column B by column A
If column A is equal to any value in the array "taco,burger,spaghetti" column B should read "food", if column A is equal ...

**0**

votes

**1**answer

1k views

### Updating another Excel spreadsheet from current spreadsheet

I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...

**0**

votes

**1**answer

440 views

### Count from a cell with value to the end of row and dividing total by number of number of months regardless of blank

How do I write a formula that will auto start sum from the first cell with value though to the end which including blanks, and then divide the total by number of cells from the one with value through ...

**0**

votes

**3**answers

116 views

### Saying in Excel: IF this is true, then show me the cell two below

Hy there,
i am trying to create an automatic invoice and probably i am making my life much more complicated than i should.
here is my problem... i have these tables (short version)
Name
Street
Place
...

**0**

votes

**2**answers

2k views

### Compare two columns in two sheets

I have another question, even though the last one i asked is still pending. I have a large data of over 100,000 records. I am using the code below to loop through the data so that any duplicate ...

**1**

vote

**1**answer

96 views

### How to cross data from 2 excel sheets

I'm doing a small internship at a local library.
My mentor requested my help to a do lenghty job, but it has to be done over the week.. So, this is what he requested me: to look up in 2 different ...

**0**

votes

**0**answers

120 views

### Using Excel to calculate time differences with blank cells and calculate monthly totals

I have a sheet that looks like this:
Level 1 Level 2 OFF
Date Time Date Time Date Time Total T Total L2
12/3/2013 12:50 ...

**0**

votes

**1**answer

609 views

### Formula to display text from multiple cells from one sheet in another

I have a list of sales reps and the names of the customers they've sold product to, on one sheet. I have tried VLOOKUP, but it will only return one set of text from the cells. I would like to know, ...

**0**

votes

**1**answer

457 views

### Automatically format currency based on adjacent column - Excel

Using Excel and need a little help with a simple formula.
I know conditional formatting should do the trick here, however, I need help creating the exact formula for my need.
In column A I write the ...

**1**

vote

**1**answer

3k views

### Change Value of Excel Cell when another cell contains specific character

I created a multi-sheet Excel workbook to track workouts. Page 1 is a calendar with clickable links to each other page. Each other page is a specific type of workout. So if you click today's date on ...

**0**

votes

**2**answers

420 views

### CountIf()-data from different workbooks

I am trying to take data from two separate workbooks laid out like so:
picker | lines
--------+------
zf | 4
ag | 22
jw | 8
zf | 7
cr | 39
ag | 10
ag ...

**0**

votes

**2**answers

86 views

### want to extract email address from a string of characters — pretty close [duplicate]

I am close to the end, I just need to add something in to remove the open and close parenthesis and then add a semicolon. I got this far by googling; I just need one more piece to the puzzle. :)
...

**1**

vote

**2**answers

290 views

### Excel: return name of column with highest value in a non-continuous array

I have looked up many answers to find the highest value in a column, and return the column name. However, every answer is dependent on using a continuous array and uses the INDEX function. This is ...

**3**

votes

**2**answers

140 views

### Count first occurrence of one criterion based on a second criterion

I am trying to write an excel formula (or formulas) to count the first occurrence of a value in a given year for each Person in my table. I've searched for related questions on Superuser to no avail. ...

**0**

votes

**2**answers

612 views

### How to add 4 hours in a format cells as yyyy-mm-dd hh:mm:ss and fit year,month and day

How to add 4 hours in a format cells as yyyy-mm-dd hh:mm:ss and fit year,month and day.
Example below.

**0**

votes

**1**answer

321 views

### Insert columns, within a range, based on cell reference

So am trying to tell VBA to insert a certain number of columns between two other columsn based on a cell reference.
I've managed to get excel to insert columns within my range:
Sub INSERT()
...

**0**

votes

**1**answer

69 views

### compare and update spreadsheets

Please I need some help with excel spreadsheet here. I have two worksheets, one holding the old records and the other holding the current record. What I wish to do is to develop a vba code associated ...

**1**

vote

**1**answer

94 views

### Excel 2010 - Calculate dates and return a 0 if it is the current date or date passed

I need to calculate days between today and the 'Last day to offer' and have Excel return a 0 if today is the last day to offer or if the date has passed; otherwise it should return the number of days ...

**2**

votes

**1**answer

306 views

### Excel VBA code Private Sub Worksheet Change Power function not working

I'm making a worksheet where the wind speed defines the potential energy of the location. This value is the basis of a few sheets in the same Excel file. I'm trying to write a code that where I10 is ...

**0**

votes

**2**answers

46 views

### Can I add data from a cell on sheet 2 to a cell on sheet 3?

I want to grab the total of the # hours from 6 cells in a row on sheet 2, and add them and the total of a cell on sheet 3, and display the result in another cell on sheet 3. What I have tried is:
...

**0**

votes

**1**answer

208 views

### Getting average of data with multiple entries for a single date using Pivot table in Excel

I have tried this, but I am unsuccessful.
I have the data dump in an excel sheet (Excel 2010). The first column has month. The second has the dates. The third column has numerical data. I have applied ...

**0**

votes

**2**answers

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

**0**

votes

**1**answer

404 views

### Generate ordered numeric list in Excel based on cell value

I'm not sure if this is possible, but I would like to create an ordered numeric list automatically based on a cell value. For example, if I have a cell with a value of 100, I want excel to ...

**0**

votes

**1**answer

248 views

### Finding IPs covered by Subnets in an Excel document

So I have a giant excel document that contains a list of IPs, but there are also entrys that have subnets. So for example, I might see "IP" "/16" and the first two octets would be the first two ...

**1**

vote

**1**answer

35 views

### Return Earliest Date from a range

I have an excel spreadsheet where I want to pull the earliest date in the column based on the criteria in a different column (for a given tech) into a different sheet. Rows on sheet1 contain store ...

**1**

vote

**2**answers

1k views

### Sum column until a criteria is matched in the adjacent column

I have a table looking like the following:
Col1 | Col2
-----+-----
A | 12
A | 4
B | 5
A | 16
A | 8
B | 91
C | 6
A | 55
-----+-----
C | ???
What I want is if I type C (a ...

**1**

vote

**0**answers

564 views

### Splitting an excel 2010 spreadsheet into multiple workbook files based on column value

I find and clean a lot of contact data, sorted by country, which I am currently using excel to manage. Mainly because it is easier to clean and manipulate the data I find this way, and I can easily ...

**0**

votes

**1**answer

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

**0**

votes

**1**answer

298 views

### Turning students’ marks into grades with excel

I have a sheet contain grade boundaries, For example, the first row says ‘A* | 100 | 90’, the next row is ‘A | 90 | 80 ’, the next ‘B | 80 | 70’ and so on. Another sheet contains a list of student in ...

**1**

vote

**1**answer

318 views

### Get the row and column for a cell in a named range?

Given a cell that exists in a named range, what's the cleanest way to get the row and column numbers for that cell inside the named range? In other words, I want the row and column number relative to ...

**0**

votes

**2**answers

3k views

### How to split comma-separated values by formula, not by script

In A1 I have the following string.
"831 days , 12673 total edits, 12286 approved, 5974 reviews"
I want a formula (not script) that can take this string and return the following in B1:E1
B1=831 ...

**2**

votes

**1**answer

3k views

### Conditional Formatting in excel based on another column

I have an excel worksheet and two columns. I highlight the first column if the value of the cells are less than 0.
Now how can I highlight the second column cells based on the first columns that are ...

**0**

votes

**1**answer

16 views

### Calculating non duplicates

I have below data in three columns
A B C
07002 72J217.4 72J217
07002 72J229.5 72J129
07002 72J229.6 72J129
07002 72J229.7 72J129
07006 61E001.5 61E001
...

**1**

vote

**1**answer

143 views

### Need a Excel script to identify email addresses that contain a different variation of the first name of the contact

I have an Excel spreadsheet with 50k+ rows of data, where we are trying to identify within the rows of data where the email address has no match of the first name, etc.
We are basically trying to ...

**0**

votes

**1**answer

90 views

### Excel - pulling data from certain sheet of different workbook

I am trying to pull pricing data from a larger workbook (masterprice.xlsx) for a new basic "price quote" workbook. The large workbook has a different sheet for each model name. In my new workbook, I ...

**0**

votes

**0**answers

63 views

### Displaying some cells data depending on selected text from other sheet

I have an excel sheet which looks similar to below and with some more columns.
Area Branch Empolyees Products Shelfs
Area1 Area1 Branch1 10 Product 1 10
Area1 Area1 ...

**1**

vote

**0**answers

18 views

### transposing a row with formulae & values both in several cells to another sheet in same file Excel [duplicate]

Consider the data arranged as follows in my Sheet1. I want the fifth row to be transposed in the Sheet2.
When I reference the first item from B5 in Sheet2, and then drag the fill-handle(?) ...

**0**

votes

**3**answers

120 views

### Put two Excel formulas together

I have some particular data in sheet1 and some other data in sheet2.
I need to compare a specific value(Var1) from sheet1 and return a specific value from the sheet2, and return an output for the ...

**2**

votes

**3**answers

231 views

### How to make excel return a date from a column corresponding to the last 'YES' in a different column

I'm trying to develop a spreadsheet tool to help track the training currencies of professional aircrew. In order to be considered 'current' in all training, aviators must accomplish a variety of ...

**1**

vote

**2**answers

51 views

### summing values that match text critera

I have a need to create an Excel Sheet that can compare a column of employees' names to a second sheet that has a certain number of monetary figures, as such:
list of specific employees who's profit ...

**2**

votes

**1**answer

56 views

### What is the cleanest way to write an array formula to facilitate the addition of more rows?

I have a spreadsheet with several columns that use array formulas, specifically referencing other columns of data. I started off using the A$1:A$100 syntax (if 100 was the last row of data in the ...

**0**

votes

**1**answer

1k views

### Excel 2007 - Using combination of Countif and Indirect

I have numeric data in column P in 50 different sheets ( each sheet has different person name). I have one summary Sheet which has names of all the worksheets in column A. In summary sheet column B ...

**0**

votes

**0**answers

63 views

### Mail Merge | Excel transpose or any other solution

i have an excel with 3 columns: domains, clients, email clients
each client can have more than one domain.
I want to mail merge all my clients to make a change in the dns. but i don't want to send 5 ...

**0**

votes

**2**answers

61 views

### Excel 2007 - refering value from other worksheet in another function in excel

I have two worksheets. I have data in Sheet2 in column A for which i want to use average function. In sheet1 i have values in only two cells. B1 cell has value 5 and B2 cell has value 15. Using these ...

**0**

votes

**1**answer

32 views

### How do I replace two or more instances of a character in an Excel string?

For example, what function or combination of functions would I use to convert string 1, "this_is_a_string" to string 2, "this is a string"?