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

learn more… | top users | synonyms (5)

0
votes
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
3answers
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
2answers
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
1answer
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
2answers
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
2answers
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
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
0answers
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
1answer
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
1answer
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
1answer
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
2answers
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
1answer
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
1answer
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
1answer
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
1answer
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
0answers
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
0answers
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
3answers
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
3answers
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
2answers
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
1answer
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
1answer
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
0answers
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
2answers
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
1answer
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"?