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

learn more… | top users | synonyms (5)

0
votes
1answer
4 views

Hyperlinking or linking in another way to a cell without it being able to change

How do I hyperlink or link to a cell from another cell in a way that when rows are added, and the row numbers keeps changing due to new rows of information being added throughout the sheet, the link ...
0
votes
1answer
8 views

Add a cell content to another cell if a different cell has a specific content

How can I fill a cells value (e.g row 29 col A) with =sum(B1:Z1) but then only add the content of a cell in that row 29 if row 4 of that column says "UK IR". That way I can add all the cells together ...
0
votes
0answers
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
1answer
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 ...
-1
votes
0answers
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 ...
0
votes
1answer
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
0answers
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
1answer
17 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
0answers
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
1answer
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
1answer
40 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
0answers
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
1answer
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
0answers
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
0answers
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
1answer
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
1answer
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
1answer
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
1answer
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
2answers
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
2answers
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
2answers
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
1answer
34 views

How to count months in intersection of two date ranges [on hold]

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
0answers
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
1answer
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
2answers
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
1answer
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
1answer
62 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
1answer
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
1answer
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
2answers
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
0answers
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
2answers
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
1answer
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
1answer
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
1answer
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
2answers
68 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
2answers
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
2answers
37 views

How do I SUM values in column B based on criteria in column D in Google Spreadsheet? [on hold]

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
2answers
71 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
1answer
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
0answers
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
1answer
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
2answers
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
2answers
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
3answers
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
3answers
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
0answers
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
2answers
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
1answer
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 ...