62 questions
-3
votes
1
answer
208
views
Check if Map Chart drawn successfully or not
1) The following code is able to draw a Map Chart successfully.
Public Sub Macro1()
For i = ActiveSheet.Shapes.Count To 1 Step -1
ActiveSheet.Shapes(i).Delete
Next i
...
-2
votes
1
answer
60
views
How do I pivot distinct values in Excel (preferably PivotTable fields editor)? [closed]
I have a table layout with different number of text values for each id column. Now, I'd like to pivot them, so that each id gets a single row and each text value is put into a different column of the ...
- 3,262
2
votes
2
answers
103
views
Excel ignores doublequotes around line feed character in CSV file
I have input files that are CSV format, created in another program. I have no control over them. I'm finding that with those files, line feed characters within doublequotes are incorectly treated as ...
- 1,067
1
vote
1
answer
109
views
VBA code exits on a file open if started by keyboard command
I'm using Excel 2021. I have a VBA macro that can be started either by menu command or keyboard shortcut.
It had previously been working fine both ways. But while adding to the macro, I found that ...
- 1,067
2
votes
0
answers
50
views
Disable touch shortcut menu pop-up from long presses
Excel 2021 used with touch screen. Windows 10. Long taps or double taps cause this horizontal pop-up bar to appear. I have disabled right click and double click through VBA but this is something ...
- 21
0
votes
0
answers
44
views
How to use complex formulas in data validation via VBA without Excel adding the @ symbol? [duplicate]
I'm facing two related problems while working with VBA in Excel. Both involve complex formulas, but they behave differently when set through VBA compared to when entered manually in the Excel UI.
...
2
votes
2
answers
101
views
Is it possible to construct an array in excel using existing cell values
It is possible to construct an array in excel as follows: ={1,2;3,4}
Using this method I want to calculate the inverse of a 5 x 5 matrix of a series of elements in one row rather than distributed as ...
- 63
0
votes
1
answer
51
views
How to find a cell in a table based on what row a button is?
I am wanting to add a sheet using the first 2 cells of a table as it's name. For example, in the following table, Button 1 would create a sheet named "Doe,John".
Last
First
Button
Doe
John
...
0
votes
2
answers
217
views
Count each time value appears, reset if value change, using dynamic array [duplicate]
Data
| label | seq |
| ----- | --- |
| a | |
| a | |
| a | |
| b | |
| b | |
| c | |
| c | |
| c | |
What to do
I want to count the ...
- 173
0
votes
2
answers
115
views
How to Extract the Actual Value from &8&P in VBA?
What I tried:
I attempted to retrieve the value from the RightHeader in VBA using the following code:
Sub GetRightHeaderValue()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)
Dim ...
- 1
1
vote
3
answers
107
views
Excel: Linebreak after 3 Commas
I have a cell with a content like:
2,1.5,2,3,1,2,4,3.1,2.1,9
What I need is to add a linebreak after every 3rd comma, like:
2,1.5,2,
3,1,2,
4,3.1,2.1,
9
Can this be done with a formular (not Visual ...
- 1,847
1
vote
3
answers
92
views
Count all unique values in Table columns sequentially, and summarize them into a single string?
I'm working on a D&D character sheet model in Excel (2021), which uses a Table to fill in the data per level. The current thing I'm running into is the Class and Level field, which tells you how ...
- 13
1
vote
0
answers
69
views
Xlwings Converting a Cell to an Excel DataType (e.g., Stocks, Currencies)
I'm trying to setup a command using Xlwings within a Python script to set a specific cell within an Excel worksheet to an Excel Data type (e.g., Stocks, Currencies). I've found the VBA code that sets ...
- 11
0
votes
0
answers
52
views
Excel Combine Column A from Multiple Worksheets
Currently using Excel 2021 Home Office. I have a sheet named 'Total'.
Goal: Display all the words from Column A , in multiple sheets from Sheet1 to Sheet9.
So it will display words all from Column A ...
- 1,327
2
votes
2
answers
70
views
How to extract a list of records with certain criteria met in one table while identifiers extracted from another table?
In Microsoft Excel, I have a table named TableTwo, with fields:
tName
dDate
tStatus
tName is key-identifier of the records.
We extract a list of tName, named tNames, from table TableOne. Then for ...
- 25
1
vote
2
answers
434
views
How to use SEQUENCE formula in Excel data validation
I have input data like this:
Start Number: 88
Count: 7
I can then use the SEQUENCE function like this to display a list starting from 88 and iterating for 7
Eg:
=SEQUENCE(C4,1,C3)
Assuming ...
- 9,864
0
votes
2
answers
166
views
Dynamically Inserting a row and iterating over it in VBA?
During reverse iteration of a range of cells (down to up going right to left), a row is inserted above the current one and and shifts down.
Sub ReverseIterateAndInsertRowRestart()
Dim ws As ...
- 753
0
votes
3
answers
176
views
MS Excel sorting multiple columns and have empty cells first with VBA
In Microsoft Excel, sorting a column or multiple columns in ascending order (A-Z) sends empty cells to the bottom of each list in the column. Moreover, it provides no options for dealing with empty ...
- 753
-2
votes
2
answers
731
views
Create Weekly Date Range in Excel
Much appreciate all of you for helping me to solve my case in Excel.
I want to create a date range in Excel but the data I have will have a lot of repetition because it is based on the ticket creation ...
- 25
0
votes
4
answers
104
views
find closed match value in matrix and return its row and column header in excel
I'm trying to return the row header and column header based on the value I'm looking for in matrix .
How can I find the lower closed match on matrix and return its row and column header in single cell?...
0
votes
3
answers
64
views
Count the highest consecutive occurrence and then add the sum only the highest consecutive occurence
I have a formula to count consecutive negative occurrences
=MAX(FREQUENCY(IF($AB4:$AB16<0,ROW($AB4:$AB16)),IF($AB4:$AB16>=0,ROW($AB4:$AB16))))
However, I'd like to add another part to count the ...
user27443005
0
votes
1
answer
90
views
Dynamic list which adds selected items into a single cell on new rows
I have students, teachers and teacher planning worksheets and need to find a neat and dynamic solution to the following in the 'Scheduling' table:
lookup teacher names - this I can do!
based on ...
- 23
2
votes
2
answers
93
views
I need to fix this formula so that the columns letters go UP as I drag DOWN
So specifically with the following formula, how do i fix make the range say =B2:b21, =C2:c21. =D2:d21, etc when i drag down?
The full formula is:
=TEXTJOIN(", ",TRUE,FILTER('Staff Rota'!$A$2:...
- 23
0
votes
0
answers
142
views
Excel INDEX function gives back just one column if I wirte {1,2} array in the optional column pos)
Table1
Column A
Column B
1
A
2
B
3
C
this is my function:
=INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,2})
and the result is:
I expect to get back the whole table...
Could you please help me?
I use ...
0
votes
0
answers
62
views
Drop down menu Autofill
Hey I need help in this
example:
I first solved the proplem of showing the correct cities based on the what was selected in the first drop menu by using this formulas in data validation: (coutry and ...
0
votes
1
answer
72
views
How to make Dynamic Drop Down Menu
I have two Drop Down Menus and I want to make the second show values based on what I picked in the first one.
I used IF function in data validation but it doesn't work.
Here is the example:
I have ...
0
votes
3
answers
117
views
Speed up column insertion
In Windows 11 / Office 2021, I want to insert information in column A 51 times, skipping column D, column F, column H, and column CZ.
This runs slowly.
Sub 列挿入()
Dim stepNo As Integer
Dim copyStart ...
- 1
0
votes
1
answer
72
views
Removing or highlighting duplicate values from same row (client order) (but not from a different order)
I have let's say, some client orders (each order is different or belongs to different client) and I want in the same order to check if there is a duplicate model input on the model category as shown ...
- 17
1
vote
1
answer
56
views
Enter data in input field in Selenium
Below is the element details of input field
<input name="aadharNumber" placeholder="Enter Aadhaar No." error="[object Object]" maxlength="12" type="text&...
- 11
-2
votes
1
answer
181
views
Line of succession based on family tree
I have following data in Excel.
I need above data in below format.
As Elizabeth II has child Charles & Charles has child then Charles will be in the next line after Elizabeth & same for the ...
- 1
0
votes
2
answers
183
views
Excel search function problem when trying to display only certain columns
[Office Professional 2021]
I have an Excel workbook made of 2 sheets:
--1st sheet is named Database_locale and it had a table that has data from column A to column L and this database is updated every ...
1
vote
1
answer
434
views
Lookup multiple criteria from column 2 in a table and return the value in column 1 if all match
I'm essentially trying to lookup multiple items located in the data table in column 2 and return the corresponding order number from column 1 IF all the items match.
I was thinking I may have to ...
- 47
0
votes
1
answer
421
views
Risk Matrix Formula Excel
I am trying to get it so that the Impact column is auto-populated with the text in the below matrix when the user selects a value from both dropdown menus.
As you can see I've tried using Index/Match ...
- 87
0
votes
2
answers
3k
views
how to create a checkbox in a cell if the cell next to it contains text?
I have used a filter formula and I want to make a column that shows checkboxes for every row that contains text from the filter formula result
I used this formula =FILTER(Table_1;ISNUM(SEARCH(C9;...
0
votes
1
answer
39
views
Formula evaluation returns weird value
I created a formula in excel to get the iso.weeknumber in 2 numbers:
=JAAR(A2)&" "&ALS(LENGTE(ISO.WEEKNUMMER(A2)=1);"0"&ISO.WEEKNUMMER(A2);ISO.WEEKNUMMER(A2))
The ...
- 359
0
votes
1
answer
2k
views
Insert images to cells based on cell value
I need to insert one or two images from a folder on my PC to an Excel worksheet.
I need to insert an image in cell (H43) based on the value in cell (E5) and an image in cell (L43) based on the value ...
- 29
0
votes
0
answers
89
views
Place two positive x-axes of different range beside each other (Excel 2021)
I was wondering if there is a way to reproduce this kind of graph in Excel 2021. The reason being is a need to compare two sets of data. They have a similar y-axis, which is time in seconds, but the ...
- 13
0
votes
0
answers
60
views
Make auto fill form a part of table
I have a table (table1) then is my data table same as below
Table1
and I have a form then that have a table inside it same as below
Form
I need when I enter the "POD" from "Table1" ...
0
votes
2
answers
57
views
How can I check if any of the values in a column with comma seaparated values are in another table in Excel?
I have a table like so:
IDs
data
one, two
a
three, four one
b
five, six
c
seven
d
eight, nine, three
e
I have another table like so:
ID
one
four
six
nine
I need to add a column to the first table that ...
- 5,933
0
votes
2
answers
239
views
How to find sum of a group of items' values based on the last value of each item?
In Excel, I have a table of historical value data of different items in multiple groups of items. As a new item's record entered, the old value of that item has to be abandoned and the total values of ...
- 25
-1
votes
2
answers
598
views
Sumifs with OR condition on 2nd criteria [duplicate]
I am trying to SUMIFs total product quantity based on product's SKU and Status.
To keep track of my inventory, I used SUMIFs function to sum the products SKUs and their quantities. But i also wish to ...
- 7
0
votes
1
answer
230
views
How to protect a header row while allowing deletion of inserted columns
I have a worksheet and want to prevent the user from editing the header row cells but allow the user to insert and delete their own columns freely. When I try this, I am able to insert columns, but ...
- 1
1
vote
1
answer
124
views
Count distinct and unique values in pivot table or other way?
I have a contract table, with amendment, type and stats columns. But the table source lists the contract and all amendments as a separate row.
I need to categorize contracts by their type and status, ...
0
votes
4
answers
2k
views
If Cell has Specific Text then Multiply 3 Cells - Office Pro Plus 2021
I am trying to make a form where if Column B contains "Carcass" or "Benchtop" then Column H will show E * F * G/1000000
But if it shows Misc or Hardware etc, it will show Column G
...
- 7
2
votes
2
answers
987
views
Why CHOOSE function doesn't return an dynamic array correctly?
I'm trying to use the CHOOSE function to return just a few non adjacents columns from a table, based on this answer, but the result doesn't look correct.
Simulating the same problem in the table below,...
0
votes
0
answers
3k
views
Pivot table "Sum of Value" shows 0 and only count works
I have a data set with no blank cells and the format of all cells are numbers. When I try to create a pivot table for it, it by default takes the count of value. When I try to change the value field ...
- 11
0
votes
2
answers
118
views
How to allocate amount of rows according to amount of rows in another sheet
How do I insert amount of rows according to data from another sheet?
I have 5 data out of the 10 rows I have (I keep the empty rows for spare)
How do I make it so the data here corresponds to the ...
0
votes
1
answer
29
views
How can I split the words in a range to a column?
I want the data of the range A2:B6 to be placed inside the column C with the following conditions: non-duplicate, non-empty and sorted.
I have Microsoft Excel 2021.
please help me.
Screenshot01
Myfile
...
0
votes
0
answers
168
views
Embed/Host Excel into WPF application
I am trying to embed an excel workbook into WPF application. I have used the Microsoft.Office.Interop.Excel for achieving this, It works and opens the excel workbook in WPF application.
Problem: When ...
1
vote
2
answers
115
views
Worksheet_change working, but won't call a sub routine
I've been searching for an answer but nothing fits my problem. I have Sheet 1 where I've created a private sub ("disable1") to enable or disable a checkbox based on the value of a cell. I ...
- 25