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

**0**

votes

**0**answers

3 views

### Most efficient function(s) to compare and extract rows from two reports?

I work for an international shipping company processing hundreds of containers each day. I generate a report (from an AS 11g servlet, FWIW) in the morning, work through it based on the 2 Modes of ...

**1**

vote

**1**answer

17 views

### Sumifs function where I want to sum a range not just a single row or column

I am stuck.
I am entering timesheet date into an excel sheet:
Column A - Resource Name
Column B - Project Phase
Column C - Resource Team...
Column D to AAA is a date
Therefore a single resource could ...

**0**

votes

**0**answers

116 views

### Reference last cell not only in a row or column, but in a range

all users, especially Jerry Beaucaire.
I was reading this link when I stumbled upon his answer
Here is the link
How to refer to the last cell in a row in Excel?
And Jerry Beaucaire answered it like ...

**0**

votes

**0**answers

22 views

### How can I copy new data with additional rows, while including formulas?

As an example I have data in the range A1:D55, and in Column E I have a formula using VLOOKUP in reference to column A.
I pull the data again for A1:D55 and the values change (titles remain the same) ...

**-1**

votes

**0**answers

19 views

### Excel Formula will not update [on hold]

I have a formula in excel that will not update automatically even though it is set to do so. If I hit F9 it still will not update. I have to be in the formula bar and hit enter for the update. Any ...

**2**

votes

**3**answers

6k views

### Excel-MATCH function doesn't work on pasted values

I'm trying to find the positions of the values(text) in one column in another column. I ran the function: =MATCH(B1, A:A, 0) and I get a #N/A result. But this result is incorrect...as I clearly see ...

**23**

votes

**8**answers

317k views

### How to compare two columns and find differences in excel

I have two columns in Excel that I want to compare and find the differences between them.
Suppose
Col A has 50 numbers i.e 0511234567
Col B has 100 numbers in the same format

**1**

vote

**1**answer

39 views

### How can I replace forbidden characters in a file names before saving in VBA?

This will be very simple for somebody who knows VBA better than me. I used a code original provided by user Nixda on this forum to split excel worksheet into multiple CSV files based on column value ...

**0**

votes

**2**answers

34 views

### Excel: Validation rule with value that should be in the list of another column without blank

On excel, I've two tabs: one listing all people participating to an event, with a lot of details. I've a "FullName" column which is basically defined by:
=[@LastName]&" "&[@FirstName]
This ...

**0**

votes

**1**answer

31 views

### Multiple Formulas in one Cell for hotel

I am trying to create a housekeeping spreadsheet where when a room number is entered into a cell a 0 or 1 (0 is for studio and 1 is for a 1 bedroom) is automatically populated in another cell. I have ...

**4**

votes

**2**answers

4k views

### How do I download a stock price in Excel 2013? Is there an equivalent to Google Doc's GoogleFinance spreadsheet function?

In a Google Docs spreadsheet, I can use this cell formula:
=GoogleFinance("GOOG", "price")
to download the latest price of a stock. Is there an equivalent function in Excel 2013?
Earlier versions ...

**1**

vote

**1**answer

34 views

### How to count months in intersection of two date ranges

I need to calculate the number of months in the intersection of two date ranges.
For instance, in Column A I have start dates of employment
12/1/1998, 1/1/2003, 2/1/1995
and in column B I have ...

**0**

votes

**1**answer

15 views

### MS Excel - Cumulative sum of a column based on “id values” in another column?

I have been trying to figure out how to make a formula that calculates cumulative sums of rows in a column based on their id values in another column but still haven't found any solution for it. I try ...

**0**

votes

**0**answers

11 views

### Dynamic Values in a formula? [migrated]

I have a google sheet where I am using this query to pull data from another sheet :
=QUERY(IMPORTRANGE("SHEETLOCATION","Master Customer!A1:u10000"),"Select Col3 Where (Col18='Survey Complete' and ...

**-4**

votes

**0**answers

12 views

### To get the result in excel [on hold]

I have few character with some gap in each charaters.
Just wanted to get all the characters before the gap
Eg:- TSFT40009 TRMG23789
MNCDFEWIPZY MNKYUNIM20.

**0**

votes

**1**answer

18 views

### Grab cell data and use in “IF” statement

Is it possible to grab a cell containing a date and paste that date in an "IF" statement containing text, similar to the below:
...

**-1**

votes

**1**answer

27 views

### Excel 2010: How do I reference a specific field using a defined column name and a row number?

I'm using Excel 2010 and am creating a workbook to define details about products in a store (including category, price, accessories). I also want to define default category details to use if the ...

**-1**

votes

**0**answers

34 views

### Which Excel functions are array formula friendly? [on hold]

What is the comprehensive list of all Excel functions that accept arrays as an input and so are 'friendly' to use in an array formula?
The unfriendly ones seem to collapse down the output into a ...

**0**

votes

**1**answer

30 views

### SUMIFS w/ multiple Criteria

Base on the Emp ID and the Pay End date I'm looking to sum up the Total Earnings and if it is equal to or higher than the WC Cap in the exposure column I'd like to return the WC Cap value. If it's ...

**0**

votes

**3**answers

2k views

### Compare multiple columns for any two or more cells within the same row having the same content

I need a formula to compare multiple columns for any two or more cells within the same row having the same content. If that is true, then display "TEXT A" (can be anything such as "TRUE"). If all of ...

**0**

votes

**0**answers

32 views

### How to Sum based on two critera, date range and name

I have a table that has timesheet data for a number of users.
Column A has the resource Names. Row 1 contains each week of the year, starting with the 5th of Jan.
The table contains hours booked by ...

**0**

votes

**0**answers

22 views

### finding the difference between two times in libreoffice

I'd like to know how to solve the problem:
in cell A(n) is the first time
in cell B(n) is the second time
in cell C(n) is difference of times
I don't want to put each time into cell C(n) function ...

**0**

votes

**1**answer

28 views

### Conditional sum in Excel or Libreoffice calc

As an example, in the table above, I want to populate the empty column titled 'Totals' with the corresponding sum of all the Values in the left tables 'Values' column belonging to the category ...

**0**

votes

**1**answer

16 views

### Converting a column list from A2:A142 to a list with commas into cell B1; holding a format as “0000” with leading zeros

This code works to convert the column list to a concatenated list with commas, however the format doesn't have leading zeros while the VBA loops in B!.
Dim i As Integer
Dim s As String
i = 2
Do ...

**1**

vote

**3**answers

7k views

### Find a non-case-sensitive text string within a range of cells

I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's ...

**0**

votes

**1**answer

33 views

### How to have the column letters in a formula automatically increment

I have a table with multiple tabs.
On Tab "Reviewers", I have the reviewers' names in column A, and the items (documents) being reviewed in columns G through AG.
Each column contains the number of ...

**-4**

votes

**1**answer

25 views

### Calculate difference between numbers in 2 columns row-by-row in a spreadsheet [closed]

I have 3 columns in a Google Docs spreadsheet:
A) Inventory purchased price,
B) Inventory sold price,
C) Difference between A and B (i.e. profit or loss)
Data is going to be entered manually into ...

**1**

vote

**1**answer

18 views

### Splitting Large Strings in Excel an Arbitrary Number of Times

Suppose I have a 50 character string and I want to split it in to arbitrary chunks. For example I might want to split it into chunks of 4,5,3,8,2... and so on. What's the easiest way to do this in ...

**0**

votes

**2**answers

29 views

### Use INDEX to lookup multiple values in a list

I am using this formula to Look up all instances of CUCUMBERS in column B on the first sheet of my workbook: "ITS" I have identical to column B in Column X.
So far this works great, just as I want ...

**0**

votes

**2**answers

28 views

### Google Docs / Excel how to transform redundant row in columns

I have a list of redundant rows like this
See my Google sheet
Target FRBBED05517
Job state EXECUTION_ORDERED
Target FRBBED05523
Job state EXECUTION_ERROR
Target ...

**0**

votes

**2**answers

3k views

### Excel - Counta from a certain cell until a blank cell

I'm trying to create a dynamic formula which counts all the rows from cell D3 downwards until a blank cell is reached. I want to be able to add new data everyday and have the range automatically ...

**0**

votes

**0**answers

27 views

### How to use Google Sheets to Pull Changing Values from a list [closed]

I am tracking several salesperson's sales by Fiscal Period in a list. Columns are Period, Date, Company, $.
I would like to create a summary page that only shows the current period's sales, as ...

**1**

vote

**2**answers

73 views

### How to convert a number for a salary amount, to a specific, 9 digit, format in Excel

If I enter a salary dollar amount as 34512.15, I'd like to have a formula return this amount in a specific format as follows:
M(HT)(TT)(TH)HTOCC
M=Millions
(HT)=Hundred Thousands
(TT)=Ten Thousands
...

**0**

votes

**1**answer

28 views

### EXCEL: Additional COUNTIFS / Condition Needed for Value Ranges

I am recording multiple channels from individually housed birds, and sometimes their vocalizations bleed-through to other channels.
I want them to be tagged for removal in some way. ...

**-1**

votes

**1**answer

23 views

### Why doesn´t Excel function change cells for calcuation

I have the following function:
=SUMIF('Pivot'!$A$5:$A$51;'Projects Overview'!E4;'Pivot'!$B$5:$B$51)
I need it to automatically change $B$5:$B$51 to $C$5:$C$51, $D$5:$D$51, etc. when dragging the ...

**2**

votes

**2**answers

70 views

### Excel 2013 VBA programming

I'll start off my saying I'm trying to save time for a family member who is doing this program manually.
Sheet 1
Column A is a start date
Column B is a projected start date
Column E is a Task Name.
...

**1**

vote

**1**answer

32 views

### In Excel, How can I add values in a specific column if I have a checkbox type spreadsheet?

The Excel sheet is linear with column B being the distance walked each day. Across are a column for each participant. As they join the walking group each day, I'd like to keep a count on total ...

**0**

votes

**1**answer

61 views

### How to use Index and Match formulas for multiple criteria

I have a spreadsheet that contains Olympic information, with the following fields in columns:
A) Athlete, B) Age, C) Country, D) Year, E) Closing Ceremony Date, F) Sport G) Gold Medals.
I want ...

**1**

vote

**1**answer

24 views

### Getting CHAR function to return multiple characters in Excel 2011

I'm using Office 2011 on Mac.
I'm trying to write a formula to strip lower-case characters (and ideally spaces as well, but that's less important) from text in a cell. The purpose is to convert ...

**0**

votes

**2**answers

29 views

### SUMPRODUCT with VLOOKUP

I'm trying to get the Product and Sum of what matches in the TC Ref column and the Earn Type.
For example 7926 would be (45.32 x -3) + (45.32 x -3) + (45.32 x -4) for REG and (45.32 x -4) for OT.
I ...

**0**

votes

**5**answers

894 views

### Excel COUNTIF Slow

I'm having an odd issue in excel that I haven't run into before. I have an excel sheet that I'm attempting to pull some specific records from, requiring that I know of duplicates. I am using a COUNTIF ...

**2**

votes

**2**answers

38 views

### Counting the Cells in a Named Range without VBA

I have a disjoint set of 9 cells: A1,B3,C5,D7,E11,F13,G17,H19,I23. I have assigned a Name to these cells: MyPicksI have a simple formula to pick the value in one of these cells at random:
...

**4**

votes

**1**answer

2k views

### Linking Excel 2013 cell to Word 2013 table cell adds unwanted line breaks

I have a problem where I am trying to copy an excel cell that contains content using a formula, into a word document table cell and link them such that the formatting is used in word.
What happens is ...

**1**

vote

**0**answers

19 views

### How do I incorporate IFERROR into this formula?

I need to know how to include an IFERROR statement in this formula in Excel 2007.
=VLOOKUP($E3,HSA!$E$3:$G$20,2,FALSE)*$D3
I want the formula to return a 0 instead of an #N/A.

**1**

vote

**1**answer

26 views

### Find first number greater than x and return another value in that row

I have created an investment spreadsheet. In one column I have the ending balance each year. In another column I have the years.
A B
2000 500K
2001 450K
2003 1.2M
2004 1.5M
How can I display ...

**1**

vote

**2**answers

27 views

### How do I compare two list of data that contains a certain number in Excell?

How do I compare two lists of columns that contains a certain number? Please look at my example below:
Column 1 Column 2
-------------------
300 Room 305
301 Room 306
302 Room ...

**1**

vote

**1**answer

19 views

### Summing multiple cells that match row AND column criteria?

I've been given a file with 2 years worth of dates as column headers and sales info below. There's also a column that contains Year data. I'm trying to sort by both Year and Day of the Week. I'm ...

**0**

votes

**1**answer

16 views

### How can I create multiple spreadsheets using the value of a column?

I have a workbook (Req_PO_Log_V2.xls) that has all of the expenditures for our repair and maintenance funds from our current FY. The lay out is as follows:
| Date | Req Number | Acct | Vendor # | ...

**0**

votes

**2**answers

66 views

### IF Statement with VLOOKUP

I'm trying to get a value from the ADD Taxable which is column 10 in the Check (sheet). I only want the value from column 10 if the Earn Type on Timecard (Sheet) has BONUS in it. I'm using the TC Ref ...

**0**

votes

**1**answer

25 views

### How to find first date after given date in a sorted column

I have two columns of sorted dates in Excel. Given a date in column 1, how do I find the first date in column 2 after the given date?