Questions tagged [microsoft-excel]
A spreadsheet application by Microsoft. Use this tag along with [vba] if your question involves programming Excel in VBA, and with [worksheet-function] if it involves an Excel formula or worksheet function. Also add the the specific version tag (e.g. [microsoft-excel-2019] or [microsoft-excel-365]) as features and functions may vary between them.
25,934
questions
0
votes
1
answer
544
views
Only display cells with data extracted successfully with REGEXEXTRACT in a Google sheet
What I'm looking to do is paste a bunch of raw data to column B on sheet 1 then look for cells with specific criteria and move them to column B on sheet 2. I was able to use the REGEXEXTRACT formula ...
- 23
0
votes
1
answer
2k
views
In Excel, I want to extract text before a certain character on some cells but not everyone [closed]
My list has the following
Ted
Ted~Brianna
Ted
Stacie
Ryan~Brennan
I want to extract only the first name, but not all my cells have multiple names. Can anyone help PLEASE?
0
votes
0
answers
87
views
VBA Sort & Hide Macro
I have a macro I use to sort and hide fields inside a table, however the users who utilize this don't all have the same fields or even in the same place:
User 1 "Order" is column B but User 2 "Order"...
- 87
0
votes
0
answers
59
views
Unlock Sheets via VBA
I have an excel with a lot of formulas running and it goes to an executive team. Naturally I have locked the two summary pages to prevent formulas from being deleted accidentally but on some systems ...
- 87
0
votes
0
answers
986
views
How to merge Excel data with an Excel template to output Excel workbooks?
I have a spreadsheet workbook with data per raws:
+-----------+------------+-------------+
| shapes | property 1| property 2 |
*-----------+------------+-------------+
| S1 + 20 | ...
- 725
0
votes
2
answers
3k
views
Excel - Split data by a range
Given the below example, I have a data set, and I want the data set to be grouped by x decrements of 5. How can I accomplish this?
...
enter image description here
0
votes
0
answers
233
views
Excel 2016 alphabethical sort doesn't seem to work correctly
I use an English installation of Windows, but an Swedish installation of Office 2016. When I sorted a couple of rows alphabethically today, the result was very strange. Apparently, Excel 2016 doesn't ...
0
votes
1
answer
1k
views
How to exclude certain columns from being filtered
I have a table of data with order dates, orderer and product id etc.
To the right of the table I have a visualization of the estimated amount of days until completed.
How can I make the ...
- 3
0
votes
2
answers
609
views
Change cell value then the will follow vba
I have a code that automatically fill series in column A.
My worksheet is protected with a password
and can only edit specific cells.
column A is the basis or the number of requested data.
Example:
...
- 45
0
votes
3
answers
42
views
How to know if any of the cell of Excel sheets contains formula fields
I have an Excel file with tens of sheets. I want to convert all the cells to "value" fields. I can do this by copy-paste manually, but I want to know what are the fields generated using formulas, ...
- 3,717
0
votes
0
answers
124
views
How to resolve runtime error for VBA Loop
I have a macro that loops through cells. My range starts at at $A1 but I need it to work for a range starting at $A8. How can I fix my code:
Sub test()
Dim a, i As Long, ii As Long, dic As ...
- 1
0
votes
1
answer
677
views
display current date base on cell value vba
kindly disregard my first two questions :D
I've already found a solution for it
but for this one it's kind a bit hard for me, since I'm not good at macro especially on its Condition function.
I have ...
- 45
0
votes
1
answer
2k
views
Excel - I want to populate a specific month's data by entering it's value equivalent without having to add multiple formulas together
Budget to Actual Table
2019 Actuals
In the Budget to Actual table there is an amount in cell L9. I want to populate the data from a separate table (2019 Actuals) using the column headers 1,2,3,4 etc ...
- 1
0
votes
1
answer
155
views
fill series with additional value vba
hi how can I make a fill series if I want it like this
GEE-2019-000
GEE-2019-001
GEE-2019-002 and so on...
here's my code for the fill series I just want to add GEE-2019-
Dim Lst As Long
...
- 45
0
votes
2
answers
58
views
Excel - Function used within range critera
While defining a range criteria, eg.
=SUMIFS(A:A, B:B, [criteria]);
I've found it is possible to do math operations on the criteria range.
Eg.
=SUMIFS(A:A, B:B, "/2>"&C1)
But have hit a wall ...
- 103
0
votes
1
answer
130
views
Date adjusted to next working days after cut-off vba
I have a question, I'm trying to create a macro that has cut off time.
for example:
on the worksheet paper, if the requester logged their request, current date will automatically display, however if ...
- 45
0
votes
1
answer
41
views
Deleting Rows and moving all rows together
I am needing to work out best way and quickest way to be able to achieve Deleting rows with an Y in column A and then moving all remaining rows up so no blank lines are left, as illustrated below.
...
- 1
-1
votes
3
answers
1k
views
How to convert rows of questions and answers to single row with columns
I have an Excel 365 (2016) file that contains rows of questions/answers that I need converted to columns as shown below.
Person Question Answer
Bob Dogs Name Rover
Bob ...
- 3
0
votes
0
answers
110
views
Delete Duplicate VBA macro
I am using a very simple simple delete duplicate macro but its prompting me to select a column. It'll always be exactly the same column and it even highlights it but still asks me to confirm. I would ...
- 87
0
votes
1
answer
45
views
How to show my full title when hiding adjacent cells?
My title is in A1, it is a large title. I am hiding column B. When I print preview, I lose half of my title because B is hidden. Is there any way to print the entire text in A1 correctly while hiding ...
0
votes
1
answer
614
views
Copy DUPLICATE values from two columns into a third column
This is similar to another post:
Copy unique values from two columns into a third column
I am looking to do the Opposite of this: Here's the gist...
Two separate things I am looking for from 5 ...
0
votes
0
answers
61
views
Why does Ctlr+P bring up randomly different print windows in Excel?
So I'm doing nonsense with SendKeys (Duplex printing >_>), and for some reason, Ctlr+P (seemingly) randomly brings up two different dialogs - the normal one, and something the "old" one.
Why is this?...
- 471
0
votes
0
answers
40
views
Excel text box content to cell
I know there is an option to link cell content to text box so once you type to cell something it will be displayed in text box. Is there something similar but vice versa? Once I type to text box it ...
- 141
0
votes
1
answer
94
views
Dynamically Changing Excel Chart
I have a range based table the has months April-March on row 1, then two metrics on rows 2&3 for those months. I have created a line chart based on the whole table and it works fine. But at the ...
- 405
1
vote
0
answers
36
views
Auto filter & copy data
I have an Excel file with data:
So I want to sort this data as per this image:
Then after that I want to filter data as per sorting order, then copy data in other sheet or other excel file (with ...
bhavesh
3
votes
5
answers
3k
views
Is there a text editor which displays column numbers and/or displays text in a grid to quickly glance column numbers?
I basically need a text editor which displays text in a grid, so that I can know the exact column numbers of the text. The numerical simulation software I am using, TOUGH+ (written in FORTRAN), has a ...
- 33
1
vote
3
answers
81
views
Count unique with criteria
I need a bit of help,
I am in need of counting a few things but I need to ensure they are unique to avoid a duplication issue.
So here it is
Column 1 Column2 Column3
1030 ...
- 87
0
votes
0
answers
25
views
I'd like to have a checkbox on one sheet send information to another sheet in excel
So I want to have a database of materials I use often with other information like cost, relail and available lengths et cetera. Then check the box next to it and have it populate on another sheet ...
- 101
1
vote
1
answer
210
views
Convert a date & time text string to a date?
A program I use exports its measurement dates as MM/DD/YYYY HH:MM and excel sees it as text. How do I convert that so that excel recognizes it as a date? Also, the length of the text string varies (e....
- 11
1
vote
1
answer
70
views
What's wrong with my IF formula? Combining IF, AND/OR, and COUNTIF into one
Thanks for pointing that out @Scott Craner. I made that edit to my formula, but it still doesn't work. Here's what I mean:
I wrote a simple formula in column H that checks if the Item# is on the sheet ...
- 15
0
votes
1
answer
4k
views
Excel: non-nested data column in Pivot Tables
I've created a Pivot Table that looks like this:
From a source table like this:
I am trying to add one more value to the table, "Cancellations" (which are already included in the "Private Lessons" ...
- 142
0
votes
2
answers
1k
views
How to compare data on two excel sheets with VBA code?
Sample data set:
Is there a VBA formula that will compare the dates, and the similar words, between the two data sets? For example, if it contains "Ter" and "1/9/18", then I can find out that the ...
0
votes
1
answer
87
views
Kindly explain about the below formula
Can anyone tell me what c1:c is in the following formula, at the begining of the INDIRECT function:
IFERROR(AGGREGATE(15,6,YEAR('Tab-3'!$D$3:$I$3)/(MMULT(COLUMN(INDIRECT("c1:c"&ROWS('Tab-3'!$C$4:$...
- 1
3
votes
1
answer
738
views
Microsot Excel - two way mirrored/linked cells with diferent values (one in percentage / another in absolute value)
I have A1 cell formatted as currency (e.g. EUR) - in which I manually enter
base value - e.g. 1000 EUR of which I want make further calculations in B1 and C1 cells.
B1 cell is formatted as percentage (...
- 33
0
votes
2
answers
2k
views
Generate rows based on columns
I have a starting table like this:
And I need to generate a row for each of the letters in the third column. What I did so far is to extract that info into separate columns, like this:
My goal is ...
user744427
0
votes
2
answers
3k
views
Counting duplicates with multiple criteria (SUM IF FREQUENCY MATCH)
I'm trying to get a count of crashes from an event log using the SUM IF FREQUENCY MATCH combination of formulas. Column A has all the event IDs (but there are duplicates that i would like to ignore ...
- 9
0
votes
0
answers
27
views
Why does my Userform and modules not persist when I quit out of a spreadsheet?
I am very new to VB. I am trying to create a userform which relies on modules, that starts upon pressing a button on the spreadsheet. I can get this working, until I exit the spreadsheet and try to ...
2
votes
1
answer
34k
views
Is there a way to show only specific values in x-axis of an excel chart?
In my problem, I have 0.1 0.2 0.5 1.0 on the x-axis and I want to plot my chart by showing only these values on the horizontal-axis. Is there anyway or any extension (like kutools) to make this happen ...
- 21
0
votes
1
answer
382
views
Compare excel column from different files
want to compare two different excel files in a way that if column A (file 1) match column A (file 2) then check for differences between column B (file 1) and column B (file 2)
- 1
0
votes
1
answer
471
views
combine data with different header
I have a multiple worksheets majority of have same header
but one(1) worksheet has different header
I have this code that will combine them all
Sub combined()
Dim xWs As Worksheet
On Error Resume ...
- 45
0
votes
1
answer
3k
views
How to link a cell to a shape or picture in excel?
I am wondering if it's possible to link a cell to a shape or picture that when I click the cell, no matter where the shape/picture is within the workbook, it should locate it. This is a shared ...
- 15
1
vote
3
answers
10k
views
Convert one column into a new column every 10 rows in Excel
I have a set of 110 numbers in Excel. I want to split this into 11 columns of 10 rows each.
So, for column:
324
292
300
120
276
320
984
136
300
280
69
289
266
292
266
494
315
118
295
289
I want ...
- 13
0
votes
2
answers
620
views
Excel with Legacy (Web) connection - prompted for login on every refresh
(I am the author of both the Excel sheet and the webpage the connection goes to)
I have an Excel sheet with multiple embedded Connections: "From Web (Legacy)" to a webpage with an HTML table in it. ...
- 117
0
votes
1
answer
991
views
How do I calculate my average page coverage for my Black toner cartridges based on the pages Yielded?
This should be a simple formula, but the logic is escaping me.
Let's take an example cartridge that Yields 4,000 pages (at the industry standard page coverage of 5%).
This hypothetical cartridge ran ...
1
vote
0
answers
171
views
Excel VBA Codes Not Working in Newer Version
I have written some codes in Excel 2010 and gave them to a colleague who has Excel 2013. However, they just are not working properly in the newer version of Excel. For example, when you are pressing ...
- 11
1
vote
0
answers
32
views
Excel - How to look up most recent date data entered
Sheet 1 of my spreadsheet is used to show which retail store each member of my team is visiting that week - Team members names on Y axis and days of the week on X axis, then store names in the cells ...
- 11
0
votes
2
answers
12k
views
How do I hide rows based on a cell value
I have a worksheet which has many formulas and I need the following to happen: when this worksheet is used, and some cells on different rows are filled with a number other than 0, the rows which ...
- 1
1
vote
1
answer
285
views
Can excel return the dynamic address of a specific cell?
I'm trying to work out if I can get the address of a specific cell (X) returned as a value (e.g B7), but in such a way that, should (X) subsequently be moved (e.g. B7 moves to M7), the correct, ...
- 11
0
votes
2
answers
42
views
Searching for a value in Excel between tabs
I have a function that will take a value from Column B in Sheet1 and search for a match in Column D of Sheet2 and return that value if found.
=INDEX(Sheet2!$D:$D,MATCH(B2,Sheet2!$D:$D,0))
However, ...
- 11
6
votes
1
answer
14k
views
Excel telling me my name already exists when renaming a table
I have copied a table from an external workbook into a different workbook. When I rename the table (on the design tab), Excel tells me that the name already exists. When I rename a range within ...
- 61