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

**0**

votes

**0**answers

2 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

15 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

18 views

### Excel Formula will not update

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 ...

**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 ...

**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 ...

**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 ...

**-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**

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 ...

**-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

**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

27 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 ...

**-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

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 ...

**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

**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 ...

**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**

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
...

**-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 ...

**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 ...

**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

**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 ...

**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

**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 ...

**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 # | ...

**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 ...

**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?

**0**

votes

**2**answers

65 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 ...

**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:
...

**0**

votes

**2**answers

36 views

### How do I SUM values in column B based on criteria in column D in Google Spreadsheet?

I would like to sum all the values in column B based on a criteria measured against another column D in the same row. So, going down the Column B, I should count the value if Column D in that row has ...

**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**

votes

**1**answer

37 views

### Can I split a spreadsheet into multiple files based on a Row in Excel 2013?

I have a spreadsheet in excel 2013 which have multiple column and rows. I need to send some columns to different people separate files. Please anyone can help me in this. Please try to use macros, or ...

**0**

votes

**0**answers

38 views

### Extracting HTML from Excel to enter in MySQL/Wordpress site

I have a WordPress site and an Excel document. I'm not familiar with Excel formulas so forgive me.
The document contains a lot of names, email addresses and telephone numbers. I want to create a ...

**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

**2**answers

34 views

### Excel Index/Match Sum

I have 2 worksheets in Excel. Both have data from a 2 different databases. The first tab has part numbers and costs (could be different day to day), the second has how many times that part number ...

**-1**

votes

**2**answers

56 views

### If one number then a different number

I have two columns of numbers on a data page, over 500 pairs. And I need a formula for the working page that will insert the second number in “column B” if the first number is in “column A”. So if my ...

**0**

votes

**3**answers

43 views

### Excel macro: Last non-empty cell in a column

This is a followup question to this one.
The following formula can be used to find the value of the last non-empty cell in a column, in Microsoft Excel:
=LOOKUP(2,1/(A:A<>""),A:A)
My ...

**0**

votes

**3**answers

42 views

### How do I import data from 6 Excel Workbooks into one on a daily basis?

I'm needing to be able to pull data from 6 individual Excel Workbooks that are all formatted the same, first line being the same header across all six and then the data starting row 2 going down being ...

**1**

vote

**0**answers

17 views

### Where does the calculation take place? [closed]

In Google spreadsheets, I have quite a big amount of relatively complex array formulas in many cells. That takes ages to complete calculating.
Does the speed of calculation depend on my PC ...

**0**

votes

**2**answers

37 views

### Finding a value in one colunm that matches another column then putting that value in a third column (excel 2010)

All of the values are texts since the are usernames that are from two different systems.
so one column has:
Column A Column B
smit19108 Fred99015
Fred99015 Smit1908
...

**1**

vote

**1**answer

52 views

### Excel formula to show FORMULA

Ok, guys. I am 100% sure that a few days ago I saw and tested a real built-in Excel formula for showing a formula as text in any cell. Now, when I need it, I cannot find it!!! No VBA, no tricks. Just ...

**0**

votes

**0**answers

20 views

### Excel formula If column B starts with… then columns D & E populate from a list

I'm working on a spread sheet where column B is filled with ID numbers. Column D and E correlate directly to the first few digits of the ID number.
Example:
B= 1.1.1 then D= Imaging and E= T.P.
B= ...

**2**

votes

**1**answer

27 views

### Excel Formula - returning data

I have a spreadsheet that has four columns of data.
I want column number four (D) to display the value of column three (C) IF the number next to it in column two (B) matches ANY number in column one ...