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

**0**

votes

**2**answers

11 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

25 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

14 views

### Where does the calculation take place? [on hold]

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

23 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

37 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

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

**1**

vote

**2**answers

21k views

### Excel Formula To Get First Non-Zero Value in Row and Return Column Header

I am attempting to find a formula where I can look at a column range within a row and return the column header in that row where the first non-zero value occurs, moving left to right.
Below is a ...

**2**

votes

**1**answer

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

**1**

vote

**1**answer

11 views

### Applying reference to another sheet to all cells

I have LibreOffice Calc file which has 2 sheets.
I wanted to reference Sheet1 from Sheet2, so I wrote function =Sheet1.A1 in Sheet2 cell A1.
How do I apply this to all cells on Sheet2? (Sheet2.B5 = ...

**-1**

votes

**0**answers

32 views

### Office 2013 Data Extraction from multiple workbooks [on hold]

I generate numerous time sheets for my client on a daily basis (varies from 6-12 daily) and usually work is performed at least 6 days a week. I need to populate the total number of hours (ST and OT) ...

**0**

votes

**2**answers

20 views

### Excel 2010 Table auto expand/formula copy not working

I have a table with around 2k rows, for which I originally needed any formulae copied down into new rows, when they are added. To achieve this, I created a table from the data as many threads stated ...

**0**

votes

**0**answers

34 views

### excel - how to count number of names in a column, name is specified in a cell

I have two worksheets, "Clients 2014" and "Receipts 2014".
The receipts have client names for each row (each row is a receipt).
The search key is in the "Clients 2014" worksheet.
I want to ...

**0**

votes

**2**answers

2k views

### Excel INDIRECT function and conditional formatting - highlighting a row

I'm having an issue with conditional formatting using the INDIRECT function. I'm doing something similar to Using INDIRECT and AND/IF for conditional formatting , but the only answer there isn't ...

**0**

votes

**1**answer

2k views

### In excel how to do a 'sumif' function that is dynamic to a changing column

I'm trying to use a formula to do a sumif of a column of monthly data that changes depending on the current month. For example say I am entering this formula in column A of tab 2. Since it is ...

**0**

votes

**0**answers

15 views

### How do I copy/referrence last value of the column from another file and worksheet

I have two spreadsheets - one is the inventory sheet that gets updated with sales and purchases, and the other spreadsheet is where I want to reference or copy the ending inventory as soon as it gets ...

**4**

votes

**3**answers

16k views

### How do you include an OR inside a SUMPRODUCT function, in Microsoft Excel?

How do you include an OR inside the sumproduct function in Excel? I currently use two sumproduct formulas because I have two variables I want it to count:
...

**1**

vote

**2**answers

46 views

### How to I simplify code that combines information and adds up values for certain columns?

I am revamping some VBA code that was done 15 years ago that someone else created. I have to add more code to fit our needs but I exceed the amount of code allowed to run.
I have a Products in ...

**0**

votes

**1**answer

20 views

### I need the number in a cell to countdown when data is entered into other cells.

I'm making a sheet to keep track of the number of software licenses.
In cell B1 I have the number 35 representing 35 licenses. As they are assigned to systems in cells C5-C40 I would like the total ...

**0**

votes

**0**answers

42 views

### Referencing a cell from another sheet [closed]

I've got a sheet named 'No.of Mails Data'! where the data is
and i want to use my formula on another sheet
=OFFSET(INDIRECT((CONCATENATE("B",(COUNTIF($B:$B,"*")-6)*6+2))),,,30,1)
this is the ...

**0**

votes

**0**answers

9 views

### How can I Collate and create totals of a range of transactions in iwork Numbers

ColumnA ColumnB
Dave 10
Dave 10
Peter 5
Susan 5
Dave 5
Peter 5
I would like to create new table to read down ColumnA, create a new list with no Duplicates and Total up the Values ...

**0**

votes

**2**answers

2k views

### Complex rota hours calculation in excel

OK so there are a number of different shifts in operation here and I want a total hours formula.
problem is the rota is written using a number of different notations for different shifts
some are ...

**0**

votes

**1**answer

6k views

### Any trick to calculating percentages when the base is a negative number

I am trying to calculate percent change when the base number (at time 1) can be a negative and the resulting number is positive (at time 2). My problem is the percent change from -4 to +4. A change ...

**0**

votes

**1**answer

19 views

### How to return the name of uniquely named sheet

So I've searched high and low and cannot seem to find an answer to this question. Spent almost two days trying every thing I could find on line, but none were specific enough to my situation. Without ...

**0**

votes

**0**answers

17 views

### Update formula ranges when adding new rows to a spreadsheet [migrated]

I have a Google Spreadsheet in which I keep a list of expenses. I add a new expense by inserting a new row above the last expense. On the first row of the spreadsheet, I keep a sum of all expenses: ...

**1**

vote

**2**answers

39 views

### Excel formula to convert date formats

Assuming I have cells that have text values mentioned as:
02/23/15 to 03/01/15
Can I use excel functions to convert this to something like
23-Feb-15 to 1-Mar-15
So far I have looked at formula ...

**1**

vote

**2**answers

40 views

### Excel Convert the correct Date type

I'm currently following the date type dd/mm/yyyy i need to have another row showing the month in mmm type. E.g. From 13/09/2014 to SEP
My formula is =Text(A2, "mmm") which is the correct formula,
...

**0**

votes

**0**answers

15 views

### Memory limits in Excel (functions lookup)

can I obtain an advice how many Excel files with what size can I have opened at the same and link with functions (lookup) in Excel 2010 32 bit system?
Is there a way to predict how fast the ...

**1**

vote

**2**answers

38 views

### Multiple IF Statement

I have a question about IF statements.
I wanted to use VBA, but the other guy I am working with doesn't know how to do it, so I am trying to only use worksheet formulas. So, I figured the best way ...

**0**

votes

**2**answers

26 views

### Excel Equation: How to skip cell value if row header contains certain text

So I have a workbook for tracking my inventory in real time. It's a really bad bandaid for a problem I cannot have addressed at the moment, so bare with me.
The issue is, I want to have my inventory ...

**3**

votes

**4**answers

12k views

### Merging columns in Excel with “;” separator

I'm new here and I hope to seek for your assistance on converting data format of my excel to csv.
This is my raw data in excel in different row and cells:
A B C D E F
1 2 3 4 5 6
...

**7**

votes

**4**answers

25k views

### Is there an Excel function to create a hash value?

I'm working with a number of data lists that are keyed by document name. The document names, while very descriptive, are quite cumbersome if I need to view them on (up to 256 bytes is a lot of real ...

**0**

votes

**0**answers

19 views

### Rearrange columns from fiscal year to calendar year in OpenOffice spreadsheet

I have twelve monthly columns plus a total column in a spreadsheet that was originally created for an April through March fiscal year. Now I need to rearrange the columns for a calendar year, without ...

**0**

votes

**0**answers

32 views

### Why is VLOOKUP returning wrong cells as data is refreshed?

I am creating a spread sheet which works out my material requirements based on future order forecast. I have live data in the workbook which represents the material on stock and they're location. I ...

**2**

votes

**1**answer

255 views

### VBA - Arrayformula to a range stored as array variable

I need to apply a formula array to dynamic ranges in my spreadsheets, which for a fixed size range works fine using for example:
Selection.Offset(2, 0).FormulaArray = ...

**4**

votes

**2**answers

3k 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

32 views

### Excel 2007 how to put a text message in a cell if multiple cells are filled with data

I am trying to put a text message in a cell (say c1) if multiple cells in column A have data in. The cells in column A are A1-3, A5, A6 A8 and will have data from 1-5. I only want the text message ...

**1**

vote

**0**answers

38 views

### Excel for Mac 2011 move column 2 in sheet 1 to column 1 in sheet 2

I would like to move column 2 into column 1 for each successive sheet, one sheet per week, automatically. Is that possible? I do it with this formula ='1st week'!D2 and manually changing '1st week' to ...

**0**

votes

**0**answers

38 views

### IF Function Combined with Median date range check across multiple sheets

I am trying to create a formula that checks if a single cell date falls between a beginning and end date using the median function, and if so returns a value. I have multiple sheets I would like the ...

**0**

votes

**2**answers

31 views

### Excel 2007 - formula to display last updated cells

I have 6 cells, A1-A6 in Sheet1.
A1,A3,& A5 contain numbers 1-3
A2,A4,& A6 contain the date the numbers changed.
A1 & A2 will be filled first. I want to display both cells in Sheet2 in ...

**-4**

votes

**0**answers

47 views

**0**

votes

**2**answers

32 views

### How can I create an IF-THEN statement in Excel with two variables?

A B C(Output)
----------- ----------- ------
| 65 | 0 | 2 |
----------- ----------- ------
| 31 | 99 | 3 |
----------- ...

**9**

votes

**1**answer

41k views

### Create a pie chart from distinct values in one column by grouping data in Excel

I have an Excel document containing information from a survey. I want to create a pie chart over location (countries).
How can I make Excel group all the distinct values together and then display ...

**0**

votes

**1**answer

19 views

### Use AVERAGEIF across multiple cell ranges in a worksheet

I am using AVERAGEIF using the following to return the answer
=AVERAGEIF('Sheet 2'!H3:K10,I9,'Sheet 2'!K3:K10)
This works well if I have all the information in one sheet, however I have the ...

**0**

votes

**1**answer

42 views

### Excel: Converting two lists into a matrix

I have two lists in my workbook currently
Name | System 1 | System 2 | System 3 |
John | x | x | |
James| | x | x |
Peter| | x | ...

**4**

votes

**2**answers

509 views

### In which order does Excel process its formulae?

I've got a fairly large spreadsheet with major calculations going on, and it's starting to slow down every time a value that's part of a calculated field is modified. I'm in the process of optimizing ...

**0**

votes

**3**answers

2k views

### Excel sheet becomes very slow

I am working on an Excel 2007 workbook (less than 1gb in size). There is one sheet which has formulae linked to other two sheets in the same workbook. The problem is that it becomes really slow ...

**3**

votes

**1**answer

35 views

### Using a Defined Name in a Formula

I have a set of cells with values:
A1,B3,C5,D7,E11,F13,G17,H19,I23
I have a formula to select, at random, the value stored in one of these cells:
...

**0**

votes

**1**answer

16 views

### Find a specific name in a column, bring in the value in the corresponding row, in the column I designate in another worksheet

So I have a worksheet that people will go into and put their name down to sign up for a meeting for a specific date. In a separate sheet, I have their names and I manually input the days they signed ...

**2**

votes

**1**answer

56 views

### What is the cleanest way to write an array formula to facilitate the addition of more rows?

I have a spreadsheet with several columns that use array formulas, specifically referencing other columns of data. I started off using the A$1:A$100 syntax (if 100 was the last row of data in the ...

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