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

**0**

votes

**1**answer

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

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

**0**

votes

**2**answers

24 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

43 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

23 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

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

**0**

votes

**0**answers

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

vote

**2**answers

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

110 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

149 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

98 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

53 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

176 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

47 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

56 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

45 views

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

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

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

**0**

votes

**3**answers

145 views

### Parsing non-standard date/time data in an Excel spreadsheet cell

I'm looking for some advice on this one more than anything. I have a spreadsheet that contains store hours for each location at work. The formats range from this:
"Mon-Sat 9:30am-9:00pm
Sunday 11:...

**2**

votes

**1**answer

157 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

231 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

57 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

22 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

70 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

76 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

89 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

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

**0**

votes

**1**answer

90 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

372 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

892 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

65 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 (return 1), else FALSE (return 0).
I cannot write ...

**0**

votes

**1**answer

98 views

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

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

184 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

48 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

39 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

24 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

740 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

33 views

### Excel - Finding and Allocating [closed]

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

20 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

40 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

78 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

72 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, C,...

**-1**

votes

**1**answer

93 views

### How do I populate cell based on value from two separate cells?

Can anyone help me write the below formula the correct way?
=IF(C13="North Dakota","$2,500",IF(C13="Montana","$2,500",IF(C15="Yes","$5,000","")))
What I wish to achieve is: If value selected in ...

**1**

vote

**1**answer

43 views

### Format Number with Leading & Trailing Zero

How can I convert 32.5 to 000003250 in Excel 2010?
I have tried TEXT(A1,"#000000.000000") and =A1&REPT("0",6-LEN(RIGHT(A1,LEN(A1)-FIND(".",A1))))
Both examples return with decimal point ...

**0**

votes

**1**answer

70 views

### Calculating number of complete boxes and loose items for Scout Popcorn Sale

I'm working on a table to calculate popcorn orders for my Boy Scout Troop annual sale. I want to calculate how many cases and loose item count of a specific item I should be receiving.
For example, ...

**0**

votes

**2**answers

116 views

### Excel: check if two cells have specific content and show true/false in another

I have a large datasheet with multiple columns. Long story short, my columns A and B (of text type) are lists of codes. For simplicity's sake let's say column A is days of the week, while column B is ...

**0**

votes

**1**answer

117 views

### VLOOKUP cells that contain a certain text in Excel

The challenge is as follows: I have a column with names extracted from a source. The names come in different ways each time it is extracted. For me to be able to use it, I need the names in a specific ...

**1**

vote

**2**answers

154 views

### COUNTIF and SUM in Excel with different ranges

Is there a more efficient way of making the following function?:
>=COUNTIF(A1,">0")+COUNTIF(C1,">0")+COUNTIF(E1,">0")+COUNTIF(G1,">0")
I want to count if the cell is bigger than 0 ...

**0**

votes

**1**answer

78 views

### Need unique count of text from multiple columns by category

My data looks something like (assume begins in A1):
Category,Label1,Label2
Dogs,blank,doe
Dogs,zoo,blank
Cats,zoo,yup
Cats,doe,hgg
Cats,blank,doe
What the result should look like:
Dogs,...

**1**

vote

**4**answers

39 views

### Excel 2013 - Fill in numbers from 2000 - 65000 without using the drag to fill handle mouse method

I need to fill in numbers from 2000 to 65000 in Excel 2013 and using the fill handle and dragging is a very long process.
Is there another way to do this?

**2**

votes

**2**answers

255 views

### Excel - return multiple values for each of a list of values

I've been trying to solve this for a good few hours now, but with no result...
My setup:
I've got excel sheets of cosmetics product recipes.
Each recipe contains dozens of ingredients, each given ...