386 questions
0
votes
0
answers
79
views
Power Pivot for Excel fails to retrieve duplicate row from query
I'm fetching data from a SQL Server database through a query in Power Pivot / Excel 365.
It's to gather sales data from the rows of all the invoices in a particular time frame so I'm extracting fields ...
Advice
0
votes
4
replies
109
views
How to mark two largest values?
I need advice on how to mark the two largest values among a set of values.
1
3
2
4
FALSE
TRUE
FALSE
TRUE
- 5,607
0
votes
3
answers
111
views
Counting multiple ranges based on a single criteria [closed]
I have a data set where I have my criteria in Column A which is an alpha numeric code, and my values in Columns C through H. There are duplicates in Column A and I need these to be captured and not ...
- 23
2
votes
1
answer
152
views
VBA code to check if user-input filename is a duplicate
VBA newbie here. What I'm trying to do: Write a macro that brings up the Save As box with a filename pre-filled in, which the end user can edit if they choose. If the file name already exists in the ...
- 11
5
votes
3
answers
252
views
Expand records for each month between two dates
I have a few members lists for donations for a campaign. Individual members will to contribute for a duration.
Here is an example:
ID
Name
Donation Amount
Donation Start Date
Donation End Date
Status
...
- 38.1k
0
votes
2
answers
101
views
Try to open Excel.xlsm file using xlwings results in Com-Error
I'm currently working on a project where I need to open an .xlsm Excel file through my code.
However, I keep running into the following error:
öffnen
Traceback (most recent call last):
File "C:\...
- 11
3
votes
6
answers
235
views
Attempting to create a pivot-like report using a formula
I am working with this data and attempting to create a pivot-like report using a formula, with months displayed as columns, status categories as rows, and the corresponding status counts under each ...
- 611
1
vote
0
answers
167
views
Is there a way to define a class Function call "If" in Excel VBA?
I am trying to implement a boolean based logic custom class for my application.
I am trying to define a Public Function If(trueValue as Double, falseValue as Double) As Double. The logic is if the ...
- 5,025
0
votes
1
answer
79
views
Excel/Excel 365 - =FILTER() function fallback
On Excel365, I am using a basic FILTER formula to retrieve data from a different Workbook (Workbook2) based on the cell content inside Workbook1.
For a given input in cell A1, which is a fiscal year (...
- 1
2
votes
1
answer
86
views
how to use Paramarray in Property Let in VBA (excel365)?
I am trying to write a VBA class with Get/Let properties of an internal dictionary.
here is the implementation of the properties.
Private m_Values as New Scripting.Dictionary ' I've added the ...
- 5,025
1
vote
1
answer
115
views
How to implement a Default property usage in nested class hierarchy inside VBA?
Consider the following code
parent.cls
Public Child as New ChildClass
ChildClass.cls
Private m_internal As New Collection
Public Property Get Item(index As Integer) As Variant
Attribute Item....
- 5,025
2
votes
4
answers
169
views
Cross sorting arrays
I have a table that list the tickets with a list of tests attached:
And I want to get the list of test with the tickets related to this test:
I'm using Excel Office 365 and the dynamics arrays, so ...
- 351
4
votes
6
answers
286
views
Nested BYROW in Excel?
I have five columns for which I need underlying nested values on a single row. Here is sample data I have:
ID
Product
Sales Manager
Sales Rep
Rep Status
1
Product1
Manager1
Rep1
Active
2
Product1
...
- 53
2
votes
1
answer
188
views
In Excel, why does the output of Unique() include duplicate values?
I am trying to use =TEXTJOIN(";",TRUE,UNIQUE(TEXTSPLIT(A1,";"))) to remove duplicate values from a semicolon separated list of names (as a string in a single cell on each of many ...
- 23
0
votes
0
answers
25
views
In my Excel 365 worksheet: I want to lock a specific worksheet but just let a data validation value box, assigned to a cell, operatable
In my Excel 365 worksheet: I want to lock my worksheet "Home" but just let a data validation value box (not form or active-x combobox), assigned to a cell ("I6"), operatable.
I ...
- 793
0
votes
0
answers
59
views
How can I remove the border of a user form in Excel 365 via vba code?
I have a Userform1 where I try to remove the header and the 3d border that comes as default.
The header part I have realized with a vba script. But the border part I couldn't make
According to Remove ...
- 793
0
votes
1
answer
192
views
Need an excel VBA code to determine whether or not a cell contains an embedded picture
Following are IsPictureInCell and CopyCellPictureToClipboard vba funtions. I'd appreciate your suggested modifications to these two functions so they will correctly work with pictures locked into ...
0
votes
1
answer
102
views
How do I modify my excel IF statement to check for any (not all) True values and not just stop at the first TRUE occurrence
I'm trying to create a formula to count the numbers of colors chosen for a specific child, based on answered selected in various columns. For example, Marsha Chose Primary & Secondary Colors in ...
- 25
0
votes
0
answers
33
views
The active x comboboxes in my office-365 excel worksheet changing size randomly when screen changes, or I open the workbook from other computer
I have a macro enabled Excel 365 workbook (.xlsm) and I have a problem that I cannot fix since months. I have read several entries on different platforms but none of them fixed my problem:
I am using ...
- 793
0
votes
0
answers
47
views
VBA selected print range screws up unhiding excel columns
I created a print control user form to determine which rows and columns within a larger print range are to be hidden, shown and restored after creating a pdf print file. The user form includes a label ...
2
votes
4
answers
128
views
Trying to separate a version from a name
I have a table of ids and need to be able to have two separate columns one with the name and one with the version. The issue I am having is that the version numbers have a varying number of characters....
- 21
-1
votes
1
answer
97
views
Excel 365 web - OLEDB connection
I have an Excel sheet with some macros embedded in it. The macros are mainly created to establish an OLEDB connection to a local server and fetch data from SQL Servers DB.
I am looking for a way which ...
- 147
0
votes
1
answer
134
views
Excel 365: Formula is calculating correctly but displaying incorrectly
I've been working on developing a fairly simple inventory system in Excel 365 and, for the most part, I've been able to implement everything that I wanted to, but there is one feature that hasn't been ...
0
votes
4
answers
171
views
Excel command to find whether a particular month lies between two dates or not
Below are the few dates and I want to know how to check whether Dec-2024 falls between these two dates or not.
Similarly find whether Jan-2025 lies between these dates or not
I have tried If & ...
0
votes
0
answers
31
views
In Excel 365 the intial portion of first column getting truncated
I am using Server side excel automation APIs to generate Excel and I am seeing the resultant Excel is having truncated first column.But if we minimise the Excel file generated and reopen ,it just saw ...
- 99
0
votes
0
answers
27
views
How can I adapt this formula so that it returns all possible permutations, instead of the same number of perms as objects?
Assuming A2:l2 has five words, the formula below returns permutations using all five words.
=LET(z,TOROW(A2:L2,3),REDUCE(TOCOL(z),DROP(z,,1),LAMBDA(a,w,TOCOL(IFS(ISERR(FIND(z,a)),a&z),3))))
See: ...
- 284
0
votes
0
answers
30
views
Still getting randomly uncontrolled active-x control's resizing or repositioning problem in Excel MS 365 V 2402
I’m using Excel 365 Version 2402. Everything works as expected in my macro-enabled Excel workbook, except my active-x ComboBoxes. I know this is a very old issue and there’s so much written about this ...
- 793
0
votes
1
answer
45
views
Challange with DSUM Function: How can I use the DSUM function to sum values based on criteria that match the starting characters of a field?
I am facing some challang in summing up values using DSUM function in Excel.
Below are the detils.
I have a table:
ID Region Item Category Quantity Price Stock
======================...
1
vote
1
answer
175
views
extract text from a web page code vba excel
I have this example code where a method is used to extract text from a web page. In the complete code, these pages are then used to extract a whole series of data very quickly. The complete code has ...
- 11
0
votes
1
answer
101
views
UserForm Error: Run-Time Error 5 (Invalid Procedure Call or Argument)
UserForm UI
I have created an Inventory Management System for my company, using an Excel UserForm.
It is supposed to take the following inputs:
Product Type: Dropdown
Product Name: Dropdown
Date of ...
1
vote
2
answers
131
views
In Excel formula I am trying to split a total savings value between goals but once a goal is reached allocate to the remaining to the other goals
In this table below I am trying to create a formula in Column D under 'Allocated' that allocates a split of D1 based on the goal and allocation %, once an allocated amount reaches the target amount it ...
0
votes
1
answer
135
views
Microsoft Office Scripts - Move cell value based on another cell's value
I'm looking to loop through each row and based on a cell value I want to move another cell's value to new cell.
Example: Loop through each row if column "Method" has a value "D" ...
1
vote
1
answer
161
views
Using the number format in excel to represent numbers in Cr, L and K
Pure excel formatting question:
I want a number formatting to provide this output.
I have tried the following techniques.
[>=1000000]0,,"L";[>=1000]0,"K";0
This provides ...
- 43
0
votes
2
answers
78
views
Macro delete multiple rows selected text and date
I'm looking for a macro that is formulated to search rows and select specific text in column A for deletion that contain the text "Summary", "Contract", "Amount", "...
1
vote
1
answer
87
views
Why Is Range Worksheet's Object Failing?
I'm trying to change the value of the cell "L83", as another cell is updated manually. those cells make part of a table.
this is the code that I'm using:
Sub UpdateCell()
...
- 109
0
votes
2
answers
70
views
Is there a way to create a macro that converts these hyperlinks into embedded images in Excel?
I frequently generate spreadsheets of multiple rows of images. Before our database switched platforms, I was able to use a macro that converted the hyperlinks into embedded images. However, since ...
0
votes
1
answer
66
views
Create 48 month array of summed arrays offset and multiplied for each month
I've hunted around and know the answer will be in here somewhere, but where?
I have a 48 month revenue profile and would like to multiply it by the number of such customers won in each of 48 months ...
- 1
0
votes
2
answers
81
views
How do I bring across the background colour with the text from a drop-down list?
I have an Excel 365 workbook with 2 tabs – Lists and Detail. On the Lists tab I have a list of Responsible Parties like this
Users are able to add more items by inserting above the N/A cell and they ...
- 1
0
votes
1
answer
293
views
How to add a missing column to the Transform Sample File
Background
I have a Power Query which combines multiple excel workbooks with the same table name in the same directory into a single table which I will then be using for a source data for a pivot ...
- 9,984
0
votes
1
answer
100
views
How to unload a userform when Power Query refresh completes when workbook is opened
Background
I have an excel workbook with about 5 power queries in it. They are set to unload data on save and refresh on open. This is set through the power query as I have been told the workbook ...
- 9,984
1
vote
0
answers
127
views
Data Validation Dependent Drop Down List Error With Table
What I am trying to do:
I am trying to add a dependent drop down list via VBA.
Problem:
When I try to step through the code I get an error at the .add line
Sub TaskNoValidation(InputTable As ...
- 9,984
1
vote
1
answer
81
views
IsError not capturing error returned by expression
What I am trying to do
I am trying to determine the first column in a named table that has a date.
What I have tried
Since all data in a table header row are stored as strings I figured I would have ...
- 9,984
2
votes
1
answer
73
views
Is there a string metric for the dictionary distance between two words?
If I use Microsoft Excel to sort the strings
Abba
Abbaa
Abbaz
it will sort them in the order above - ignoring the case, as I want it to do.
However, a measurement like Levenshtein distance would give ...
- 284
1
vote
2
answers
86
views
ActiveSheet.ExportAsFixedFormat Not always printing all active sheets
have 2 Subs that do the exact same thing, but for different Worksheets.
Unhide 7 sheets, select all, print, hide them all.
Sub PrintStage2() works as expected and exports all selected worksheets.
Sub ...
- 21
0
votes
0
answers
77
views
Can an office 365 addin write and execute an office script?
I have an Excel spreadsheet for which I have written an office 365 addin hosted in Microsoft Azure. The addin creates a Taskpane for the user which makes their interactions with the spreadsheet more ...
- 11
-1
votes
2
answers
123
views
Scoring values including NA() - Excel 365
Let's say I have the following array of numbers:
$222.71
$370.00
$224.93
#N/A
$230.00
$175.00
$160.00
$248.00
$150.00
I would like to score/rate the numbers above using some arbitrary ...
- 7,689
0
votes
1
answer
140
views
Excel-365 functions don't work as expected
While creating the solution for "How to Merge Rows values into a Column on a prior Row?" I composed the formula:
[G11] =LET(
c,COLUMNS(Table1),
at_l,INDEX(Table1,0,c),
n,ROWS(Table1),
...
- 5,607
1
vote
1
answer
69
views
Rank numbers including NA()s in Excel 365
Let's say I have a list of numbers including a few NA()s.
In the previous versions of Excel I have used the following formula and it worked (not very elegant mind you):
=IFERROR(MATCH(F8, AGGREGATE(15,...
- 7,689
0
votes
1
answer
444
views
Highlighting cell if data doesn't match column on different tab
I have a spreadsheet with multiple tabs. Basically, I would like for Column B on tab 2 to highlight any cell that doesn't match any of the cells on Column A on tab 1.
The formula I'm using doesn't ...
0
votes
0
answers
50
views
excel automation to copy and paste values within a column where the first row equals a data value specified on a different worksheet
Is there a way to get an automate scrip to read a value in a given cell on one worksheet, and then find that value in row 1 of a second worksheet and then copy & paste values for all cells in that ...
