**1**

vote

**0**answers

5 views

### Varying Format Part Number Sort Issue

(Current Sort Sample:)
2-1203-4
2-1206-3
2CM-
3-1610-1
3-999
…
AR3021-A-7802
AR3021-A-7802-1
B43570-
B43570-3
I am working on an 8000+ record parts list. The challenge I am running into is that ...

**0**

votes

**0**answers

13 views

### Excel x64 file corruption in .xlsb files for Office 2013

I have a number of Excel files, all created in Office x64, all in .xlsb format and all containing macros. Every now and again, these files become corrupted and I cannot get them to run. I can however ...

**1**

vote

**3**answers

30 views

### How do I use the AND function and the MATCH function together to get something that is not “No” and has not been returned previously

I have the following data and need to return all values that aren't "No".
I.e., I want to create, in another column,
a list of all the values from Column E that aren't "No".
There will never be ...

**0**

votes

**1**answer

17 views

### Check which (multiple) cells in column A contain substrings specified in column B?

In column A I have M&A deal descriptions that mention company names. In column B I have company names, for each of which I want to see where it is mentioned in column A.
What I'm having trouble ...

**2**

votes

**0**answers

12 views

### MS Excel 2013 function TODAY() not updating unless source file is open

I use the TODAY() function in an excel worksheet which is accessed using MATLAB (i.e. I don't read the worksheet directly). Now I have to open the excel file just to update the TODAY() function, and ...

**2**

votes

**0**answers

14 views

### Unlock Excel VBA “Find” window location

When editing VBA code the FIND window (Ctrl F) appears at the bottom of the screen. It can be manually re-positioned but upon re-opening it appears at the bottom again. It used to open in the last ...

**0**

votes

**0**answers

19 views

### Match each row against all other rows

I have 53 rows with 25 columns nand it's all txt values. Is there a formula that will match each row against all other rows? I basicaly need a similarity matrix and am wondering if there is a shorter ...

**1**

vote

**1**answer

14 views

### Excel chart: Poor plot of a straight horizontal line, due to settings that use “Major Unit: Fixed” for the vertical axis

I have a table of data that is the source for a chart. I import different data into the table often to plot it in the chart. In the settings for vertical axis, I selected "Major Unit: Fixed (at 1.0)", ...

**-4**

votes

**0**answers

17 views

### I want to develop the splitting one row into multiple rows in Excel

You provided an excellent answer on how to convert Excel data in rows into a vertical column or series of columns. The page is: How do I split one row into multiple rows with Excel?
In that ...

**0**

votes

**0**answers

22 views

### Sum if function - data missed

I'm calculating a value based on 3 criteria. One of the values being calculated is incorrect as one of the criteria is not being picked up for one of the lines. E.g I'm counting hours based on ...

**-2**

votes

**0**answers

20 views

### Dynamic text in Word based on Excel output

I am trying to print a brochure for clients. The client selects the options from an excel sheet. Based on the selections, i prepare a word output, which has some standard terms and conditions and some ...

**0**

votes

**0**answers

6 views

### Fetch Google Sheets data with Excel Power Query

I am using Excel 2103 and Excel power query.
The fetch data from web works fantastically on most websites, however I cannot get it to work on Google Sheets
I have tried using the share URL as well ...

**0**

votes

**2**answers

62 views

### How to tell Excel that “Years” are “Years”, not data

I have googled everywhere about this but no solution found. I have three columns in Excel:
A: Years, from 1960 to 2013
B: Maize production (in tonnes)
C. Wheat production (in tonnes)
If I make a ...

**-1**

votes

**0**answers

24 views

### Restricting a cell value in Excel

I need to restrict a cell with following two conditions:
text value which equals to APPLIED FOR (11 characters)
alphanumeric value with minimum & maximum of 11 characters
Kindly help.

**3**

votes

**1**answer

35 views

### Excel date month issue

I am working on projects that come in every 4 and a half month. I want to make a calender so I can see when the deadline is for each project.
I have a formula that adds up 4 months and 14 days. (4,5 ...

**2**

votes

**0**answers

34 views

### Excel array formula and structured table date header works with double digits and fails with single digit dates

I have a formula that matches the date with a structured table header row. I have extensive code that deals with the text string nature of this row successfully.
However, when trying to check visible ...

**1**

vote

**1**answer

15 views

### Copy an excel file with Macros and New Tabs in the ribbon to another computer

I'm working with Microsoft Excel 2013. I'm using couple of macros I did and I made a new tab in the ribbon menu which shows the macros I did so that I can click on the this created tab then click on ...

**-1**

votes

**0**answers

22 views

### Excel Formula to show number of months to break even for ROI

Trying to create a formula to determine length of time (in months) to reach ROI.
Column A - Start Date
Column B - Remaining balance until ROI (Investment less current profits) reflects a (negative) ...

**0**

votes

**0**answers

23 views

### Automated Goal Seek - Excel

I have been using goal seek successfully to back into a known number. This works great with a handful of lines - now I need it for 100s (probably 1000s) of lines.
Any help would be greatly ...

**0**

votes

**0**answers

23 views

### Put a new string of characters on a new line after each line break after every new string in a text file

I need to put a new string of characters on a new line after each line break after every new string in a text file.
It is a large list of website addresses that are only seperated by one space on one ...

**5**

votes

**1**answer

25 views

### All Public Variables in VBA Macros in my office stopped working this morning

It turns out, I'm an idiot (or, at least, hopelessly inexperienced with this whole software development thing). The below is what I spent my whole day panicking about. I'll write an answer detailing ...

**0**

votes

**0**answers

20 views

### How to merge/teleport a column

I'm working on an inventory sheet. As I sale items or buy items, I'd like to have one enter point ( or two if needed) to key them in. So if (G5 = sold) then the number enter would "move" to F5(total) ...

**0**

votes

**1**answer

32 views

### How to to divide column containing formulas and hard-coded strings into two separate columns?

I have a column that consists of hard-coded cells as well as formulas. Based on whether a specific cell is either hard-coded or contains a formula, I want to display it in two separate new columns.
...

**0**

votes

**0**answers

20 views

### How can I select two data points on line graph and return its corresponding values?

I have a simple line chart and I want to select any two data points of line and wants in return its corresponding value.
For example, my 11 different points are below. Simple line graph can be drawn ...

**1**

vote

**2**answers

22 views

### Returning a blank cell in MS Excel formula if 2 source cells are blank

My formula in B3 is =A3+B2-B1. I want B3 to be blank if both B2 and B1 are blank also. Could anyone help me with this please?

**0**

votes

**1**answer

11 views

### Turn off automatic display of properties

In my workplace, any time I open an Excel sheet (Office Professional Plus 2010) the "Document Properties" or "Other Properties - Server" docked dialog appears at the top of the document, dominating a ...

**0**

votes

**1**answer

23 views

### Merging five colums but do not merge if row contains two values

I have 5 columns all with numeric data in. R S T U V
The first three columns can be seen here so you get the idea. I want to merge them all into the first column "R" - in the vast majority of ...

**0**

votes

**2**answers

49 views

### How to lookup a value and return multiple unique values to a single cell - comma separated

I am attempting to lookup a unique identifier (Product Number) which is held in several different stock locations. For example:
__A__ __B__ __C__
1 Item # Warehouse ...

**2**

votes

**1**answer

27 views

### Concatenate a range with a conditional seperator

I'm looking for something similar to this trick for concatenating a range with a seperator, but I want the output to include a special character on the item on the range that is for this row.
For ...

**0**

votes

**1**answer

16 views

### Date values displayed as numbers instead of dates

Whenever I type a date into one of my worksheets the cell displays the value as a number not a date.
The cell's value is a date and the format is date but the text displayed is a number.
? ...

**0**

votes

**0**answers

10 views

### Multiple selection and graph export in Excel Online

I'm exploring Microsoft's online Office Suite and I have some questions about Excel Online.
1) Is it possible to multi-select multiple cells? I'm referring to the action in the "classic" version ...

**2**

votes

**2**answers

18 views

### Show decimal places if not a complete whole number

I have numbers in cells in Excel. I want the numbers formatted so that if they have decimal places they show to a maximum of two, and if they have no decimal places it doesn't show any.
For example:
...

**0**

votes

**0**answers

23 views

### If(ISERROR(INDEX combination produces “You've entered too many arguments for this function” error

I have two working formulas and I want to combine them both:
This identifies a unique item number in a list
=IFERROR(INDEX($B$3:$B$16, MATCH(0,COUNTIF($E2:E$2, $B$3:$B$16), 0)),"")
This ...

**0**

votes

**0**answers

25 views

### Excel 2013 External data import and copy

I have a workbook into which I import external web data. This is linked to another worksheet so that for instance when I refresh data into cell A1 that is automatically copied into A1 in another ...

**0**

votes

**1**answer

22 views

### Sum of Excel Range Between Dates

So I've struggled with this problem several times and usually just end up giving up on it, but this time I want to solve the problem once and for all. I have a straightforward table as follows:
Date ...

**1**

vote

**1**answer

9 views

### Merge tables with each table a column in the new table, merging rows

I've looked at the question How do I merge two tables in Excel that have identical columns? (and the unanswered followup Merging tables in Excel).
I have a case that is somewhat similar, but with ...

**0**

votes

**2**answers

24 views

### Fill one cell depending on value of another

I'm trying to create a Risk Assessment sheet in Excel.
I have one drop-down with days at the moment and I want another cell to be populated depending on what day is selected.
I have managed to get ...

**0**

votes

**1**answer

25 views

### Excel formula related to conditional formatting

Column A has 10 numbers expressed as percentages and is conditionally formatted to highlight the top 40%.
I want column B to show a "1" next to the corresponding highlighted top 40% of numbers in ...

**0**

votes

**1**answer

19 views

### Getting “Application Defined or Object Defined” error '1004"

So I was trying to copy data from different sheet and paste on current and I am getting this error:
"Application Defined or Object Defined" error '1004"
Can someone help me with this ?
Option ...

**0**

votes

**1**answer

55 views

### Find out the number of months between two dates [duplicate]

I need to calculate accumulating leave for personnel.
It grows by 1.25 days per month.
What formula should I use to add 1.25 per month starting from the date of employment?

**0**

votes

**0**answers

23 views

### Excel 2007 Conditional Formatting On Formula Not Updating When Cell Updates

I have set the following conditional formatting rules:
=$B97=1 Set Format (Custom) ">" @ On cell =$D97
=$B97=2 Set Format (Custom) "> >" @ On cell =$D97
=$B97=3 ...

**1**

vote

**1**answer

19 views

### Creating a table similar to pivot table

I'm not sure how to explain it, but I hope this example makes it clear.
Data:
Yes/No | Place1 | Place2 | Place3
----------------------------------
Yes | Thing1 | Thing2 | Apple
No | Apple | ...

**1**

vote

**3**answers

39 views

### Excel, Average for a specific column within table

Im trying to calculate an average just for one column within a table of values.
It is like this:
Hi Hello Hey
1 | 2 | 3
1 | 2 | 3
1 | 2 | 3
And I'm trying to get the average for one ...

**0**

votes

**0**answers

43 views

### How to disable snap/jump scrolling in Excel 2016 for Mac

I'm desparately looking for a way to disable snap-scrolling in Excel 2016 for Mac. Basically as you scroll Excel will continually try to keep the cell you're focused on in the centered. So when you're ...

**1**

vote

**0**answers

21 views

### How to transpose repeating groups in Excel

How would I transpose repeating rows and columns in Excel. For example how would I transpose the repeating pattern of rows (Tan color) to repeating columns (Purple). Would I need to do this in VBA ...

**1**

vote

**1**answer

42 views

### Excel : how to put a literal dot into a cell format

Whenever I put a dot character into a custom cell format Excel will interpret it as if I want a decimal separator in that location of the format. The problem being that decimal separator changes with ...

**0**

votes

**1**answer

18 views

### Search two columns in Microsoft excel 2013 and set the value of a third column with a specific value

I want to search a specific text which is the firstname_lastname e.g John_Smith in worksheetA. WorksheetA has a column firstname and another column lastname. So after knowing which row has the ...

**0**

votes

**1**answer

24 views

### How do you prevent corruption of shared Excel files?

I have 10 or so Shared Macro Enabled Excel files that about 30-50 users modify multiple times each day. Over time the files get bogged down and bloated from excel thinking users are still using them ...

**0**

votes

**2**answers

31 views

### Finding #NA values after Vlookup between two excel

We have two sheets for Jan and Aug. Both consist two columns , one for Client name and another one for no of shares they attain. we have to find out client names
which exist in jan but not in Aug
...

**1**

vote

**1**answer

18 views

### Excel on Mac automatically converts all numbers to thousands

I'm on Excel 14 for Macintosh.
It automatically converts all numbers to be representative of thousands. I have searched throughout to change a preference or option and cannot find it.
Example
...