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

learn more… | top users | synonyms (3)

0
votes
0answers
12 views

How to fix the sheet name part of an Excel cell reference when the whole sheet is copied?

I want to reference another cell in my worksheet in such a way that when I copy the sheet, I am still pointing to the cell on the original sheet. If the reference is to a cell on a different sheet, ...
1
vote
1answer
21 views

How to efficiently reference an entire row in Excel?

I am working on a budget spreadsheet in which I have to pull in cell data from another external spreadsheet. I have to do this for several different sheets with massive amounts of data in the sheets, ...
-1
votes
2answers
19 views

Excel create reference to whole column throught a formula

I have a spreadsheet where I need a reference to a whole column, catch is that I don't know which column ahead of the time, so I use MATCH function to find that out. Once I find the right column I ...
0
votes
1answer
14 views

Comparing 2 Cells and then based on their values Multiply another cell

I have a spreadsheet for my insurance business. I want to have a spreadsheet to determine my commission on a monthly basis. So I have one column for Line of Business (Auto, Home, etc) and then another ...
1
vote
3answers
68 views

How to construct a conditional COUNTIF formula

I have a worksheet where in D I have time (0600,1800,2400) and in R I have disposition e.g (accomplished, pending etc). What I want is a formula to count the disposition in column R as per the time ...
1
vote
1answer
42 views

How to combine data from two sheets and conditionally format rows based on sheet of origin

I have 3 sheets in Excel "Open", "New", "Combine" "Open" Sheet (Old list) Ticket ID | Priority | Department | Status 36009 | 1 | Finance | 01-Open 34781 ...
-1
votes
2answers
41 views

Excel possible to fill two cells with one if-formula

I have searched on the internet to look if this is possible : IF(logical test ; true value ; false value ) This is what i try to make for example A2 =IF( B2 = 20 ; "True" & give C2 value ...
0
votes
1answer
25 views

Can't get VLOOKUP working with roster spreadsheet

I have an Excel spreadsheet set up as a roster. The way the roster is setup is for the ease of the person creating the rotation. This roster is not easy to read for the staff. Mon ...
1
vote
0answers
12 views

ArrayFormula + Average giviing me div/o error

I have the formula on B2 if(D2="", "", AVERAGE(D2:AF2)) This gives me the average of all numbers in the row, and takes off the ugly error when there is no numbers. I tried spreading this formula to ...
0
votes
2answers
28 views

How to use different formula in case of en error?

I want to run the following formulas on a cell, in this order, in case the previous formula returns an error. =MID(P2,SEARCH("q=",P2)+2,SEARCH("&",P2)-SEARCH("q=",P2)-2) ...
0
votes
1answer
20 views

Excel: How to strip search terms from a cell (referring URL)

I have cells that contain a referring URL of visitors who entered my websites. this URL always has a different lenght, but it's constant format is such that the search term always shows up between p= ...
1
vote
1answer
11 views

Managing different currencies in Excel budget sheet

I need to enter the values as I write cheques (ie $100 USD and $100CDN, not $111.55 USD and $100CDN) otherwise it will be a nightmare for me to match entries to invoices. I could create a column for ...
1
vote
1answer
10 views

Why does ADDRESS function return an error across different worksheets?

When I try to count cells that match my criteria using the address function, I get mixed results depending on which sheet the formula is nested. For example, if I use the following to count the ...
0
votes
1answer
18 views

Excel Values Require Enter within Cell to recognize as number

I have a worksheet where I am getting direct output from STATA that has a weird character format that included " =[ number ]". I put in a new column to strip the characters and leave the numbers. ...
0
votes
1answer
23 views

Importing multiple CSV files

I have to run a daily stock analysis where I pull multiple .csv files, and then have to go through an annoying task of deleting unnecessary fields from the .csv's. Only then can I start compiling the ...
2
votes
2answers
30 views

How to find the last value in a column in excel?

I am tracking rooms booked in a hotel on a daily basis in Excel. Every day I input the number of rooms booked in a column to show a trend of pick up over a period. I'd like to return the value of ...
0
votes
1answer
20 views

Count Row and Column in Pivot Table

Hi I have a sheet that collects the following ++++++++++++++++++++++++++++++++++++++++++++++++++++ + Name + Address + Borough + Postcode + Date Added + ...
0
votes
1answer
44 views

VBA: How to replace entire Excel cell value basing on its content?

I have an Excel Spreadsheet with some names in a cells. There can be as one name per cell as multiple names in one cell. For example: Cell A5 contains "Joe", cell BD54 contains "Joe;Harry;Molly", ...
0
votes
1answer
11 views

Excel INDIRECT function worksheet names

So I have a series of worksheet in my workbook names A1-A4,B1-B4 etc. I'm trying to make an index page that pulls some of the data from each worksheet into a table but can't seem to get the INDIRECT ...
1
vote
1answer
21 views

How to compare two sheets and get the common rows

I have a excel file which has two sheets and below is the first sheet and second sheet. I want to compare the two sheets (ex: each row of first sheet with with all the rows of second sheet ) and then ...
1
vote
1answer
34 views

Matching and updating columns in Excel from two documents

I'm completely green with Excel, whenever I think there has to be a quicker/easier way I usally just GOOGLE it and sure enough I find an answer, fab! Now I'm trying something a little more tricky and ...
1
vote
1answer
13 views

Is there a way to do a partial text vlookup?

I am trying to vlookup partial text which will return a specific value if found within my data. I have a list of postcodes (zipcodes) and would like a vlookup to look up the first part of the postcode ...
0
votes
1answer
11 views

how to get the count of column data based on some condition

I have the excel with two columns like below ID Address GID1 address1 GID2 address2 GID1 address1 GID8 address1 GID2 address8 so i want to get , for all same ID's , count ...
0
votes
1answer
29 views

Sort & Ranking Functions in Excel for Time AND Number Values in one Column

I have a spreadsheet that is being used to track and score a fitness competition, each workout has a time cap, so athletes that complete the workout under the time cut off are given a time as their ...
1
vote
1answer
27 views

Excell formula for multiple pasts without repeating previous cells

I have a data set for 12 years of 4,380 days. I want to take the sum of each week. when I make a formula {=SUM(A1:A7)} and drag to drop button to the next cell it shows {=SUM(A2:A8)}. This is not what ...
-1
votes
2answers
32 views

From date of last event, calculate number of days until next regularly scheduled event

I need to calculate the number of days since last occurrence. For example, I have to go to doctors every 90 days. My last doctor visit was on 1/9/14. I need a formula to tell me how many days until ...
0
votes
1answer
26 views

Count Unique values based on criteria within 2 columns

I hope someone out there has the brains to help me with issue. I have a spreadsheet that has multiple columns and duplicates. What i want to do is count all the unique values in column A, only if ...
0
votes
1answer
30 views

Make formula impossible to find / read

I have developed a spreadsheet for performing various calculations which our employees will complete and then distribute to clients / competitors. To protect the "intellectual property" of the ...
0
votes
2answers
43 views

Converting comma seperated values in multiple rows

I am in urgent need of importing huge data from excel to SQL. one of the excel sheet contains data as follows. ID Name Category Section 1 Books Stationery KG1,KG2,1 2 Pencils ...
0
votes
0answers
44 views

Search rows containing a certain value for other values and comparing them

Here is what I'm trying to do: This spreadsheet contains 40k+ component serial numbers. These assembly serial numbers are in column c. These components contain sub assemblies and those also have ...
0
votes
1answer
14 views

Formula to determine available options based on prerequisites

Example: A spreadsheet for a single studient has four columns: Course|Completed?|Prerequisites|Available The "Course" column lists the names of courses. The "Completed?" column indicates a "yes" if ...
0
votes
1answer
20 views

Excel - Find a specific column in a CSV file, and copy to the current sheet

I get a regular data export in a CSV format, which I like to pull into excel and report on via helper columns and pivot tables. I do this by linking to the csv file, and copying the cell contents ...
0
votes
0answers
16 views

In Excel 2013 how can you make every distinct number value have a matching distinct colour?

I have a table of computed numbers in excel (different every time it is computed), for example: A B C D E 1. 9 8 9 9 1 2. 7 1 1 1 9 3. 1 6 5 2 9 4. 6 6 3 2 2 5. 3 3 2 3 7 In the example I have ...
0
votes
0answers
22 views

I need to import data to an excel sheet

I originally went through a spreadsheet and created a new column called regions next to an existing column that had suburb data in there. It took me ages as there was 8000 rows of data. Anyway its ...
0
votes
0answers
12 views

How to embed an icon from one worksheet to another, within the same workbook

Does anyone know how to embed an icon referencing another worksheet page onto another worksheet ... all within the same workbook. I have been trying this out but keep getting information on how to ...
0
votes
1answer
30 views

Can I use a worksheet as a function in Excel?

I have a worksheet set up such that I can input a value, do some math to give me a bunch of values, repeat math a few times, and add up all the values and return an output value. It is complex enough ...
0
votes
2answers
31 views

Auto numbering based on repeating cells

I need help auto numbering a spreadsheet that has submissions with inventors but some submissions have multiple people listed, but need to be numbered the same. e.g.: No. Count Name 0011 ...
0
votes
3answers
38 views

Can't work out the logic for formula

I have two cells and I want to write a formula to check two conditions and return "Error" if either condition is met. The conditions are; 1.Are both the cells empty? 2.Are both cells NOT empty? (It ...
1
vote
1answer
11 views

Compare data cells from two independent file

I would like to compare the cells values from two different Excel files. I have already done a program which is working if it's on the same sheet. Now, I would like to "external" the first cell. The ...
-1
votes
1answer
20 views

check one column against another and return a third

I'm trying to create an import csv. the sheet has the following data ImportID, 1stname, FirstName, inport_ID 1, Bob B, Bob T 2, sue, Bob B, 1 3, Jill P, ...
0
votes
1answer
15 views

Copy a specific cell on all sheets to master sheet

I have a master sheet with the format as below: Master sheet Column A Column B Column C No Description Price And multiple sheets with the format below: Sheet ...
0
votes
1answer
22 views

Match first and last name to 2nd column then output corresponding 3rd value

I'm trying to create an import csv. the sheet has the following data ImportID, 1stname, lastname, FirstName, LastName, inport_ID 1, bob, Abraham, bob, Abbott, 2, sue, Abraham, bob, Abraham, 1 3, ...
0
votes
3answers
48 views

EXCEL - How to have a formula output and user input on the same cell?

I would like help from someone as I believe this problem requires VB. I basically have a CELL(A4) where people will be entering values to find out the corresponding calculations displayed below. ...
1
vote
1answer
22 views

In Excel, how do I replace all instances of text in a cell if any of the text matches values from a range of cells)

To make things easy for users of this workbook, I've setup column J with data validation (pulling values from Sheet2) that allows for multiple selections of event types (Academic Calendar, Art ...
0
votes
2answers
23 views

Indexing a sum of values by row from multiple columns

I'm struggling with this one... In row 2 I have week numbers up to week 9 in this example. Row 1 has a formula that determines to which month a week belongs. In this example, week 5 is in month 1 ...
1
vote
2answers
48 views

Perform a calculation n times and sum the results (without VBA)

I'm looking for a way to sum the result of a calculation performed a given, arbitrarily large, number of times. Suppose I have 15,000 customers each of whom will spend between $100 and $200, and I ...
0
votes
1answer
8 views

Formula to incorporate Highlighted text & Loop function

I'm trying to analyze over a 1000 line items, with different information in all columns ,although certain columns might have duplicate information. I can do conditional formatting and highlight the ...
0
votes
1answer
11 views

Microsoft Excel 2013 on Windows 7, creating cost based on drop down values

Hi I am need of some assistance and was wondering if anyone can help me. I am creating a spreadsheet for quoting jobs on site and am stuck with a particular part. I have a cell containing a drop down ...
1
vote
2answers
50 views

Look up a value in a list and return ALL multiple corresponding values

The basic answer to my question is provided here: How to look up a value in a list and return multiple corresponding values. However, once the formula is entered, I need to drag down, or copy/paste ...
-2
votes
1answer
22 views

Need an “IF” function that shows whether the difference of a simple subtraction is greater than a certain value

I have values in cells A3 and A2. I will be subtracting the value of A2 from A3. What I need is to be able to highlight or flag the result if the difference is greater than an absolute value of .01 ...