Microsoft Excel

137K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

Experts,

in a previous ask to "Convert Months to Days for Total price Calculation". I had an issue with entering a "Renewal Date" into the userform. In the attached workbook is the fix.  

This issue was fixed but changes are not incorporated into this worksheet 29173473a.xlsm. Please incorporate the fixed code.


https://filedb.experts-exchange.com/incoming/2020/02_w08/1444654/29173473a.xlsm

https://filedb.experts-exchange.com/incoming/2020/02_w07/1443481/29170885a.xlsm
0
Experts,

I need help with converting the attached link file worksheets Original Quote and RAW_DATA to a Table format with the ability to convert back to a range while having the code to still function correctly.

https://filedb.experts-exchange.com/incoming/2020/01_w03/1440849/29169174.xlsm
0
Hi:

If you have a SQL view in Excel and if the Excel spreadsheet is the data source for Power BI, is it possible to refresh the dataset without having to refresh the actual spreadsheet and if so how?

Thanks!

Software Engineer


John
0
I would like to modify this script or even have another script run on the output if needed, so that any data in the output file which are "numbers stored as text"
will be converted to "numbers"... and the file resaved.
CSV-to-Excel-Copy.ps1
0
I have an Excel add-in firing an HRESULT 0x800401A8 error, when calling the getter of Worksheet.Parent.

I would be glad if anyone could let me know what the error might be, but more importantly and more generally, I would like to know how I can understand these HResult codes?

I did the following but am still confused:

Read the Microsoft's documentation on the Structure of COM error Codes.
Checked this SO thread.
Tried my chances with Magnum DB.

Thanks!
0
The attached file with script removes "decis" but would like to combine both "remove Deci" scripts from prior posts into one script and make it faster.

The attached file contains two additional sheets to test with.
CombineRemoveDecisandMakeFaster.xlsb
0
The idea is to create a script that removes rows where there are 2 or more numbers from different "decis". A deci is defined as numbers from 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, and 90-99,

There should be:

1) a prompt would ask for input of "Maximum numbers allowed from different decis" or something similar. If the input is one (1) then
2) choose the sheet to be processed
3) sheet "Numbers_3" would be edited as follows:

 This process should be able to be repeated for any sheet similar to "Numbers_3"

Remove rows containing 2 or more numbers from different decis
Notice how all the green rows contain number from only 1 "deci". That is the goal.
Remove-rows-with-2-or-more-numbers-.xlsb
0
Is there a way to have a single excel workbook shared with multiple users where each user can only SEE a single sheet after supplying login credentials?  I know you can prevent editing in this manner but I want to prevent viewing as well.

I am trying to create a simple password keeper for a small organization so that the users can stop using sticky notes stuck to their monitor for this purpose.  I am open to other suggestions but they have to be free and easy to use.

Thanks so much!
0
I want to bring data from one spreadsheet to another, if a field is 'No', then pull comments from D5 over to another sheet.  

So one sheet - D5 is 'Yes' or 'No' - if 'No' and D6 are comments.  I would like the data to appear on another sheet.  

If 'Sheet1' - D5 = 'No' then pull data from 'Sheet1' - D2.   Thanks Experts.
0
Hi
I have an excel sheet with Names, email and passwords
I want to send an email to each individual on the list with their password, also with some text explaining what it is used for.
I guess I could use word and merge the excel sheet data
But don't know how I can get it to send separate emails to the individual
Any ideas or guidance please

Gordon
0
Hallo,
I have a list of dates in A12 -   only workdays.
How can I add 2 blanks for Sat and Sunday
Should look like this:
Excel
Thank you very much.
Best regards,
Thomas
Zeiterfassung-2020.01-Expert-Exchang.xls
0
The idea is to create a script that allows a maximum of "X" numbers from same "deci". A deci is defined as numbers from 1-9, 10-19, 20-29, 30-39, 40-49, 50-59, 60-69, 70-79, 80-89, and 90-99,

There should be:

1) a prompt would ask for input of "Maximum numbers allowed from same deci" or something similar. If the input is one (1) then
2) ask to click on any cell that contains numbers to be processed then
3) sheet "Numbers_3" would be edited as follows:

 This process should be able to be repeated for any sheet similar to "Numbers_3"

max of "X" from same DeciThe picture shows items in RED that should be removed they all contain more than one number from the same deci. The sample numbers that should remain are 3, 7, 10, 20, 30 since only 3 and 7 are from same deci. 3, 7, 10, 20 and 29 are shown red since it contains 3 and 7 from same deci plus 20 and 29 from same deci. Thus it does not meet the criteria.
AllowMax_X_fromsameDeci.xlsm
0
Hello. Im requesting assistance with the following. I have a excel file that i want to filter according to the dropdown list selected. attached is the example file. In the "main" tab is where the table and the dropdown list will be featured. Once the table is filtered to the dropdown selection, the last column (column E on the "main" tab in the attachment) needs to be answered either with YES / NO. Once i have answered all the rows, I want excel to record the options selected in the "results" tab.

the "results" tab will have the same columns as 'main" tab.

Your assistance is greatly appreciated.
work_deck.xlsx
0
I have 2 seperate worksheets. I choose the range from one column to paste into another blank worksheet for analysis and to create a table. IS there an easier way then choosing the top of the column and having to scroll down to the end of my records? There are 1,000 records in some and more in others. I dont want the whole column as I am chosing a range and creating a table in my new worksheet.
0
I have 2 tables in power query I want to comare the id column in each table and find the matches? How do I do this?
0
The attached file contains code that makes combinations from data in "sheet1" B10:AG19  and puts them in sheets "Numbers_X" where "X" is the first number in B10:B19.

When the code is executed again it deletes data in sheets "NumbersX" then writes new data.

The goal here is to edit the code to maximize the number of combinations made.. The current script makes combinations using 32 numbers from "sheet1" (grey / white alternate for easier view). The current code also uses the first number as the only common number as can be seen in sheets "Numbers_X".

The additional numbers have been highlighted light and dark yellow. If it is not possible to max at 69-70 then I'll accept the maximum possible under the circumstances. Please specify the maximum possible numbers used so I can adjust the data accordingly.
CreateMaxCombinationsForSpecificGro.xlsm
0
Excel
ExcelHallo,
you see from row A12 the dates of the month are listed, according to the value in B3 Month and B4 Year.

Is it possible only to show Monday to Friday and no Sat and Sunday?
I am new with excel and have no experience at all,
Thank you for your help.
Best regards, Thomas
0
Hi Excel Formula Experts!

I'm creating an Excel spreadsheet and want to make a date go RED and BOLD ; Eight Days before the date entered in the sheet.

The dates are currently just Black and Bold. See the snapshot below.

Sample Snapshot
The cell sample format I'm using for the dates is currently formatted with is the pre-set in Excel as "14-Mar-2012"

Is it possible to put in a formula for the Cell Formats so that the dates will turn RED and BOLD automatically when the sheet is opened and stay that way?

So that the first date turns permanently RED and BOLD on: - 23rd Feb 2020
And the second date turns permanently RED and BOLD on: - 19th Apr 2020
And the third date turns permanently RED and BOLD on: - 31st May 2020

Possible? If so, what is the cell format formula that I would need to use?

Many thanks for any help.

Regards, Andrew
0
Hallo,
I have a question about my excel program.
Excel
From A12 you see I have the dates with day of the week.
Is it possible  when I have a Monday-Thursday to set automatic
in I=7 J=0 K=15 L=30 and N=0,75 O=7,75

and on a Friday I=7 J=0 K=12 L=0 and N=0,25 O=4,75

But I also have to manually change the rows and override the values above by my self.
Is this even possible?
I am new with excel and have no experience at all,
Thank you for your help.
Best regards, Thomas
0
Hi Experts,
 I have one Excel file in one folder (C:/Source). I have also on Macro enable excel file where i need to copy source data to Macro enabled file with formatting. I need your help to write Macro to get file from Source folder and paste to Output tab.    I am attaching Input and Report file with sample data,

Can you please help me?

Source File

Input
Desired Output
OutputInput-FIle.xlsx
Report.xlsm
0
Hello Experts,

I have a page(with datagrid), which has an export option to CSV when exported all the data comes as expected but one of the cell value shows #NAME? when I click on it under the formula bar I see the actual value. E.g. --TEST is the actual cell value, but I see #NAME? instead. When I click on the cell, I see the value in the formula bar. I don't have a formula for that cell and also it happens only for some cells, other cells under the same column shows what I see on the page(datagrid)

Please help, what exactly happening.
0
I have an Excel VBA module that sends an email that includes a hyperlink to a network drive location (that may have spaces). I have some code that sort of works but needs a bit of help...

  Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = managername & "," & vbNewLine & vbNewLine & _
              "Data has been dumped from the survey controller on project number " & newjob & ".  The name of the file is: " & vbNewLine & vbNewLine & _
              filefromname & vbNewLine & vbNewLine & _
              "and it is located in the following folder:" & vbNewLine & vbNewLine & _
            "<a href=""<file:///" & GetFolder & ">"">" & GetFolder & "</a>" & vbNewLine & vbNewLine & _
                "Comments:" & vbNewLine & surveynotes
    MsgBox strbody
    
    On Error Resume Next
    With OutMail
        .To = manageremail
        .Subject = "Some survey points have been downloaded on one of your projects"
        .Body = strbody
        'You can add a file like this
        '.Attachments.Add ("C:\test.txt")
        .Send   'or use .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing

Open in new window


The email sends no problem, and the extra chevrons in the string help me pick folders that have spaces in them.  But when the user gets the email, they see the hyperlink source code in the Outlook email like this: (PS - the link works no problem)

HTML is showing
instead of seeing it neatly like this:

What I want it to look like
If I grab the "strbody" variable and paste it into dreamweaver or some other editor, it looks fine, but when the Outlook email is received, the HTML code for the link shows up.  What can I do so that the user doesn't see all the <a href> text that goes with the hyperlink?


Thanks very much!
0
The code makes combinations from data in B10:AG19 in "sheet1" and puts them in sheets "NumbersX" where "X" is 1-10.

When the code is executed again it deletes data in sheets "NumbersX" then writes new data.

The goal here is to edit the code to:


1) Number the sheets using the number in B10:B19. In this instance "Numbers_3, Numbers_4, numbers_7, etc.

2)  write combinations using only the first number (located in B10:B19). After it is finished wit combinations that match the first number stop with that sheet then go to next sheet, etc.

FYI: I have 1) manually edited sheet names to match the desired result and the first sheet shows only combinations with the number 3 that matches the number in B10.

There is no data in the last sheet since there are no numbers in B19:AG19
CreateCominationsFromGoups_2-2020_F.xlsm
0
I need help with writing a formula on the sample file attached.

I am looking to find the following:
- First date with a value after there have been 6 blank cells in a row
- Last date with a value after there have been 6 blank cells in a row and no additional cells with any value afterwards

Sample is attached!  Thanks!
0
Hi

What Access VBA code would I use to automatically email all Access tables as Excel spreadsheets
via Outlook without the user being able to alter the data. So I want the emails to fire off straight away.

Thanks
0

Microsoft Excel

137K

Solutions

38K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.