All Questions
Tagged with microsoft-excel-2016 microsoft-excel-2007
34
questions
-2
votes
2
answers
11k
views
Conditional Formatting with greater than & less than parameters based on date in cell
Trying to conditionally format a cell based on the date in the cell. My ultimate goal is to try and track warranty expiration dates on some of the product we maintain. Within 30 days of expiration ...
- 1
0
votes
0
answers
159
views
Call a sheet using a variable name
I have this code in wich I add a sheet with a certain name and then I need to call this sheet to get a cell value but it doesn't assume.
Dim nome1 As String
Dim tableName As ListObject
Dim addedRow As ...
0
votes
1
answer
262
views
running 2 excel versions at once, cant associate filetype to older version
I have excel 07 and the latest Excel 365 (I think thats Excel 16) both installed in windows 10. When I just had 07, I had no problem associating csv and xlsx files with that program. Now that I have ...
- 1,173
0
votes
0
answers
92
views
Excel 2016 to open in the same place we closed it, like Excel 2010
Slow to the game, I know, but I just upgraded to Office 2016 from Office 2010. In 2010 when I opened Excel, it opened on the same screen, in the same position, no matter how many instances I opened......
- 476
0
votes
2
answers
48
views
excel - group sheets by columns
I have a bunch of .csv files with the same structure, but it's not guaranteed that they are in same order row-wise. I would like to group them all in a single spreadsheet. The following image draws ...
- 101
1
vote
1
answer
105
views
Excel: Match a value from a column and then collect values from a row
I want to enter a value in G2, then it will search from column A. In the picture attached it is A26 cell. Then I need to fill up G4 and G5 cell from column B and D.
How can I do it?
- 11
0
votes
1
answer
38
views
Transpose some columns of an Excel table
I am not able to achieve going from this format:
To this format:
I could transpose manually, but there are hundreds of items.
- 103
0
votes
0
answers
63
views
Which Excel formula is more elegant for this data?
I'll try to be succinct and I'll provide an image for clarity at the bottom.
The problem:
I have a large set of data consisting of City resources (publicly available) which includes the information at ...
- 1
0
votes
1
answer
248
views
Requesting Formula for 4-days work and 4-days off including weekends to schedule tasks
Experts,
I need help in formulating the below example for 4-days work and 4-days off based on hours scheduled. I'm able to do for straight days. Please see the details below.
Formulae Used
in cell G9=...
- 159
0
votes
1
answer
981
views
Excel formula to format URL Regex
I need to create Regex for Black listing that takes all variations of URL - http:// or https:// with or without www. I came up with this:
Url:
https://www.example.com/contactus.aspx
Regex:
(https?:\/\/...
- 3
-1
votes
1
answer
535
views
I want to scan down column 1 and if, for each cell, it matches any of the cells in column 2 I want it to return the entry in column 2 it matches
I want to scan down column A in sheet alpha (alpha!A) which is a list of emails such that if, for each cell A1,A2,...AX, the said cell matches any of the entries in column D of sheet beta (beta!D), ...
- 1
0
votes
1
answer
49
views
How do I conditionally format to highlight cells with =-IF in the formula?
I have a large workbook with many sheets, each with two sections in them.
One section, the formulas should begin
=IF(ISNA(...
and in the other section the formulas should begin
=-IF(ISNA(...
...
4
votes
1
answer
160
views
Force Excel to re-parse a whole column of dates
I have a series of dates in a column, currently recognized as text. Thus sorting doesn't work correctly.
When highlighting the column and changing the format to Date, Excel still recognizes it as ...
- 1,900
1
vote
2
answers
160
views
Excel 2016 - How to bring up a conditional list
I'm trying to create a dependent list in Excel 2016, but don't really know where to start. I tried following a couple of guides to create a dependent list, but it wasn't doing what I wanted it to do.
...
- 13
0
votes
1
answer
833
views
Trouble saving an Excel document as tab delimited file with japanese Shift-JIS
Trying to save an excel document containing Japanese characters as a tab delimited file with Japanese Shift-JIS
I have done the following steps.
Change the character encoding setting in the ...
1
vote
1
answer
688
views
SUM multiple rows when criteria is met and Match a specific column
=IFNA(SUM(VLOOKUP($A4,Consol!$C$8:$BS$222,MATCH(TB!G$1,Consol!$C$2:$BS$2,0),FALSE)),0)
I have a project finance model (PFM) by month and I need the data to feed into a TB (also monthly). So what I ...
- 11
1
vote
1
answer
1k
views
Excel function to find and count all duplicates by row, excluding the first instance
I'm trying to create a report in excel that counts the number of duplicate rows, excluding the first instance of that row, in an imported sheet. I know that I could use "remove duplicates" in the Data ...
user1022144
0
votes
0
answers
545
views
MATCH Formula Returning 0
I have the following formula:
{=INDEX(config!H2:H20,MATCH(1,(F5=config!F2:F20)*(VLOOKUP(D5,config!C2:D20,2,FALSE)=config!G2:G20),0)) }
I then wanted to add another condition to make sure that ...
- 157
0
votes
3
answers
72
views
Select 1st day of each user id in excel
Trying to figure out away to get the expected output from the id and date columns. How do i select 1st days of each user id .. the formula i tried is this IF(A2=A1,(IF(B2>B1,FALSE))) but not getting ...
- 5
-1
votes
1
answer
1k
views
What is Excel #N/A error abbreviation, looking for?
In MS-Excel we have below functions return errors:
#N/A #REF! #NAME? #DIV/0! #NULL! #VALUE! #NUM!
I think about what is the complete error massage, which each one are represents?
Of ...
- 35
0
votes
2
answers
131
views
Keep only one copy of the duplicated data
I am trying to clear out a list of content that appears 2,3,4 or more times repeatedly.
I have two columns and I would like to eliminate the whole row of any repeatedly entry of column B.
My ...
- 19
1
vote
1
answer
2k
views
Cell conditional formatting, based on the format of the same row's cells of another column
I've highlighted my duplicate text in column B, using Conditional Formatting. No I want to highlight the same field on column A (and afterwards delete all non highlighted texts), but I am not sure how ...
- 19
0
votes
1
answer
860
views
Power Query with Additional Column
I am using a power query to read rows indexes by an item number from one file I receive to one file I am maintaining. I need to add some columns that are editable by me (cell editing), in a way that ...
- 43
1
vote
2
answers
355
views
Filtering Excel Data by Column Value and Saving Columns to Individual Files
Many years back we had to come up with a solution to survey results that we were getting via CSV. Back then we'd get data where the first column was e-mails, and subsequent columns were 1's or null ...
- 43
6
votes
2
answers
6k
views
How to protect worksheet with writeable cells that retain formatting and comments
Is there a way to protect a worksheet such that certain cells are writeable (e.g. user can put numbers into these cells), but the formatting and comment box cannot be changed? I am able to create a ...
- 389
0
votes
1
answer
39
views
Update Content within Cells in Excel sheets using specific format
My excel sheet has columns with value like
RootUser*UK\abc.def*def, abc*-*https://website.xyz.com/sites/123*
And I want the format to be
<Mapping Source="UK\abc.def" Target="i:0#.f|membership|...
- 103
0
votes
1
answer
57
views
Excel Create a function that counts the number of "s" in a row
I have to create a function that counts and shows in one cellm the times that the "s" character appears in a row range in Excel. It has to be robust enough since many times the "s" character can ...
- 3
1
vote
1
answer
2k
views
Can't associate Excel files with Excel 2007
I have a pc with Wndows 7 Professional (SP1) which has MS Office 2007 installed, but also appears to have come with Office 2016 preinstalled but unregistered (i.e. a limited trial please buy type of ...
- 111
1
vote
1
answer
1k
views
Excel: How to get a running total that resets every 7 days
I need to create a formula that will find a running total for the week, then reset for the next week.
We have column D as the pay per hour, column E as the hours, column F as the pay for that day, ...
- 13
0
votes
1
answer
777
views
How to sum values in a range if criteria needs to be of multiple possibilities?
See screenshot. What I am trying to do is sum up all the fruits distributed by the US, but there are multiple fruits that I need to source from the key. I want to make a dynamic formula instead of ...
2
votes
1
answer
221
views
Change cell color when a different value is encountered
Hello there. I Inserted an SQL query in Excel to pick for me data like the above.
Is there a way I can set Excel to automatically Change The Background color each time it encounter a different value....
- 57
0
votes
1
answer
822
views
Excel - How to return a list of values in one column if a value in another column meets certain criteria
Hello need a bit of excel help here if possible
i have a list of prices in column a in ascending order and certain price thresholds (min/max) in 2 different cells in column b. I'm trying to pull all ...
- 1
0
votes
1
answer
48
views
How can I test out a line graph I made in Excel 2016 in an older version of Excel
Currently I was making some graphs and manipulating some data. It looks pretty good so far. Problem is : I left my work computer at work, and need to have some graphs tommorow. The work computers ...
- 115
0
votes
1
answer
183
views
MS Excel - I wish to count unique values based on a parent column
I am very new to Excel. I need some help in counting unique values in each of three columns (considering them only one at a time) with respect to a parent column (the first one (A) in the image). Can ...
- 1