# Tagged Questions

**1**

vote

**1**answer

18 views

### Sumifs function where I want to sum a range not just a single row or column

I am stuck.
I am entering timesheet date into an excel sheet:
Column A - Resource Name
Column B - Project Phase
Column C - Resource Team...
Column D to AAA is a date
Therefore a single resource could ...

**-1**

votes

**0**answers

24 views

### msgbox doesn't display current value [closed]

I am wondering why don't the msgbox display a new appended value from a cell rather just the declared variable.
I've declared a variable called "Newentry".I am adding a string value to a cell & ...

**0**

votes

**0**answers

28 views

### How do I format numbers with abbreviations in Excel 2003?

In Excel 2003, is there any way to format positive numbers with abbreviations (specifically K, M, & B) while displaying all decimal places but not trailing zeroes like so:
878.1K
100M
1.478B
...

**-1**

votes

**0**answers

19 views

### Excel Formula will not update [closed]

I have a formula in excel that will not update automatically even though it is set to do so. If I hit F9 it still will not update. I have to be in the formula bar and hit enter for the update. Any ...

**4**

votes

**1**answer

155 views

### How to automatically fill range by interpolating between lower and upper bounds

I apologize I do not have any code or examples too show for this. I am not sure how to even approach or identify the function or result I would like to achieve.
What I have is spreadsheet with ...

**2**

votes

**1**answer

31 views

### Excel - keyboard shortcut to double click on format painter

Double clicking on Format Painter has the effect of allowing you to click a few times and apply the same format in all those places.
Alt-H-FP has the same effect as a single click on format painter.
...

**4**

votes

**3**answers

40 views

### Keep Specific Columns From Repeating in Excel

There is a report that I pull from our ERP system that lists order detail information. It will list the order number, customer code, customer name, order date, order status, order total, product code, ...

**3**

votes

**4**answers

394 views

### Splitting the string by space?

I have a string like timestamp 32.828000?
I want to extract only 32.828000 from above string and store in adjacent cell.
I do
=left(b1,len(b1)-1)
i get timestamp 32.828000
Now how do I split the ...

**-7**

votes

**0**answers

28 views

### Excel Check if part of the value in a cell match another list and if yes return the list value [closed]

Excel checks if part of the value in a cell match another list and if yes return the list value.
For example:
Cell
LA, USA
List:
USA
China
UK
LA
Function:
find out the cell match usa and ...

**2**

votes

**0**answers

32 views

### Append values to Access table from excel with ID autogenerated

I have a access database with some tables which get updated daily with some values. I need the tables to have a ID field which is autogenerated when the other values of the table are imported from ...

**1**

vote

**1**answer

28 views

### How to add higest ten values from odd/even columns in Excel 2010

I tried to combine sumproduct and large like this.
=SUMPRODUCT(LARGE(((MOD(COLUMN(E48:AD48),2)=1)*E48:AD48),ROW(INDIRECT("1:10"))))
This worked for odd columns. When I changed 1 to 0, it didn't ...

**2**

votes

**1**answer

15 views

### Validate that a combination of cells are unique in a row in Excel

I have an Excel sheet where 3 columns (A, B, and C) form a unique identifier for each row, but the values in the columns are not unique. For example:
ABC REF 001
ABC REF 002
ABC ...

**-8**

votes

**0**answers

24 views

### Employee Salary Calculation [closed]

Employee compulsory time is: 7:00 hours per day. If employee slary is 10,000 per month:
C2 = In time : 10:00 AM
D2 = Out time : 13:00 PM
E2 = In time : 17:00 PM
F2 = Out time : 21:00 PM
Total ...

**0**

votes

**0**answers

26 views

### How to export images from Excel table [duplicate]

I have an Excel table with two columns: id and picture.
id is a string and picture contains a single jpeg picture.
This table contains almost 1,000 rows.
Is it possible to export the images as jpeg ...

**0**

votes

**1**answer

28 views

### Excel 2013 - PC - conditional formatting with multiple sheets

I have a project that covers 2 sheets. It concerns doing inventory for small businesses.
On sheet 1 there is a schedule of inventories and purchases that a business can make each month. On sheet 2 ...

**0**

votes

**0**answers

27 views

### Excel, macro move one row down and then delete the row

I'm trying to clean up duplicate rows. My data is in a table with filters applied. I have tried using the Excel feature to remove duplicates, but get the following error.
Cannot complete ...

**1**

vote

**0**answers

22 views

### Excel function Return all row that in the list cell match specific key word [closed]

I wanna make return all item from a list that any cell contain specific key word.
If possible in more advanced level, make the return from a range of cell instead of list of row. Thanks.

**0**

votes

**1**answer

32 views

### Multiple Formulas in one Cell for hotel

I am trying to create a housekeeping spreadsheet where when a room number is entered into a cell a 0 or 1 (0 is for studio and 1 is for a 1 bedroom) is automatically populated in another cell. I have ...

**2**

votes

**0**answers

17 views

### How to return results of SQL query to Excel sheet with VBA function [duplicate]

I'm trying to create a VBA function in Excel to affect dynamic value from Access to Excel sheet. This is my code.
Public Function SQL(DB As String, Query As String)
Dim conn As ADODB.Connection
...

**1**

vote

**3**answers

42 views

### Is there a way to set Excel up whereby trailing spaces are visible?

I know lots of techniques to remove trailing spaces from cells in Excel (e.g. TRIM), but sometimes I get given large sheets from other people which contain trailing spaces in dangerous places, where ...

**0**

votes

**1**answer

20 views

### adding image from excel to word using mail merge

I'm using mail merge to generate a word document from an excel sheet and some cells contain images (office 2013).
Is it possible to export them as well to word directly with mail merge without putting ...

**-4**

votes

**3**answers

34 views

### Convert a group of letters into a date in excel [closed]

I'm trying to convert a lettered date code into a date.
Example: SWITCHGEAR = 1,2,3,4,5,6,7,8,9 & 0. So when I enter a combination of the letters "SIRISC" it would read 13/03/14. I have tried ...

**0**

votes

**0**answers

20 views

### Query/Reports in Excel only

I'm trying to use Excel in a way probably more suited for Access. Unfortunately I have no access to Access or Visual Studio (outside the VB prompts in excel.)
I have an excel "database" with several ...

**-2**

votes

**1**answer

21 views

### How can I find cell with minimum value in a column and add the values next to it to another range?

I have values in range A1:A3 and also in B1:D3.I would like to find the cell with minimum value in range A1:A3 and to add the all row values next to that cell to another range by hitting a command ...

**0**

votes

**4**answers

35 views

### Excel 2007 How do I get rid of the 0 (zero) in this formula

I have dates in columns U,X,AA,AD,AG and AJ and Names in V,Y,AB,AE,AH and AK. I have the following formula to select the latest updates as they are updated. Columns U and V will be filled first and ...

**0**

votes

**1**answer

21 views

### MS Excel - Cumulative sum of a column based on “id values” in another column?

I have been trying to figure out how to make a formula that calculates cumulative sums of rows in a column based on their id values in another column but still haven't found any solution for it. I try ...

**0**

votes

**0**answers

25 views

### Excel formula returning different results on different machines

For a piece of work I'm doing, I use a formula that calculates the difference between two dates for each row in the report that the system generates. Essentially, the software I'm using generates the ...

**-4**

votes

**0**answers

12 views

### To get the result in excel [closed]

I have few character with some gap in each charaters.
Just wanted to get all the characters before the gap
Eg:- TSFT40009 TRMG23789
MNCDFEWIPZY MNKYUNIM20.

**0**

votes

**2**answers

29 views

### Why do I see ~$<filename> hidden Excel files?

I have some hidden excel files with the following name format
~$<filename>.xlsx
What is the purpose of these files ?
Is there any harm in deleting them ?

**2**

votes

**0**answers

11 views

### Pivot Table Slicers. Can I ensure one specific item is always selected?

I'm using Excel 2013.
As in the title, I have a slicer (connected to a pivot table and pivot chart) and I'd like to make sure one specific item in my slicer is always selected whilst allowing ...

**0**

votes

**1**answer

18 views

### Calculate total number of open item at each month in Powerpivot

I want to find a way to calculate the total amount of open items in each month in powerpivot. My data looks like this...
Id Team CreatedDate ClosedDate
01 AAA 1/30/2014 ...

**0**

votes

**1**answer

20 views

### Grab cell data and use in “IF” statement

Is it possible to grab a cell containing a date and paste that date in an "IF" statement containing text, similar to the below:
...

**0**

votes

**0**answers

20 views

### How to update cell reference after inserting a row [closed]

I have 2 columns A & B, A contains a specified number and B is incremented by +1 in each row.
What I'm looking for is when I insert a blank row A will be increment by +1, and B restarts from 1 ...

**-1**

votes

**1**answer

42 views

### How can I create a chart to show a possible pattern [closed]

I have 3 columns in my table.
Date Day of Week # of hours
11/27/2013 Wednesday 7
2/4/2015 Wednesday 7
3/18/2015 Wednesday 7
5/2/2013 Thursday 2.5
5/15/2013 Wednesday 2.5
...

**0**

votes

**1**answer

20 views

### How to sum up weighed normalizations of columns?

Say, I have a sheet with the following (simplified) structure with absolute values:
What I want to achieve is to get a table with the normalized values (i.e. the maximum is equivalent to 1. In my ...

**0**

votes

**2**answers

19 views

### How to create a Excel formula that finds the corresponding range in a matrix for a given value?

I would like to create a Microsoft Excel formula which finds the corresponding range in a matrix for a given value.
Explained with a generic example
Reference table
| Name | From | To |
| A | ...

**0**

votes

**0**answers

37 views

+50

### Hotkey to open filter drop down menu in merged cell in Excel 2010

I use Excel 2010 and use Alt+Down arrow to open filter drop down menus.
Sometimes my header contains vertically merged cells (e.g. autofilters are in the second row and in some columns cells in rows ...

**0**

votes

**1**answer

24 views

### Excel: How to filter values in a pivot table so only rows with the latest date display

I have some data I collect and I use a pivot table to analyze it. I recently started collecting the data in a different way to capture a running total, but it's messed up all my pivot tables. I am ...

**1**

vote

**1**answer

30 views

### Alternate Row Colors of Duplicate Data in Excel

I am working with a dataset in Excel that contains duplicate values and non-duplicate values that are numbers. I would like each duplicate value group to have alternating shades via Conditional ...

**1**

vote

**3**answers

41 views

### Removing Tabs When Exporting Excel to Text [closed]

When I save an Excel file as a text file every cell has a tab after it. How do I remove all of these tabs?
I'm looking for a way to do this with a basic text editor, like Notepad, because I need ...

**0**

votes

**0**answers

32 views

### How to Sum based on two critera, date range and name

I have a table that has timesheet data for a number of users.
Column A has the resource Names. Row 1 contains each week of the year, starting with the 5th of Jan.
The table contains hours booked by ...

**-1**

votes

**0**answers

22 views

### Extracting columns from an Excel file

Here is my scenario:
There is a certain service that creates summary reports each week. This is an Excel file with a number of rows and columns. One of these columns is an ID. Think of this as the ...

**0**

votes

**1**answer

31 views

### Convert multiple rows to single row for same Material

I have multiple rows for each Material (number of rows will vary) with different values for Manufacturers & Manufacturer part numbers. I need to convert this into one row for each Material with ...

**0**

votes

**1**answer

24 views

### Web Scraping Macro error

I am trying to scrap multiple pages that are a search result of a key work.
I wrote this code but I am getting an error when I run it. Error:
Application \-defined or object-defined error.
When I ...

**0**

votes

**2**answers

41 views

### How to give worksheet tab a color if range of cells contains text

I have tried code that I've found on SE, and elsewhere but they aren't working as I think they can. I'll list them below. I'm almost certain this is an easy question.
What I'm trying to do: If in any ...

**0**

votes

**1**answer

31 views

### Conditional sum in Excel or Libreoffice calc

As an example, in the table above, I want to populate the empty column titled 'Totals' with the corresponding sum of all the Values in the left tables 'Values' column belonging to the category ...

**0**

votes

**1**answer

16 views

### Converting a column list from A2:A142 to a list with commas into cell B1; holding a format as “0000” with leading zeros

This code works to convert the column list to a concatenated list with commas, however the format doesn't have leading zeros while the VBA loops in B!.
Dim i As Integer
Dim s As String
i = 2
Do ...

**1**

vote

**2**answers

33 views

### How to list all possible solutions for missing digit?

In Excel, I want to add 2 numbers with missing digits and would like a list of all possible solutions.
For example: Given 5.x1 + 2.y1 = 7.32, what are the possible combinations of x and y?
In this ...

**1**

vote

**0**answers

14 views

### Deleted record keeps reappearing in data file using MS Word 10 merge/Excel 10 spreadsheet

I am unable to remove one particular record from my data file. I remove it from the excel spreadsheet, but when I try to merge it is back--even after I have changed the file name after removing the ...

**1**

vote

**1**answer

20 views

### How to paste static value, not dynamic value in VBA

How do I change this piece of VBA code such that instead of =datewanted in the excel cell, I get the static value of the "datewanted".
ws.Range("A" & lastRowofA & ":A" & lastRow).Formula ...