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

**0**

votes

**2**answers

14 views

### Loop down column until blank cell

The following formula works well if I define each cell (over and over), but I need a loop that will run down column A & B for hundreds of rows.
I have tried and just cannot seem to be able to ...

**1**

vote

**1**answer

10 views

### How can I custom format in excel to place periods (as separators) in an 18 digit entry?

This is regarding 18-digit accounting codes that contain multiple periods, as separators. I would like to enter just the digits without typing in the periods. Example:
112233333444555556
would ...

**0**

votes

**2**answers

26 views

### Excel - Sum assigned numeric value of 1 to different phrases in different cells in a row

I'm assessing a survey of multiple choice answers using Excel. I need to assign a numeric value of 1 to fixed text answers and show the sum on another worksheet in the same workbook (the text is ...

**0**

votes

**0**answers

23 views

### Easy way to convert one row of data into multiple in Excel

I have a worksheet that looks like this:
Right now each Number contains a varying amount of Companies and Company Numbers. I would like to set up the spreadsheet so each Company has their own row ...

**-1**

votes

**1**answer

33 views

### Formula to return “N/A” when other cell value is “No”

How do I get one cell to come up with "N/A" when "No" is selected from another cell's drop down?

**1**

vote

**1**answer

15 views

### Aggregate a range of worksheets with errors

I have several worksheet in Microsoft Excel and I am currently adding them like this:
=SUM(MySheet1:MySheetN!A2)
This funtion adds the content of all A2 cells on all the worksheets contained ...

**2**

votes

**1**answer

19 views

### Adding values among text in an Excel cell

I have an Excel sheet with 7895 lines, 6 columns in column B I have values like:
“Classification 124: Item 1 (€2345,70) item 2 (€123) item 3 (€1456,75)”
What I am trying to do is create a formula or ...

**0**

votes

**0**answers

12 views

### Formula code in Word 2016 [mac]

I'm currently making an invoice template in word, but I'm fighting with the formula code which is used.
Currently this is my table:
But, the number of rows wil depend on the days which I've ...

**0**

votes

**0**answers

14 views

### Cleaning up data fetched from Google Spreadsheet to Excel

I've been trying to synchronize and build a local database with Excel data. I need to build the database and program to create user accounts, manage the packages, track and log the status of the ...

**0**

votes

**1**answer

8 views

### Excel: How to obtain depured colum B after deleting matching values from column A

So I have in Column A a Blacklist of sites that i can not use any more
In column B i have listed all of my current sites (more than 5000 rows).
So i need to look if any of the values of column A is ...

**0**

votes

**1**answer

26 views

### EXCEL SUMIFS with dynamic tabs (INDIRECT)

I'm trying to get below SUMIFS formula to work with INDIRECT. I have data for each week in separate tabs .. ie WEEK05, WEEK06 etc. The result of the formula (a date) will come from column W based on ...

**1**

vote

**0**answers

27 views

### Calculating partial values for use in a weighted average

I have a spreadsheet with the following information:
Column A contains a list of courses (eg Course 1, Course 2, etc)
Column B contains the number of credits available to each course
Column C ...

**1**

vote

**0**answers

23 views

### Excel search function search term from other sheet [duplicate]

I have an excel sheet with a function which uses the SEARCH function in the following way:
=IF(OR(IFERROR(SEARCH("TERM1";MYCELL;1);0) > 0;IFERROR(SEARCH("TERM2";MYCELL;1);0) > ...

**0**

votes

**0**answers

41 views

### multiple IF conditions and answers

This is a cashflow spreadsheet. I want the column next to the expenses column to show me the source of the expense.
The expenses are arranged in spreadsheets, so for example; checks, taxes, general ...

**0**

votes

**2**answers

2k views

### Excel: AVERAGEIF on multiple work sheets (via 3D Reference)

I have a question when using excel averageif functions:
I have a workbook containing monthly information (stored as numerical values) in 12 different worksheets (named Jan, Feb,..., Dec). In each ...

**1**

vote

**2**answers

40 views

### Microsoft excel - formula dragging

How can I drag a formula to all the adjacent columns with similar intervals in it?
For example:
in column F, the formula is ('Time sheets'!G10-'Time sheets'!F10)*24
and in column G, it's ('Time ...

**2**

votes

**1**answer

17 views

### Index-Match returning value in cell above desired value

I have a couple index matches going on in a workbook I'm playing with. I basically have a huge lists of addresses in a table and I'm trying to pull specific ones to get mailing codes. I index match ...

**0**

votes

**1**answer

22 views

### Selecting a value based on a lookup value that fits between two values

I have a Worksheet in which I have two "tables" looking like this:
What I'm trying to achieve is to do the following:
Take the first two digit in Column D (for the first row 77)
See which row in ...

**0**

votes

**1**answer

3k views

### Updating another Excel spreadsheet from current spreadsheet

I used the VBA code in "How do you write an Excel formula that will paste a specific value in a different cell?" posted here in May 2013 and it works well to populate/fill another cell in the same ...

**3**

votes

**2**answers

22 views

### if indexmatch = #N/A keep original cell value

I have 2 worksheets in excel.
Sheet 1 is a price list given to us by a supplier
Sheet 2 is a list of all the products we supply (can be from multiple suppliers).
In the first column of each Sheet is ...

**-1**

votes

**1**answer

61 views

### Formatting any cell referenced in a Formula [on hold]

I have two sheets, one with formulas pulling data from the second. I need to create a macro or some conditional format to highlight the cell green if the data was pulled from the second sheet. sheet ...

**2**

votes

**1**answer

28 views

### Cells evaluated by user-defined module do not update automatically

I read the questions existing but no answer seemed to help. I have a function I wrote in VB which evaluates sum of cells in a certain range by font color, it all works well when I type the function in ...

**4**

votes

**3**answers

21k views

### Excel-Overcoming #VALUE error with FIND function

In cell A1 I have entered "Apple". In B2 I enter the formula =FIND("Apple",A:A). However I keep getting #VALUE error. Can anyone explain this and how do I overcome it?

**0**

votes

**0**answers

18 views

### Ms excel while entering more than 15 digit no [duplicate]

I want to add more than 20 digits in an Excel cell.
The current format of the cell is general, it converts the number to an exponential format. I tried with a number format and accounting, but when I ...

**1**

vote

**1**answer

38 views

### Cannot Use Autosum on cells that Contain Formulas

I have cells that have IF formulas in them. Excel won't let me autosum the results of those. It's as if Excel does not recognize those cells as numbers. Granted, numbers do show, but in actuality the ...

**5**

votes

**1**answer

14k views

### What excel formula can I use to calculate the 2nd Monday of a given month?

What excel formula can I use to calculate the second Monday of a given month?
You can assume I have a cell containing the first day of the month to work with.
In the interests of other users, can ...

**0**

votes

**1**answer

23 views

### How to display unique numerical values from different columns into a single cell (separated by commas)

I have a unique problem I have been trying to tackle with no avail.
I have scoured through the internet but only found partial solutions and not a complete solution.
I am looking for a function (or ...

**0**

votes

**1**answer

21 views

### How can I count unique comma-separated values - Early Bound vs. Late Bound

I am trying to display unique values from a string of values in a single cell.(separated by a comma).
I've come across this solution:
How can I count unique comma-separated values in Excel 2010
...

**3**

votes

**1**answer

33 views

### How can I look up the 2 axis values by inputting a number?

I'm a graphic designer and not very familiar with Excel but I'm fairly sure it's the simplest solution to my problem. I have a table (see below) with 2 axis going from A to L. Inside that table are a ...

**3**

votes

**1**answer

25 views

### Excel formulas - how to make a cell blank down the page

I am having trouble figuring out the formula for my budget.
Cells look like this
A B C D
DATE WEEKLY BILLS TOTAL
2015 $50 $1,956.73
Jan ...

**1**

vote

**1**answer

26 views

### How to grep summarized value from excel cell range?

I want to get summarize information from the following sheet. I don't know how to summarize this information.
My scenario is like this.
If I input -6, I want to find my target value in this ...

**0**

votes

**0**answers

10 views

### Auto-Fill data between two spreadsheets, adding new row, keeping same column, when new val

So I have a list of items that need to be constantly added to a list for auditing purposes. Problem is, the item names constantly change and also on occasion, new items are added.
Essentially, I am ...

**0**

votes

**2**answers

27 views

### Adding components of formula (net vs. gross)

I would like to add the components of a formula across a range. We have one section that shows the net cash flows for specific items, and multiple specific items.
For example =8+9-5 shows "12" ...

**-1**

votes

**0**answers

23 views

### How can I automate the process of printing 2 rows of data from one sheet after pasting it onto the form on another sheet?

Sheet 1 of my spreadsheet has the following form:
A B C D
They are just columns named 'A','B','C' and 'D'.
Sheet 2 has the following data:
A
1
2
3
4
5
6
7
8
9
10
I want to print pages of ...

**0**

votes

**1**answer

22 views

### Clamping values while averaging

I have two formulas that are calculated using the same dataset. One formula calculates the average of the values, while the other calculates an average with a maximum allowed value of 60. I'm ...

**0**

votes

**2**answers

36 views

### Data validation in Excel- all values in comma-delimited list within cell range

I have a reference table of memos and some information about them, including what other memos they reference and are referenced by. I want to make a data validation rule that will appear red if any of ...

**4**

votes

**2**answers

342 views

### Generate Combinations in Excel

I have an input row which contains the cells:
30
30
30
50
50
60
100
I want to generate all possible combinations of length 3 from this set. So I would want rows containing 3 populated cells and ...

**2**

votes

**3**answers

26 views

### Excel SUMPRODUCT - exclude header cells from formula

I have two columns, one with a date and one with values, and I want to sum up all cells, where the year of the date is the desired year. My problem is, that I have headers in the table, and I don't ...

**-1**

votes

**1**answer

23 views

### EXCEL: Can I get a count of a certain value from columns with certain headings, only from the columns with certain text?

A screenshot of the spreadsheet1
Hi
I am looking for a way (in the form of an Excel formula) that can do this
I need to count the number of "o" in columns with headings "Threat", for each row. For ...

**-1**

votes

**0**answers

40 views

### Excel Formula to show All dates in a range after today

I am trying to build a spreadsheet that I am using the lookup feature to take data entered on sheet 1 to locate that data on sheet 3, I need it to then list all Dates after today for the current ...

**1**

vote

**2**answers

32 views

### How to use OR in a COUNTIF function when dealing with dates and “>” characters

I want to write a function whereby I could count a cell if it is blank OR greater than a date.
This is a piece in what will be a much larger formula so looking to avoid a solution of ...

**0**

votes

**1**answer

34 views

### How does this SUMPRODUCT(COUNTIF( )) function work?

I was searching for a solution to count distinct values in a range, and came across this article. The example provides a list of ten names comprised of four unique entries. It then uses the ...

**0**

votes

**2**answers

11 views

### Excel filtering all cells in column instead of those filtered

I am trying to add column value in a sheet with a filter applied.
The problem I have is that Excel is adding values for rows that have been filtered out rather than just those the filter is showing.
...

**0**

votes

**0**answers

13 views

### Excel save relative formula for re-use

I need to go through a sheet, filtering on a series of fields and running a formula against the resulting columns.
However, typing the formula each time is slow and annoying.
How can I save the ...

**1**

vote

**3**answers

4k views

### How can I count unique comma-separated values in Excel 2010

I need to build a formula to walk a column of comma-separated values and count the number of unique values seen. I am using Excel 2010. I have a screenshot of some example data...
The output of ...

**-1**

votes

**1**answer

67 views

### How do I reference another cell based on the value of another cell?

No one understood my question which is understood due to my bad explanation.
I'll put an example to avoid further confusion.
In Sheet2 I want to change A2's formula then it will fill B2,C2, and ...

**2**

votes

**2**answers

80 views

### How to flag rows with the pattern non-blank, blank, non-blank using Excel

I would like your help with something I have been trying to figure out for the past two days.
I have data with 1000's of rows and several columns. I want to insert a column with a (array) formula ...

**-1**

votes

**2**answers

23 views

### In a table of groups and members, how to reduce a certain percentage of members per group?

I have an Excel spreadsheet with a table of groups and members. For example:
A Jonathan
A Roger
A Donald
B John
B Lilya
C Richard
C Lorry
What I want to do is to reduce the number of members per ...

**3**

votes

**1**answer

6k views

### Hide “inconsistent formula” error in a selection in Excel

This may seem easy to more advanced users, but I can't seem to find this out there.
I have a complex Excel worksheet. It has various formulas/calculations throughout the sheet.
Unfortunately, ...

**0**

votes

**1**answer

19 views

### Why is the MATCH function is not resolving my lookup array?

In Excel 2010, I am using two Excel tables. One table (ActiveDS) has a single column and contains a list of our active data sources. The other table (ApproverRole) has a number of columns, including a ...