-1
votes
2answers
18 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
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
9 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. ...
2
votes
2answers
29 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
19 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", ...
1
vote
1answer
11 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
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 ...
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
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
11 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 ...
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 ...
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 ...
1
vote
2answers
35 views

Auto-fill rows from columns in another sheet?

I'm using Google Doc's Spreadsheet at the moment and I'm attempting to create the same view in another sheet. First Sheet: (yes I need these merged cells) Second Sheet: Unfortunately Google ...
0
votes
1answer
26 views

Ignoring value IF “[Value_if_false]”

I am trying to figure out the average difference in monthly sales versus previous years, and I have an IF function within an Average function. I want to skip zero values in the average function if the ...
2
votes
1answer
36 views

What does “--” used in Excel function mean?

I came across this reference in a spreadsheet I'm looking at and don't understand what it means. =SUMPRODUCT(--NOT((ISBLANK(CONSTRUCT)))) Can anyone tell me what the -- in front of NOT refers to? ...
-1
votes
0answers
21 views

Excel 2010, IFERROR, INDEX, MATCH

I've got a sheet consisting of various lines like the following. POH-00006366 CYM001 Cymot NAD 6000 POH-00006366 CYM001 Cymot NAD 7000 POH-00006366 CYM001 Cymot NAD 8000 ...
0
votes
1answer
29 views

Excel 2010 Cell Formatting

I have a crew schedule made up in excel. There are 4 different shifts A B C or D. There are 5 people on the schedule, and the schedule is laid out like this in excel: Each person works the same ...
0
votes
0answers
17 views

How can I calculate the grouped averages and maximums per day for a range of dates and times?

I have a CSV dump describing the utilisation percentage of some servers in my organisation, covering data samples from several times per day. Each server belongs to a group. For each group, I have to ...
1
vote
0answers
42 views

Excel VBA - User Defined Function to turn string into formula

I'm looking for something similar to the solution posted here: Excel function that evaluates a string as if it were a formula? Problem is this does not seem to work with a function in there. This ...
0
votes
1answer
34 views

Excel 2010 Formula: Finding a dynamic column based on string value, then count # of “0's” within that column

I have tried looking at many different potential avenues at solving this; INDEX, MATCH, DCOUNT, HLOOKUP with INDEX & MATCH, COUNTIF with INDEX & MATCH. What I'm looking for is a way to set the ...
0
votes
2answers
40 views

Excel COUNTIF range search returning #N/A

I'm using Excel to search multiple days (my ranges) in a timesheet for hours worked (my HLOOKUP reads the value below the dept number) by multiple members of staff for different departments. I've ...
0
votes
4answers
42 views

Excel - How to merge rows from 2 tables based on a common column?

I am trying to merge 2 worksheets where only one col matches in content. Column"A" in both worksheets have matching values but all other fields are different. Worksheet1 Col"A" colb colc cold ...
0
votes
0answers
37 views

How do I make a function update dynamically when I make changes to the function in a “master” cell?

For example, column 1 would contain input data, and subsequent columns would perform actions on that data. Row 1 would be the master row. All changes to the formulas in row 1 would be duplicated ...
0
votes
2answers
33 views

How can I find max value for a text as unique identifier in Excel?

This is basically for an auction setup in Google spreadsheet. Say there are 26 bidders A to Z and each bid 5 times with different values. I need to find the max value bid by each of them and display ...
1
vote
1answer
41 views

Comparing two strings in excel, add value for common variables

I'm comparing two large datasets containing strings in excel. Column A contains the numbers 1-1,000,000. Column B contains 1,000,000 strings, neatly organized in the desired order. Column C contains ...
0
votes
2answers
67 views

SUM From Sheet 1 Columns to Sheet 2 Rows

Tried searching for a while, didn't see what I was looking for. On Sheet1, I have approximately 100 columns, each representing an item. Each row is populated by a quantity of that item. As you can ...
0
votes
0answers
14 views

How to get cells to default to zero or calculate additonal fees, based on selection from a drop-down list

I am building a Pay Rate Calculator worksheet with a Flat/Base pay rate & numerous Overtime pay rates. I would like to be able to have the "Overtime" pay rate cells to change depending on my ...
0
votes
1answer
33 views

Excel: how to link cell from one sheet to another

could you please, help me? i would like to find out how to do this: i have a sheet 1: with columns: number, name, color. i would like on sheet 2 to have 3 cells, where after entering the number, two ...
0
votes
2answers
32 views

Excel: Random select within table that meets two criteria

Here is an example of my data: Name: Office: Years: John Smith Canada 1 Bob Smith Canada 1 Jake Smith Canada 1 Sarah Smith Canada 1 On another sheet I want ...
0
votes
1answer
34 views

Conditional formatting

What I am looking to do is have a conditional format set up so that if row G contains the value "In progress" "With 3rd Party" or "Identifying" and row H, J or K contain no value then highlight rows A ...
0
votes
2answers
22 views

IF Function on excel - i need to put a formula into one cell using to < < however it isnt working

I need to put in one cell that IF the value of H3 and F3 is lower that 25000 but higher than 0 it will equal 29.95, if false 0 the formula =if(0
0
votes
1answer
87 views

Excel Formula: An Array formula containing VLOOKUP, SMALL, IF, OR

I am trying to create a formula that considers the data from three columns Y, H and V on my tab titled 'Data', returning the smallest value in A that meets that condition, once found Vlookup that ...
1
vote
1answer
21 views

Using column data from another worksheet

I have a worksheet with one column and that has a list of my company names and what I want to do is be able to create a stats worksheet where I can use those company names without changing both ...
0
votes
0answers
42 views

Go to first non-negative cell in a row and divide by another cell

I'm looking to have Excel calculate the financial payback period for me. In words, I'm trying to count and return the first non-negative number in a row and divide that value by a corresponding cell. ...
0
votes
0answers
80 views

Excel 2010 Compile Data from Multiple Worksheets into Summary Spreadsheet

I've compile a structural steel and architectural finishes material take off for an industrial building. I simply tallied the quantity of each length of each shape on a sheet corresponding to each ...
0
votes
1answer
29 views

excel text to specific cell

so i have this problem i want to get a specific cell from text i mean that i have made a table 1,2,3... A,B,C,.... i made a vlookup to search for specific letter in number so if i press 1 in a A3 it ...
0
votes
1answer
32 views

how to create a YES NO notification for time?

I have a formula that shows hours and minutes of how long it took to notify a client. I need to create another cell to say YES if that notification is 4 hours or longer. I currently have the formula ...
0
votes
2answers
63 views

Create New Spreadsheet of data from many Excel workbooks

In retrospect, this is an issue of data-collection, but I want to see if there is an easy way to do it without doing each file. I have about 350 separate Excel workbooks. Each workbook has a bit of ...
0
votes
1answer
28 views

How can I use a formula or conditional formatting to format mutilple criteria?

I am trying to do an excel function, either an IF formula or use conditional formatting to do an AND formula. I need it to produce all three results in one formula, it is: if the date is before ...
0
votes
0answers
59 views

Excel Table Copy Wrong Formula with Newly Inserted Rows?

I am trying to create a simple spreadsheet to track costs. I have a total of five columns with different categories, two of which have formulas. I wanted to be able to insert a new row into the ...
2
votes
1answer
57 views

Excel Help: How to scan a row and return the value of the first cell in that column

I have a matrix of numbers and the matrix has column header -- say like this: a b c d e f 0 0 0 1 0 0 0 0 1 0 0 0 What I want to do is to search for each row for the element 1 and return its ...
1
vote
1answer
41 views

Excel Conditional Formatting Colour Scales highlight group of cells when another cell is a certain value

So I have a document that has 2 drop downs with the first acting as a currency selection and the other acting as a discount selection (0% - 50%). Basically whenever I select from the dropdowns the ...
1
vote
3answers
38 views

Using OR condition in SUMPRODUCT

I have three columns of data - a sum column (A) and then the two columns that form the sum (B and C). Sometimes, just one of the B and C columns is filled in; occasionally the B and C columns are both ...
0
votes
2answers
249 views

Calculating time difference between two date/times from two columns in Excel

I have 5 columns: Start Date, Start Time, End Date, End Time and Duration. I need to calculate the duration between the Start Date/Start Time and the End Date/End Time. I tried just subtracting the ...
1
vote
1answer
34 views

How to remove one column of a calculated item in Pivot?

I have two columns. One with data from this year and the other with data from previous year. I now have added a calculated item in Pivot to display the % delta of these two years. Now when doing this ...
0
votes
2answers
57 views

How do I find the next smallest/largest value without sorting?

I have a table from which I need to pull the next largest and/or smallest value, relative to a value in a separate table. However, I also need to allow the first table to be sortable by any criteria, ...