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

learn more… | top users | synonyms (5)

0
votes
1answer
20 views

How to match Reg No. in one workbook to another

I want to find out a formula to match Reg No. for two workbooks. First work book is a master book I update everyday almost, next workbook I have employees name and ID numbers only, nos are in ...
1
vote
1answer
198 views

Dragging a cell reference in intervals

I have data as follows (simplified) in two worksheets on a document: Sheet 1 data: A1=1 -- B1=(Blank) -- C1=(Blank) -- D1=2 -- E1=(Blank) -- F1=(Blank) -- G1=3 ... Sheet 2 data: A1='Stage ...
0
votes
0answers
165 views

Excel: Finding comma delimited values in reference range

I have a two-sheet excel file in which I want to see if specific values are present in some column, based on comma separated values in some other cell. The idealized case would be: Sheet1: Cell A1 ...
-1
votes
2answers
301 views

Spreadsheet for Daily Equipment Hours

My goal on this is to keep track of equipment hours and have a daily report to reflect total hours and run time. I want to make a file with 31 days of worksheets in the workbook for the complete ...
0
votes
1answer
188 views

Excel count all rows that contain certain values for certain fields

I have a sheet with a couple hundred rows. Each row has an 'owner' and a pass/fail field. I am making a second sheet that has statistics for the users. So for each user it will count the number of ...
0
votes
1answer
52 views

Summing of adjacent cells in a single column that are <>0

I have a list of numbers (8780 values in one column -- a sample is posted below) some of them are <> 0 and some of them are 0. List of numbers: 0 0 0 0 0,127227198 0 0 ...
0
votes
2answers
71 views

How to put conditional formatting in a cell from another excel worksheet [closed]

It's hard to describe what I want, I'm not really familiar with Excel condition terms but it is basically just like this: Worksheet= Dr. castro patient consultations A B C D E ...
0
votes
0answers
84 views

“Filter by Selected Cell's Color” is so slow

I have a row includes like 60-70 thousand lines. 30.000 of them colored. And I'm trying to filter them by "Filter by Selected Cell's Color" but it's so slow. It take forever to filter just 70.000 ...
0
votes
1answer
107 views

How to CONCATENATE with variable rows

Sorry in advance if I don't explain this very well, definite newbie here when it comes to spreadsheets. I have a Text only spreadsheet with 4 columns, first 3 are Student's Last name, First name, and ...
0
votes
1answer
361 views

Can Excel's Find formula have multiple find_text criteria?

I have a list of names on a NAMES tab range A1:A100. On another tab 'Data Export', in column A I have an export of thousands of comments where one of those names from the NAME tab can be found within ...
0
votes
1answer
86 views

Apply a function/formula to a range in excel

I have many operations to perform on a set of data in excel. The data is in columns, and the functions are a lot of variations on things like =(A1*C1/B1)+(A2*C2/B2)+... Right now they are spelled ...
0
votes
2answers
93 views

vlookup with blank look up value

I have a Vlookup running and I want it to return 0 if the look up value is empty. example: =(VLOOKUP(A6,F:G,2,0)+1)*C6 +-------+--------+-------+-----------+-------+-------+ | | | ...
0
votes
1answer
2k views

Indirect function for worksheet names in Excel?

I am wondering if there is an equivalent to the indirect function for worksheets or workbooks instead of cells. What I mean by that is I am updating a rather large and unwieldy workbook for payroll ...
0
votes
1answer
90 views

Can't compare a cell to a column in excel

I'm trying to compare a column to a cell in excel several times. The columns are copy pasted from a PDF (only way to get the information). The results are bad, with trailing spaces, some completely ...
0
votes
0answers
40 views

Excel changes formula to numbers randomly

I have had recently a number of instances where Excel has changed formula to the numerical results of the formula : Formula Formula Formula Formula Number Formula Formula Formula I have ...
0
votes
1answer
32 views

Excel assigning different dollar values and adding them

I am creating a spreadsheet and I want to assign different dollar values and add them together. For example I want B to represent 10.00 per hour for each hour I type in then I want C to represent ...
1
vote
2answers
928 views

Excel compare cells in columns A and B, show in column C cells only unique to column A

I know there's a formula for this in Excel but I can't get it right. I've got a list of 7000+ names in column A, and a list of 4000+ names in column B. Some are duplicates, appearing on both column A ...
0
votes
1answer
220 views

Return the maximum value to the left of the cells containing a given string

For an Excel spreadsheet, I am trying to find the maximum number that corresponds to a cell with a certain string. For instance, if the data is: | A | B | 1 | 3 | Red | 2 | 5 | Blue | 3 | 1 ...
0
votes
1answer
251 views

Count instances of a letter in a row if a certain cell in that row contains a specified date

I have a spreadsheet containing a column of dates (week commencing) with columns of data to the right of it, and a conditional formatting rule that highlights this week's row based on the date. I ...
0
votes
1answer
29 views

Merge 2 sheets, always keeping the value from sheet 1 if there are duplicates?

I have 2 Excel sheets that I'd like to merge. They are an inventory file and are roughly 5,500 rows each, with each row being a product and each column being an attribute. The first column is the ...
1
vote
1answer
130 views

Excel: What formula to use to return the minimum or maximum of a set of lookup values? [duplicate]

I'm looking for a way to calculate the values shown in cols D & E in the below screengrab. Column D contains the earliest date from column B where the name in column A matches. Similarly, column ...
1
vote
1answer
436 views

Excel 2010 VBA only affecting last row in range

I have some VBA code written in Excel 2010 to force padding around the rows in a table, but part of the code only affects the last row in the range. Here's the full code: Private Sub ...
-2
votes
1answer
49 views

I want to have one cell that can result in 3 different values based on it's own value

I am attempting to solve for a business problem via an excel document. In this document, I am trying to solve for: "Within a cell, If the value = Option 1, then Answer 1. If the value = Option 2, ...
2
votes
2answers
97 views

Looking up last two entries in a column

This might be impossible but I hope not. I am trying to pull the last two entries from a column and add them together and then divide by two to get a percentage. These entries are updated daily so the ...
0
votes
1answer
327 views

Excel - find and remove text, surrounded by varying characters

I've got an Excel file with ~5,500 rows of inventory items. One cell in each row is a description field and contains HTML. Some (but not all) of these cells contain a link formatted something like ...
0
votes
1answer
110 views

Use INDEX and MATCH function to find value and get a corresponding value from the returned rowindex

I want to combine columns [country code] and [admin1 code] with a "." in between and then lookup that value in Sheet1 in the [admin1code] column. When a match is found I want to get the corresponding ...
0
votes
1answer
111 views

How to have excel paste a value in a specific cell according to another data table

I'm working with excel 2010, I have an excel sheet with two tables. The primary table contains a calender and each calender day has a specific numeric value in the cell below the date. The numeric ...
0
votes
2answers
55 views

When to use “0” and when to use “1”

What is difference from 0 to 1 in the following formulae? Example: =IF(ISERROR(MATCH(B2,A:A,0)),"No Match","Match") =IF(ISERROR(MATCH(B2,A:A,1)),"No Match","Match")
2
votes
1answer
172 views

When copying formulas from one workbook to another, how to make the formula to use the data from the new workbook?

OK, I know it sounds complicated, so I will try and elaborate: I have a lot of files (one created every day) - let's call them W1, W2... Every file has the same number of sheets and their names are ...
-1
votes
1answer
289 views

Share cost of activities between participants in spreadsheet

Say you have a spreadsheet you use to fairly share the cost of activities between people (example google doc): A B C D E 1 Activity Cost User1 User2 User3 2 1 ...
2
votes
1answer
2k views

Horizontal Index Match Excel 2007

i have a list of customers rows 1-500 column B, with their purchases showing in columns u-AB and based on the maximum purchases done under a specific column (u-ab), the customer will be tagged as ...
0
votes
2answers
697 views

Excel 2010 formula, How do I write this formula? (VBA)

I need a bit of help from the more experienced Excel VBA users. Basically I have 2 groups of 3 cells (6 cells total) If one group of 3 cells is filled in it needs to accept it and not return a ...
1
vote
2answers
50 views

Testing a cell for multiple condiction before calculating value

I am working on a workbook that has a formula =IF(ISTEXT(G4),0,IF(ABS((G4/H4)-1)>=0.2,0,1)) Now if H4 has a value of zero I get the Excel error #DIV/0!. I want to use the ISERROR formula to ...
0
votes
1answer
37 views

Removing whitespaces from data in a column

I have two columns of data in excel spreadsheet say column A and B. There are more data in column A than in column B. I am trying to find which of the data in column A is not present in column B. I ...
1
vote
1answer
19 views

Can I apply conditional inputting on Excel?

In excel,I want to input unique text in every cell of a column.suppose I adding name of some people on that column,and I don't want to add one persons name twice by mistake.so I want that if I input a ...
1
vote
2answers
4k views

How do you make a set of cascading dropdown lists in excel?

I'm working on a system to try to take inventory more quickly instead of filling in each row by hand. The reference data is several hundred rows. The first 3 columns in the reference sheet are Class ...
3
votes
2answers
138 views

Why is this Excel formula returning two different values?

I am attempting to use Excel (2013) to look up whether the value 1 turns up in a given set (using the CHOOSE() function, then, if so, printing the value in the cell directly to the right of it using ...
0
votes
2answers
87 views

Excel If formula

I am trying to run a simple formula in Excel: =IF(V3>419, "Pass", IF(V3>459, "Merit", IF(V3>499, "Dist", IF(V3>519, "Dist*","Fail")))) I have 2 columns; Overall and Grade Overall ...
0
votes
2answers
89 views

How can I return a list of specific words found in a cell's contents?

I have two columns, one with a string of text (A1), the other blank (A2). A1 looks something like this: "Briefs on Southern Africa, Rhodesia, East of Suez (Including Soviet Naval Expansion), ...
-1
votes
1answer
38 views

Conditional Formatting from Helloh, for a Noob

I'm going to try my best to explain what I need to figure out. Also, for you to understand exactly how mind bending this is for me, I haven't used an Excel formula since 2005/2006ish. We're doing a ...
0
votes
1answer
665 views

Advanced filtering leaving on unique values and removing duplicates

On the spreadsheet there are two subsets of data that are separated by color based on where I pulled the information from. Within these subsets are duplicate entries. I do not want to simply remove ...
1
vote
1answer
267 views

Can you define an array formula across multiple columns?

I have a table of Stores (well, for this example anyway): Store_ID Store_Name ... 1 High Street 2 Low Street ... I have another table of Employees: Employee_ID Store_ID Is_Active 1 ...
0
votes
1answer
519 views

Excel - Script that search a sheet for a cell containing specific text, and replace the text with a specific value

I want to split a cell with data into other cells based on the data in the original cell. The data from the original cell looks like this (all content in a single cell): ...
1
vote
1answer
27 views

Replicate a Calculation Sheet for each customer

I created a LibreOffice sheet, with the detailed list of expenses. And a second list with the names and debts of each neighbour. However, I'd like to generate a personalise replica of the first ...
1
vote
2answers
207 views

How do I refer to a cell in another worksheet with a long name?

As explained in this other Answer, SheetName!A1 = cell A1 in the worksheet called "SheetName" (without quotations). Say the name for my worksheet is very long. Without shortening "SheetName" and ...
0
votes
3answers
14k views

Formula =IF(VLOOKUP(…)=Cell;…) does not work [closed]

According to this and my own testing, using VLOOKUP can be compared to a specific value inside of IF. E. g.: = IF(VLOOKUP(...) = 20, [then value], [else value]) works. But if I compare the result ...
2
votes
1answer
709 views

Excel/Libreoffice returning TRUE for IF() instead of expression

See the following situation: For some Here cells A1:E3 have plain text data (no formulas), and C4:E4 have the same formula (i.e. typed in one cell and then autofilled to another ones). I wanted ...
2
votes
2answers
5k views

How to add or subtract to, or increment, column letters in Excel?

I'm looking for a simple versatile formula that allows simple mathematics to be done to column positions, returning the appropriate column letter code. For example: Given column A, set to +1, it ...
1
vote
1answer
104 views

Excel 2013 Column value determines another columns letter assignment.

I have 5 columns but I need to define column E by column A, If column A has a number that isn't zero in the array, column E should read "Overdue", what formula can I use?
1
vote
0answers
107 views

Using getpivot in excel to create flexible reports based on user inputs

I am trying to make a report in Excel that sits on a very large set of data (set up in proper pivot form). I want to be able to select a value for a few fields (or select a value from 1 field but ...