Questions tagged [excel]

Only for questions on programming against Excel objects or files, or complex formula development. You may combine the Excel tag with VBA, VSTO, C#, VB.NET, PowerShell, OLE automation, and other programming related tags and questions if applicable. General help regarding MS Excel for single worksheet functions is available at Super User.

Filter by
Sorted by
Tagged with
0
votes
0answers
4 views

How to enable conditional formatting based on if-else condition on 2 previous cells containing dates?

Cell A1, C1 always contains a date. Cell B1 contains either a date or NA or maybe empty. If B1 contains date, then I want C1 to become red on 2nd work day onwards after B1. If B1 is empty or contains ...
-2
votes
0answers
7 views

How to copy & paste data from Sheet1 to the next blank cell in another worksheet (Sheet2)?

I have a worksheet titled "Raw" and another worksheet titled "SIRs". There is raw data in the "Raw" worksheet scattered everywhere; I need to copy some data in the "Raw" worksheet and organize it by ...
1
vote
2answers
12 views

Excel - Remove a specific decimal in a cell

I am having difficulty removing just the last decimal from my cells. Any suggestions? Example: 0101.29.00.90 to 0101.29.0090
0
votes
0answers
7 views

Select Item in Pivot Slicer when A1 Value Changes

I'm trying to select an item in Pivot Slicer when I change "A1" value. So when I change the "A1" value to "ABC" the code should look for "ABC" in the Slicer Item and select it. "A1" value will be same ...
-1
votes
1answer
13 views

How can I send multiple e-mails, to multiple recipients using VBA?

I have the following list on a sheet: https://drive.google.com/file/d/13GOG5o42nPWCubTxXOTFMtlIZm0JsUSk/view?usp=sharing My goal is to use VBA to send an email to a manager, with the portion of the ...
1
vote
0answers
17 views

Copy data from one Excel sheet to another

I'm still fairly new to coding, so I'm sure that there are easier or prettier ways to write the following script. The script runs and the new sheets are created within the workbook, however, the data ...
-1
votes
0answers
8 views

Excel Automate Refresh All Connections with password inputed

I currently have 2 connections in my excel sheet to oracle database table and i want to refresh all connections to update all of the information to my excel sheet daily. Is there a vba code that would ...
0
votes
1answer
13 views

How to enable conditional formatting so that a cell becomes red whenever today's date becomes previous cell's date + work days?

Cell A1 (till A10) contains random date. I want B2 (till B10) to become red on the 3rd work day after A1. Also, if B is empty or NA then dont color B. For example: if A1 = 18Oct, then B1 should ...
0
votes
0answers
17 views

Import Data into an Excel Spreadsheet from Google Sheet

I have a Google Form which is populating a Google Sheet. Then this Google Sheet I need to be able to connect to an Excel Spreadsheet. I have been able to use the 'publish on web' method with the ....
0
votes
0answers
12 views

Is there any way to adjust datalable position automatically in ms-excel radar chart?

I want to generate some radar chart by ms-excel interop library.but when i set datasource to the chart,the datalable's position is ugly like below picture. is there any seetings i can make to let the ...
0
votes
2answers
20 views

Slot machine: Multiple IFs formula parsing error

am learning excel on my own and encountered an issue with this formula. I’m trying to simulate a slot machine random number kinda thing, where: -if you have no same number, you lose. -if you have 2 ...
0
votes
0answers
19 views

Python datetime to Unix epoch timestamp

I am trying to convert pandas datetime object to unix/epoch timestamp. I am getting an error for one particular date in the dataset. The timestamps I am trying are these: 1) 2019-08-31 21:38:00 2)...
-2
votes
0answers
17 views

if statement Macro

I am working on a multiple Sheet if statement can you please see what Macro code for this, I was trying to set that formula for a value on sheet 1 from sheet 3. =IF(ISBLANK('Download Pad (For ...
0
votes
0answers
11 views

Calculating a weighted, variable metric group score

Question: Given three metric groups, the weighting of each group to the total, and the number of metrics to measure in each group, how do I calculate the group metric score. The individual metric ...
0
votes
0answers
12 views

Creating a data series linear trend to fill between cells in the same column

I am currently working with a modified collection of data with gaps in each recorded value. I would like to use Excel's fill utility under the Home tab (editing(group)->fill->series. with Rows,linear,...
0
votes
0answers
21 views

How can I create multiple CSV Files from one excel sheet - VBA

new to VBA. I am trying to create multiple CSV files from a source file. In the example, I am changing the name of file 20191.xlsx to a CSV file, and then outputting it based on another file name (...
0
votes
0answers
24 views

Update existing chart in PowerPoint using embedded excel data

I tried changing range of PowerPoint chart programmatically using Excel.ListObject chartTable = (Ppt.ChartData)chartData.Workbook.WorkSheet[1].ListObjects[1]; chartTable.Resize("desired range"); ...
1
vote
0answers
7 views

Why does xlwings.App(visible = False) not work in my code?

I am trying to use xlwings to loop through my excel calculations using Python. I have a function that uses xlwings: def obtain_gwp(filename): app = xw.App(visible=False) print(app.visible) ...
0
votes
0answers
16 views

Excel Pivot Emp Hierarchy

I am trying to get my pivot table working based on the simple employee hierarchy as attached. I need to be able to roll up the value( total) of Hardware, Software and MGT for each org level using ...
-2
votes
0answers
14 views

web based tool for processing and displaying Excel spreadsheets that uses React [on hold]

I am trying to write make a web based tool, and I'd like to see if it's possible using React. The is a GUI for a recommendation system. The way the recommendations are found is by doing ...
-1
votes
0answers
12 views

Does File System Task work with read only excel files?

Hello I have a package that loops through some excel files, loads etc and moves them to an archive folder.. My package worked fine several days ago when I was using .txt files that I created from the ...
0
votes
0answers
13 views

Date Differences

I need to create a dimension table from the current "Data Set" in order to be used in Excel and Power Bi. As for the moment the information has been saved in an excel workbook. I've simplified a ...
0
votes
1answer
22 views

Operator '<' cannot be applied to types 'number' and 'Promise<void>' with an Excel Add-In

I am making an Excel add-in and I want to convert the data of a spreadsheet in Excel into a 2D array in Javascript but I can't get data to convert and I have been unable to figure out how to fix it, ...
1
vote
1answer
31 views

How can I use Onaction VBA command without knowing sheet name?

I am trying to use the following macro to assign vba code to a shape. When using the code I get Runtime Error 438 "Object doesnt support this property or method" My sheet number always changes, so ...
0
votes
0answers
16 views

Copy filtered rows with specific column without headers to new sheet after existing rows

I have a source excel data with 'n' number of columns which contains repeated group of data in groups of 'x' number of columns. I would like to copy first group with header, non empty and specific ...
0
votes
1answer
25 views

Solving a complicated matrix problem in excel

First time asking for advice here so I apologize if my terminology is messed up. Here is what I'm trying to figure out how to solve (ideally in excel): Given I can have up to 5 groups (A), and each ...
1
vote
0answers
13 views

How can I compare sets of categorical data to a sum of those categories in Excel? Needs to filter and chart

I have a survey data set that I need to periodically update and produce reports out of in Excel 2016. I want to filter the data by location and then compare locations by year>quarter to a total from ...
0
votes
1answer
37 views

Finding the next sequence number in vba

I am creating a button that can automatically enter a number in order. So on cell "A1" the cell is the title and it is Title Number. "A2" is blank. I would like it, that if I click on my button, A2 ...
2
votes
0answers
28 views

How to find the most updated file and change the file name into a standard one

We have a daily report sending to a folder, and then the next day, our coordinator will generate a summary by data query connecting the most updated file into the master file. But the file has a ...
1
vote
0answers
21 views

Openpyxl returns wrong hyperlink address after delete_rows()

Problem: I have a program that scrapes Twitter and returns the results in an excel file. Part of each entry is a column containing a hyperlink to the Tweet and image included in the Tweet if ...
1
vote
0answers
38 views

How do I get a function to paste data while eliminating any trailing blank cells?

I've got a function here that seems to work sometimes, and other times it refuses to work and treats the ranges that I feed as if they don't exist. I need this function to paste data from a source (...
0
votes
1answer
31 views

Nested excel formula

There is a formula I would like to edit in excel This is the original formula: =IF(AF22 = 0, 0, ROUND(((AG22)) * (AU22 /BE22), 2)) AF = Payment AG = Gross Interest AU = Fee BE = Rate I would like ...
0
votes
3answers
34 views

How to convert this condition to an excel function ..?

Please, I need to write this condition in excel as a function : if( A1 = 2 AND A2= 3 ) then { A3 will equal 10} else if( A1 = 4 AND A2= 5 ) then { A3 will equal 20} else if ( A1 = 6 AND A2= 7 )...
1
vote
1answer
28 views

COUNTIF Across Multiple “renamed” worksheets

What I am trying to do is count a single column from every sheet and indentify how many pass, fail and N/A. One more forumla for each as they will be displayed in different cells (One cell for pass, ...
3
votes
1answer
32 views

Beforesave and Aftersave ignored when saving as attachment

I have some code that I have put in Workbook_AfterSave and Workbook_BeforeSave. It works great and does exactly what I want; however, during testing, I found an odd situation where Excel doesn't call ...
0
votes
1answer
50 views

EXCEL VBA how to reset variable in for .. do loop

Is there anyone who knows how to reset the retained variable to 0 in a do loop? the variable I would like to reset is idx. IDX represent the number of subfolder being searched. For example there are ...
-1
votes
0answers
8 views

Excel Data bind using Ribbon command ExecuteFunction

Required to bind API data into Excel from custom ribbon command using officejs in typescript with ExecuteFunction with sample code
0
votes
1answer
20 views

Excel SUM: Formula for column from midway to rolling end point

I am creating a spreadsheet that shows the shifts I do at work. Column A represents the date of the shift, and then B-F represents the different shift role. I put =1 in the correct B-F column, and ...
0
votes
0answers
18 views

How to count last row of a table that contain a value with vba [duplicate]

Good evening, I have a table that may or may not contain values such as the figure below for example: I want to count the last row of "Circuit" of the table that contains a value.In the case of the ...
0
votes
1answer
48 views

How to find the next available column and apply a formula to it

I'm trying to change my designated column "J" to a variable (the next free column on row 5) as sometimes J is in use. Then apply the formula to that column and copy it, inserting after Column A. I ...
0
votes
1answer
12 views

VBA, split worksheets into multiple files and skip hidden files

I found this code a couple years ago and it worked awesome! What it does is it takes the excel workbook, splits all the sheets, and then saves each sheet as it's own book. I tried re-using it but ...
0
votes
1answer
23 views

DAX formula to COUNT() or DISCTINTCOUNT() based on condition?

I want to include a new measure using a DAX formula in a Pivot Table. I have the following DAX formula to count unique values in Column1 = DISTINCTCOUNT(Table1[Column1]) How can I COUNT() values ...
0
votes
1answer
49 views

Excel Macro gives run-time error '1004' every other time

My macro produces a "Run-time error '1004': Application-defined or object-defined error" every other time it runs. The error occurs on ".Add" under cel.Validation. Originally the code gave the error ...
0
votes
0answers
25 views

Teradata TXT File Import via VBA Excel

I have a text file(~300,000 records) that is created via multiple combined reports(some from an OBDC connection to Teradata and some not). I need to import that file into Teradata. I know I can use ...
0
votes
0answers
29 views

Using VBA for Excel: Pulling data from ODBC SQL, how do use a value of “one year before today”?

I recorded a macro that works fine, but I want to use a dynamic date so I don't have to enter it every time. I want to use the WHERE statement to pull records between today and one year prior to today....
0
votes
0answers
24 views

VBA macro to create individual pdfs of different ranges in different sheets

I am trying to create a macro button that will create pdf's for a particular range in more than one sheet and not necessarily the sheet that the macro will sit in. I know that the below code works ...
0
votes
1answer
24 views

Selecting multiple rows relative to active cell

I'm trying to create a VBA macro to select two rows above the activecell then autofill the two selected rows down one. ActiveCell.EntireRow.Offset(-2, 0).Select selects the entire row (2 rows ...
0
votes
0answers
27 views

TEXT formula reserved letters

I use the following formula (my excel is in german) : =TEXT(HEUTE(),"tt.mm.jjjj") let's say I send this formula to a colleague in the US (the formula text, not the workbook), this would not work ...
0
votes
0answers
27 views

VBA Excel: how to use a variable for the file number used for open, print and close

I would like to use a variable for the file number, instead of hard-coding it. I have tried with conversions like CDbl to have a double, as it seems that #1 means convert 1 into double.. But this did ...
0
votes
0answers
7 views

Form control buttons in .xlsm files turn into images after saving with openpyxl

I'm using openpyxl to read and edit an Excel macro with buttons included in the file. When trying to save the file using openpyxl, the buttons turn into images and can no longer be clicked. My VBA ...