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

**0**

votes

**1**answer

13 views

### Excel Can I have two LOOKUP functions within a single IF function?

I am attempting to organize a dashboard of sorts that will place the name of the appropriate owner in a column on the spreadsheet. The two criteria is the location or US state the item is located in ...

**0**

votes

**2**answers

7 views

### Excel Indirect #ref error

Trying to use the INDIRECT formula to pull a summary at the end of a large workbook. I have listed the Worksheet names in a single tab and now wish to use these to pull specific cell data into a ...

**0**

votes

**1**answer

16 views

### Adding Value to cells in Excel

I can't for the life of me figure out how to accomplish this formual and if it's even possible to do. I am trying to create a formula that will calculate the amount of shirts ordered for each size, ...

**1**

vote

**0**answers

13 views

### Control Number Generation

So currently I have the following. When I click a button or hit submit button The form looks on the Ledger Tab. I was trying to get the form to automatically generate a control number (ConNum) that ...

**0**

votes

**0**answers

9 views

### Ledger Balance Sheet Assitance

Need your help again....I am building a ledger in Excel. Tab 1 has a Form to input data into cells in tab 2. Tab 3 is Reference Information that the form pulls data from for drop down menus. On tab ...

**0**

votes

**1**answer

9 views

### How to find table_array referenced in Excel formula in cell

I was given an Excel spreadsheet that I have to convert into a SQL view.
In the cell formulas VLOOKUP is used and for almost all of the table_arrays I can find where the table_array is. But for one ...

**0**

votes

**0**answers

13 views

### Worksheet Formula Changes

='Location (33)'!A35
I have 1200 worksheets to pull in, I want the worksheet number to increase but not change the cell number, I've tried to make the cell constant and the table properties to fill ...

**-1**

votes

**0**answers

22 views

### Microsoft Excel Query

Suppose there are five columns:
A,B,C,D and E
in which:
A and C contain a combination of letters and numbers to represent a specific item and
B and D contain numbers to indicate the equivalent ...

**0**

votes

**2**answers

20 views

### Excel formula for a “running %” of a column

im trying to figure a formula for a running percentage of entries in a column.
For example, in column A, i have "r" for returns, and "n" for non returns, and "c" for canceled, but "c" is classed as a ...

**0**

votes

**0**answers

5 views

### Create a hyperlink to a cell in a “Table of Contents” sheet quickly

I have an excel document with a vast amount of sheets (upwards of 100). To make navigation easier, I made the first sheet a "Table of Contents" where I listed all of the other sheet names in a column ...

**0**

votes

**0**answers

20 views

### Excel formula needs help to create table

Trying to understand how to create a table that identifies what specific dates relate to the row and column it is in.
IE. if I have columns A, B, and C. and rows 1, 2, 3 with dates applied within ...

**0**

votes

**0**answers

7 views

### The data(Job #) in one column is only put to another spread sheet, IF another column contains data

J/C # A. B. C. D. E. F. G. H. TOTAL Estimated Time
97647 45 45 90 30 120 330
97651 15 30 45
99999 60 120 75 360 615
11111 ...

**-1**

votes

**0**answers

8 views

### Match information to a range and add corresponding data?

I've got a range (A1:b600) that contains names (a) and emails (b), and I'd like to check if the contents of another cell (D1) matches one of the names in my range. If it does I want to look for the ...

**0**

votes

**0**answers

10 views

### How do I stop column formulas from automatically calculating in an Excel Table?

When using a column formula for a table in a workbook with calculation set to manual, when tinkering with formulas, Excel calculates them if the column formula changes.
The automatic propagation of ...

**-1**

votes

**0**answers

13 views

### How to search a string with color code in many workbooks and copy row if true

How to search a string in many workbooks and copy row if true
Under this link I found a great tool to copy a row if true from a searies of workbooks and copy them into a new workbook.
My problem ...

**1**

vote

**2**answers

34 views

### Count coloured cells without a macro?

Is it possible to count cells by their colour (not by their cell value as they are blank) in Excel 2010/2013 without using VBA, as my files needs to be saved as .XLSX?

**0**

votes

**0**answers

21 views

### Vba Script to replace html tag with formatting

I am looking for a vba script, which replaces a html <strong>.
Example <strong>Any Text here shall be bold</strong>
The result shall be Any Text here shall be bold.
It shall macth ...

**0**

votes

**0**answers

23 views

### If 2 checkboxes true then add cell value - Excel Formula

I'm trying to add a list of prices depending on if 2 check boxes are ticked.
What I want to do is say if the paid checkbox is ticked and the just eat tickbox is ticked then add get the number in the ...

**1**

vote

**1**answer

28 views

### Change every character in a cell with another in Excel

I have a large list of phrases that I would like to make more disguised. I want to substitute each letter/character with another character and be able to decode this with another formula so I am able ...

**0**

votes

**1**answer

25 views

### Multiplying the remaining balance of an amount

I want to multiply the total of an array of cells, but only want to multiply the remaining number of the overage amount.
For example:
Goal amount: 10,000
Sum: 15,000
Multiply by: 5,000

**0**

votes

**2**answers

29 views

### How can I select and output the first and last row of a worksheet or a filtered list?

I am new to VBA and I have no clue where to start with this.
I have two columns of data:
Team Member Time
A 1
A 2
A 3
A 4
I am only interested in ...

**0**

votes

**2**answers

32 views

### Check if any names are present in two columns [duplicate]

I have two lists on names, first list of names just under 140k (Column A), second under just over 7k (Column B).
I want to see if there any matches from column A & B.
My Excel skills are basic ...

**1**

vote

**1**answer

24 views

### Look up a name from row in a timetable and output the column header in Excel

I'm making a timetable for interviews which comprise of 6 stations plus a rest.
(FYI in the timetable time is progressing downwards)
Sample of timetable with names omitted:
I'd like to create ...

**2**

votes

**0**answers

26 views

### Is there any way to include a comment in a referenced cell in Excel?

I have two Excel spreadsheets:
The master sheet which has tables of data
A second sheet which references certain cells from the master sheet in order to collate certain data.
I wanted to know if ...

**0**

votes

**1**answer

27 views

### I just cant figure out the formula for this excel sheet

i hope someone can help me with a formula for excel.
On my sheet i have on column A - D's for Dog, and C's for Cat, and O, for other.
I'm trying to figure out the formula for: If 8 or more of the ...

**2**

votes

**1**answer

29 views

### Increase a cell's value based on other cell's value

What I am looking for is an "IF" formula that can solve my problem:
I have a single sheet with 2 columns and an unlimited number of rows.
On the first column (Column#1), I have a drop-down with ...

**3**

votes

**1**answer

34 views

### In Excel, how would I calculate the number of Mondays between dates that excludes a list of holidays?

I'm currently using this formula:
=INT((WEEKDAY(E2-2)-E2+E3)/7).
I know how to use the NETWORKDAYS function, which allows you to a specify a list of holiday dates, but I can't figure out how to ...

**1**

vote

**1**answer

26 views

### Retrive row number of MIN IF formula

I would like to know the row being used in a MIN(IF()) formula so I can use it to grab a another value in the same row..
I have this formula:
Between the two sheets
A is an Id
B is a Date
C is ...

**0**

votes

**1**answer

12 views

### transposing and collating text from different sheets into separate sheet

I am using the following formula to collate text from 3 sheets into a 4th.
=$'sheet 1'.A1&CHAR(10)&$'sheet 2'.A1&CHAR(10)&$'sheet 3'.A1&CHAR(10)
this collates the text with a ...

**1**

vote

**1**answer

30 views

### Applying text formula only if text is in the cell in Excel

I am using 4 Excel sheets for collating text.
The formula I currently have in sheet 4 is:
=$Sheet1!A1&CHAR(10)&$Sheet2!A1&CHAR(10)&$Sheet3!A1&CHAR(10)
This allows me to write ...

**-1**

votes

**1**answer

24 views

### Auto-populate calendar with an “X” in Excel

I have have a set of due dates for preventative maintenance on equipment and I want a calendar to auto-populate with an "X" under the correct month it is due.
For example I have machine 1 due 1/1. ...

**0**

votes

**1**answer

21 views

### How to use conditional formatting to search a specific value in a range in ms excel?

I have values in the range K5:K333. I want the value in cell S5 be searched in the range and the corresponding cells of the range being formatted.
I am using conditional formatting's "use a formula ...

**0**

votes

**2**answers

23 views

### If multiple cells are with the same text, return this text(value) to another cell

I need your help with this:
If A1:E1 is "Red", return "Red" to C3;
If A1:E1 is "Blue", return "Blue" to C3;
BUT
If A1:E1 are not the same, return False or "X" to C3
What function to use ...

**-2**

votes

**0**answers

15 views

### Excel vlookup multiple value return [on hold]

I am shooting a short film and i need to allocate shots to be edited to my other 4 friends.
My initial approach is to have a master sheet where all the shots are kept and then a column headed ...

**0**

votes

**1**answer

26 views

### Prefix/Suffix Data Validation List

I'm finding it really difficult to understand how to prefix or suffix a set of words based on a what I have chosen in a 'Data Validation List'.
CLICK HERE TO SEE/DOWNLOAD AN EXAMPLE SPREADSHEET
I ...

**3**

votes

**1**answer

84 views

### excel ISTEXT returns True where a formula contains the answer “”

I have a formula that returns the value "" blank.(correctly) However when testing for ISTEXT, I get the result "True" I understand "" is seen as text, however Im looking to test for actual words that ...

**3**

votes

**1**answer

28 views

### Excel Large Function with Duplicates

I'm trying to apply a LARGE function to get the 5 largest values from a column ignoring duplicates. I did some searching around and found some proposed solutions but they don't seem to work for me. In ...

**0**

votes

**3**answers

45 views

### IF function with multiple criteria

I am trying to use an IF function to return a "1" if TRUE and a "0" if FALSE.
I need cell A1 or B1 AND C1 > 0 in order for the statement to be TRUE,or 1, else FALSE "0".
I cannot write the function ...

**0**

votes

**1**answer

33 views

### Formatting data in excel [on hold]

I am being provided information to organize and don't have options of how it is delivered. The data is editable within the "text boxes" and excel doesn't recognize any values in the cells. Almost like ...

**0**

votes

**1**answer

38 views

### Change the cell value depends upon the values in the 8 neighborhood cells in Excel [on hold]

My excel sheet contains 400 data points,its like a image pixel values,but some of my data points are missing so the value of that cell is zero,the data consist only four values 1,2,3 and 4. I need to ...

**1**

vote

**1**answer

35 views

### Make an array formula in excel calculate only on active rows

When excel calculates formulas over a column (e.g. a lookup), it's smart enough to only calculate until the last active row, rather than also running on all the blank rows.
With array formulas, it ...

**0**

votes

**1**answer

19 views

### sum values based on match criteria to another table

I would like to sum prices in one table based on a match criteria from another table
example table 1
Grocery Items Price
Apples $2
Oranges $3
Bread ...

**0**

votes

**0**answers

15 views

### Excel 2010 merge rows where data in duplicated except 7 columns

I have a xls file.
I has some duplicate rows of customer names and other data that is identical.
I want to be able to find the identical rows by customerid and copy data from one row to another ...

**0**

votes

**0**answers

12 views

### Filtering Pivot Table

I have this Pivot Table, and what I need to do is: Filter this data and show only those records whose date (3rd column) is equal to its parent date (2nd column) or just 1 day difference is there.
And ...

**0**

votes

**1**answer

29 views

### Change column references by dragging down rows

I have this equation in excel =COUNTIF(A2:A10001,"<0.5")
Is it possible to make it so that when this formula is copied down the formula becomes =COUNTIF(B2:B10001,"<0.5") and so on?

**-2**

votes

**2**answers

30 views

### Excel - Finding and Allocating [on hold]

I need to find the same values in column A (e.g. column A can contain buy a dog, white dog, cute cats, cuddly cats - I would need find all "cats" and then assign them to certain categories, e.g. "CAT ...

**1**

vote

**1**answer

16 views

### Convert formula to a comment

When programming, I often comment code out whilst I'm debugging.
I have a formula, which works fine, but I want to comment it out as I don't need it at the moment but will do in a few weeks time.
...

**0**

votes

**0**answers

11 views

### Count data from one column if other column is blank

I have a table which contains data read from a database and imported into the spreadsheet. What I am attempting to do is create a "live" report that will collect this info (on refresh) and calculate a ...

**1**

vote

**2**answers

38 views

### I need to find a number pattern in a string and copy it to another cell In Excel

I need to look at cells in column A to find the number pattern of NN-NNN and then copy that to cells in column c. e.g.- Column A has "SPREAD_11_PLAT_SHTS_22-315_IFR" and I need to copy the 22-315 to ...

**6**

votes

**3**answers

45 views

### Alternate If() function in Excel/OOCalc

Countless times over years, I've wanted to use an If statement in Excel/OpenOffice Calc in the form of:
If(A (comparison operator) B, A, C)
or, equivalently useful:
If(A (comparison operator) B, ...