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

learn more… | top users | synonyms (5)

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