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

**-1**

votes

**2**answers

1k views

### How Do You Stop Auto Formatting to a Cell I'm Using as a Barcode Number [duplicate]

I am having a little bit of a problem with Excel. I am typing into a cell a barcode number for my products but every time I leave the cell it automatically formats the cell as below.
Number I want to ...

**0**

votes

**1**answer

284 views

### Calculation of hour in Excel using [h]:mm formula

START END TOTAL TOTAL
21:00 3:15 6:15 6:15
23:25 1:11 1:46 1:46
20:25 5:20 8:55 8:55 ...

**1**

vote

**2**answers

182 views

### Excel: Check for specific value in a matching row

I want to check which products (listed in rows) are produced by which company (listed in column headings) in order to fill a table like this
Company A Company B Company C
Product 1
...

**1**

vote

**3**answers

216 views

### formula for date to fill its self out but results stay hidden when date is blank

I have made a spreadsheet that when I put in the date at the top of the header it automatically fills in the rest of the spreadsheet. BUT, I would like for the results to stay hidden when there is not ...

**0**

votes

**1**answer

516 views

### How to do a sliding window in an excel table?

I have a table with a date column and a value column like this
|A |B
1 |date |value
2 |2013-12-10 |1
3 |2013-12-10 |3
4 |2013-12-09 |2
5 |2013-12-04 |5
6 |2013-12-02 |2
Dates can ...

**0**

votes

**0**answers

77 views

### Is there something better than VLOOKUP?

I'm wondering if there is something like a VLOOKUP for multiple matches in one column.
So, for example, if I have the following:
Alfred red
Jone orange
Christina blue
Danielle blue
...

**0**

votes

**2**answers

976 views

### VLOOKUP with multiple criteria

I have a workbook with 2 sheets: spg and data
From the data sheet I wish to do a vlookup in the spg sheet, based on the value of multiple columns in spg.
I can do a =VLOOKUP(H5,spg!$D:$I,6,0) but ...

**1**

vote

**2**answers

100 views

### What functions can I use to track subscriptions?

I am struggling to make a spreadsheet which will help me take control of member's subscription fees. Fees are paid per month.
The spreadsheet has 12 horizontal cells, each one for a different ...

**0**

votes

**3**answers

63 views

### Excel counta function fixed reach [duplicate]

I have a planning sheet in Microsoft Excel 2010 for my company. I have made a screenshot of the planning sheet which you can see below. In the column CAP the planner can see how much capacity that ...

**1**

vote

**1**answer

286 views

### Add cell data to new column at the end of each row in excel?

I have a spreadsheet and I need to combine the information in three cells and add the combined information to a new cell on the same row. For example, I have columns A through M and rows 1 through 10. ...

**0**

votes

**1**answer

575 views

### How to “Fill Down” more intelligently in Excel?

I have a formula in cell A1:
=VLOOKUP(E2,Master!A2:C560,3,0)
If I do "fill down" to paste this formula into all the cells below, the formula changes as follows:
=VLOOKUP(E2,Master!A2:C560,3,0)
...

**0**

votes

**1**answer

53 views

### How do I lookup using 2 values, Verticle and Horizonal? (Apple Numbers)

I have a sheet that has an individuals info, one table as their Age (TABLE 1). Another Table has a score in cell (TABLE 2). I want to lookup the VALUE that corresponds with their age and score in ...

**0**

votes

**1**answer

51 views

### multiple IF Calculation excel

I currently have this formula in Excel:
=IF(G1+I1>E77,H1)
Sometimes I want it to be like this:
=IF(C1+E1>G1,D1)
I need both these formulas in the same cell but there will only ever be a ...

**0**

votes

**2**answers

67 views

### Force a one-dimensional array into a constant in Excel

I have a condition in an Excel IF-statement as follows:
INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0)))=1
where
INDIRECT(ADDRESS(ROW(ME3),MATCH("PremiumWaiver",$1:$1,0)))
eveluates to ...

**0**

votes

**1**answer

2k views

### How can I find max value and display text next to it in Excel?

I need a formula that will find the highest number in a column and then display the text in the cell immediately to the left of that number, i.e. find the highest grade and display the name of the ...

**0**

votes

**3**answers

95 views

### Excel Using Reference for Worksheet names [duplicate]

I've looked around to try and resolve this, however I am unfamiliar with the coding.
Here's my line of code,
='[WorkBook]BOM'!G6
What I want to do is make the Workbook call to a cell so I can mass ...

**0**

votes

**1**answer

95 views

### Replace cells in spreadsheet with cells from different spreadsheet in Excel 2010

I have a spreadsheet with phone numbers.
347-286-3341
347-286-3342
347-286-3343
347-286-3344
347-286-3345
etc...
I have another spreadsheet with phone numbers and corresponding names.
...

**0**

votes

**1**answer

34 views

### Summarizing the sales data to monthly

I'm having the following table of data:
Customer 14-Sep-12 30-Sep-12 1-oct-12 28-Nov-12 5-Nov-12
ASTDU 4718.00 3885.00 1680.00 3058.00 2147.00
BJURT 3646.00 4557.00 ...

**1**

vote

**0**answers

136 views

### automatically transfer any data on excel spreadsheet to master spreadsheet

I work in a library and I am working on a workbook to track our orders from one company. I have three sheets where each sheet represents a different branch of our library. I am trying to have it set ...

**0**

votes

**1**answer

113 views

### How to change a row of cells on excel 2010 depending on different cell data

Can anyone help me with a problem I'm having on excel 2010 please?
I am trying to change the colour of part of a row of cells when certain data is input. For example I want cells A13 to G13 to ...

**0**

votes

**1**answer

104 views

### sum values based in agent ID

I have a sheet with agent id's in column a and under each ID is a sub identifier with a value in column b of 1 - 7
sub identifiers are
Break_1000
Meeting_2000
Training_3000
Personal_4000
...

**0**

votes

**1**answer

3k views

### Check if a Row Range Contains another Row Range values in Excel

I have an Excel file that contains two different columns having Customer Numbers (let's say CustomerA and CustomerB columns). What I would like to do to check which of the CustomerA values match the ...

**0**

votes

**1**answer

98 views

### Dynamically updated floorplan/asset register in Excel

I'd like to create a combined asset register and office floorplan in Excel 2010.
Ideally it would have one data sheet with rows containing: ethernet socket number, room number, building, user name ...

**1**

vote

**2**answers

370 views

### Get percentage of distinct values with dependency on 2nd column

I have a survey that I sent out to people. One question asked their sex and another asked if they get headaches. I now have to results in an Excel file and I'm trying to analyze it.
But I'm having ...

**0**

votes

**1**answer

520 views

### how do i extract cells from multiple spreadsheets all with same format into new master file

I have 300 excel files each with the exact same format - 4 worksheets in each file with same column/row headers. I am trying to extract information from select cells of each of these 4 worksheets (for ...

**5**

votes

**2**answers

7k views

### Excel-Count number of visible cells containing certain string

I have a sheet in which I first apply a filter to a certain column to show "Unique records only". Now I have a column which contains, for e.g., values like:
Applepie
Applesauce
Crabapple
Banana
Mango
...

**0**

votes

**6**answers

21k views

### Convert Excel Text string to Time

I'm trying to Export from an OLD System that stored Time in / Time out as a text string in 24 hour time.
For example 3pm, is entered as 1500 (no colon)
Is there a simple way I can convert these ...

**0**

votes

**1**answer

50 views

### Unique values in multiple columns

I have a spreadsheet with the following:-
Prod No Store 1 $ Store 1 Qty Store 2 Sale Store 2 Qty etc
A 4.00 1 7.50 2
B 0 ...

**1**

vote

**1**answer

107 views

### Extending formula down the column without symmetry

Suppose this is the formula for my I2 cell =IF(C2<>C1, N2, K1).
If I extend this formula down the I column in excel, the default behaviour is
I3= IF(C3<>C2,N3,K2)
I4= ...

**1**

vote

**2**answers

2k views

### I need to find the last Space in a XLS Cell

I have an Excel sheet with text values, e.g. in A1: "This is the text". I need to find the position of the last space character. Is there an excel formula that will give me the result I want? I have ...

**1**

vote

**1**answer

98 views

### How can I combine the values from one column in rows where another column has duplicate data with Excel Starter?

I may have worded this badly so here is a screenshot:
So in reference to the screenshot I want to combine the three rows I have highlighted. That data from the column I pointed to should be ...

**1**

vote

**4**answers

8k views

### Excel: Change all cells with one character to something else

Is there a formula I can use that will change all cells with one character to something else?
For example, I have cells with single letters and no matter what the letter is I want that cell to ...

**2**

votes

**2**answers

315 views

### How to identify if a cell contains any of the values in a column

My question is the opposite (inverse?) of How to identify cells in Column A that contain any of the values in Column B. Specifically:
I have a list of 19,000 first names and 10,000 passwords. I'm ...

**0**

votes

**2**answers

486 views

### Excel saves a file in .csv but it keeps the columns and the rows as they were in the original sheet

I have a problem converting an Excel sheet in a *.csv file. I've searched all day for solutions, but nothing seems to work.
After I select the sheet and save it as "CSV(Comma delimited)", pressing OK ...

**0**

votes

**1**answer

56 views

### Sum a different range for each customer line on a table in Excel

I have a problem I am trying to solve and did not found similar issues in the website.
I have the following issue:
each customer receives the software upgrade at a different month
I want to see ...

**0**

votes

**1**answer

282 views

### Globe Software Atlas Error: AtlasTable function “Failed to build Atlas forumla”

We have an Atlas Template (Excel document) containing a number of formulas. One of these formulas is causing the exception message:
Failed to build Atlas formula: =AtlasTable("Production ...

**1**

vote

**0**answers

73 views

### Microsoft Excel hyperlink function to a specific location in a Word doc

I have a long column of numbers in Excel that I have mail merged to a Word doc. I would like to write a function in Excel that will hyperlink each cell from this column to its corresponding mail ...

**0**

votes

**1**answer

140 views

### How to get list of cells that match criteria in Excel 2013

I have three columns in an Excel 2013 spreadsheet. One is an ID, one is a reference column, and one is a referenced by column. Some rows reference other rows, and some rows are referenced by other ...

**-1**

votes

**1**answer

33 views

### Need Macro to perform formula based on cell not number

I am working on a budget workbook and in column I (I5-I1135) I have numbers that I need to upon prompt (such as CTRL C) have the numbers divide by 10 then multiply by 12..
Example:
cell I5 has ...

**2**

votes

**1**answer

41 views

### Vlookup not registering range changes in calculation

Typing the first vlookup yields the correct result. When I pull down the cell reference to use the same vlookup on other cells, the vlookup recognizes that the formula has changed, but still delivers ...

**0**

votes

**1**answer

94 views

### Calculating units of time in excel in order to multiply

I would like a formula that can multiply a unit of time, e,g if I enter 1hr and 20 min as 1.20 and multiply by10 i should have 13 hrs 20min displayed as 13.20 but what I would get currently is 11.

**0**

votes

**1**answer

1k views

### Excel 2007 adding a percentage increase

Value = 20 is a constant number
Percentage increase to "Value" in levels of 2%, 4%, 5% up to 20%
I would like to enter in a cell the LEVEL of the object and it return the VALUE + the percentage ...

**0**

votes

**2**answers

88 views

### EXCEL: If function and refering to cell containing operator

I don't know how to handle a cell which contain an operator and number.
Let's say I have 3 rows:
2
0,6
<0,1
In column B I have IF function which checks the number if it's greater or less than ...

**0**

votes

**1**answer

650 views

### Excel formulas for budgeting sheet (or VBA code)

I'm (an amateur) and I'm creating a sheet to help me with Budgeting. I've got bills that I can enter, but that come at a variety of different intervals. I'd love it if I could enter the total from ...

**1**

vote

**2**answers

399 views

### Get date in Excel and print as plain text in cell

Is there a way in Excel to get today's date, then convert it into plain text so it can't be changed again when you open the file at a later date?

**0**

votes

**1**answer

551 views

### Two related cells: give a value in one, calculate the other, and vice versa? [duplicate]

How can I have a cell that uses the literal value written into it, or calculates its value when no literal value was given?
For example:
I have two columns: column B with a price including VAT, and ...

**2**

votes

**2**answers

67 views

### How to deal with lots of brackets in a formula?

Say, I have a formula like this (in LaTeX or Maple or other text system):
Result: ...

**1**

vote

**1**answer

65 views

### Excel 2010 Recordset - How to insert a sequential number into an extracted ODBC recordset based on unique identifer

ColumnA ColumnB
EnquiryNo VisitNo
1804 0
1804 0
1804 0
1805 0
1806 0
1807 0
1807 0
Hope you can help as I am stumped!
My excel ...

**1**

vote

**4**answers

343 views

### Determining whether VBA is necessary in Excel, or if a pivot chart/table can suffice

I wish that I had a more precisely descriptive title for this question, but I am not very knowledgeable with Excel spreadsheets or VBA (I'm primarily a Java developer, and don't play much in the ...

**0**

votes

**1**answer

134 views

### Excel 2010 Formula not working - SUM IF

I have this excel table and I want to SUM some values from a column depending on what value they have on the column to the left.
The values from the right that have "Overall Results" on the left, are ...