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

I am linking a cell (Containing a Date) from one sheet to another and the destination cell shows the default date of 0/01/1900 but there is nothing in the original cell.

I am using the simple formula of

In the Destination cell type =

and then click on the worksheet and cell containing the Date/Data that I want to copy and hit enter.

It sometimes will show 0 in the destination cell but mostly it will show the default date.

Any advice would be greatly appreciated.

Thanks DotDefault Date Showing
0
I keep getting a number instead of date within my Excel formula. How can I format the field within my formula to be the same date format as the data in the TAG_DATE column? Please see the attached screenshot.
Capture.PNG
0
Hello,

 I would like to have values concatenated for certain columns based on a column's value . Like group on a column and append the other column values if they are non-repeating , using a macro.

I have the files ,one for desired output  and other code.

Please  help. I just could manage to concatenate a columns non-repeating values and write to a cell in  a new column before merging.

Thanks,
RAm
sampleexcel.xlsx
simplemacro.txt
0
Schedules_test.xlsxI am trying to update a schedule tracking workbook to show when an agent is scheduled based on If the status column(N) =1(Working), 2(Leadership),3(PTO),0(out). The start/stop times are in column C/J. If the N column = 1, I also need to show any breaks, lunch, or meetings scheduled for this agent. These start/stop times are also on the sheet.

If column N = 1 I want to show these symbols for each status:

Break = "B"
Lunch = "L"
Meeting = "M"
Scheduled to work = "-"

If column N = 2 show their scheduled work time as "A"
If column N = 3 show their scheduled work time as "V"
if column N = 0 show their scheduled work time as "Out"
If column N = Blank or not 0-3, Leave blank

I have tried multiple versions of excel formulas to accomplish this. If you look at any tab The first row has the latest version I have tried. The issue now is the lunch/meetings/end times are not showing correctly.

I am fine with using an excel formula or VBA that updates whenever a change is made in columns B through N.

I have attached the workbook. Any help will be greatly appreciated.
0
Hi Experts

I hope you can shed some light on something I cant seem to grasp when creating / updating a pivot table

I have created a pivot table from some courtier data we have which basically highlights the number of shipments per state in the US. Our courier, however, lists a tracking number per carton on the shipment so if there are more than on carton making up a shipment, the tracking number is duplicated

I have managed to count the unique values by selecting the necessary options and filter however when I try to refresh the table after this, it remains the same ?

Do I have to remove the table Ive created and recreate the new on with the new settings or is there a way of updating the existing table with the new criteria?

Any suggestions would be welcome

J
0
How do I add a New column in power query with "group by" when I group by columns I am unable to see the rest of the columns
0
ErrorHi All, I work in support and a few of my users are having an issue in excel.

When they open an excel 2016 file with what looks like to be a VBA sheet, once they click on a button an error pops up with

Microsoft Form
could not load some objects because they are not available on this machine.

what i know is that it works for some users and not for others.

we are on windows 10 64bit
office 2016 32bit
0
Hi all,

I have an excel document that it works for me as I need. I sent the document to my colleague and now I cannot see the calculation result because.On the cell where the figures should shows it come up with " #Value! "

Is this something to do with permissions?
0
I have a pivot table that is connected to an Access query.  I have added a field to the query and would want to add the field to the pivot table.
How can you modify the pivot table’s link to external data?  On older version of Excel, you pressed Alt-D, then P to modify settings; however, this no longer works.
I am currently using Office 2016.  I have tried to select Connection Properties from the Data tab; however, the command text only shows the name of the Access query.  The Edit Query button is grayed out.
Thank you in advance for your help.
0
On click I am copying and pasting data to different pages.  On one particular page ("OrdPrep") I am capturing the OrderNumber and the Order Description. No problem.

But on the OrderPrep page, I want to paste across columns. for example:
               On first Click                            On Second Click                   On Third Click
           Col1                    Col2                 Col3                 Col4                   Col5                 Col6      
       
1.         OrdNum          Order              OrdNum         Order               OrdNum           Order
2.         MJB0001            Stuff             MJB0002        Something        MJB0003           Nothing
3.                                    Stuff1                                                                                           NADA
4.                                    Stuff2                                                                                            Nope
The data comes from sheet1 and is selected by user.. I have tried several different methods but cant get what I want..
I tried to format this message by looking at the preview. If this does not make sense, let me know and I will forward worksheets.
Thank You,
Norm
OSsendE-X.xlsm
0
I exported data from a Shopify store to a CSV file. The original data are product descriptions from the store inventory.

When you scroll down the worksheet to ROW 10313, some of the product description and other columns are no longer populated. COL A displays the following type of formatting (XML ?) rules. Those rules continue to be displayed to the last row of about 32000. Some of the rows contain the correct product data:


<m:brkBin m:val=""before""/>
...
<m:mathFont m:val=""Cambria Math""/>
...
UnhideWhenUsed=""false"" Name=""Colorful Grid Accent 6""/>
...
<w:LsdException Locked=""false"" Priority=""73"" SemiHidden=""false""

QUESTION:
Can you explain what is happening and how to fix it?
0
Where could I get an Excel or Access version of the dummy data set - Worldwide Importers? I know I can get it via SQL but I want to get the Excel or Access version?  Here is the SQL link..https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0
0
My Excel does not recognize LongLong data type needed to run the ActiveX version of Interactive Brokers Trader Workstation Excel dde spreadsheet. It appears that I need to be running a 64 bit version of Microsoft Office. What is the best way to do this and could this cause any problems with existing Office applications that I run.  Thank yo very much!
0
Hi,

How to get this Pivot output or formula driven output based on data.
File is attached.Get-Output.xlsx
0
Hi,

I want to find highest values' Row and Column in an array in Excel.
The file is attached for the reference.Get-Highest-Value-in-Array.xlsx

Thanks,

San.
0
I am using the code below on a userform in Excel to run a report stored in access database. It currently prints the report immediately. I am trying to have the report just display without printing. I would prefer that the report opens in excel and the user can then decide to print or discard.

Right now if the user runs the report it locks the access dba until the print job is complete. I need to prevent that from happening since we have multiple writing to the database from the excel userform. Any suggestions or perhaps a better method?


Private Sub CommandButton2_Click()
       Dim objAcc As Object
       Dim strWhere As String
       strWhere = "IDnNo = " & Me.txtBrNo & ""
       Set objAcc = CreateObject("Access.Application")
       objAcc.OpenCurrentDatabase FilePath:=TARGET_DB
       objAcc.DoCmd.OpenReport "rptClasss", acViewPreview, strWhere
       On Error GoTo ErrHandler
       objAcc.RunCommand 340 
       On Error GoTo 0
       objAcc.Quit
       Exit Sub
ErrHandler:
       If Err = 2501 Then 
           ' Ignore
       Else
           MsgBox Err.Description, vbExclamation
       End If
       Resume Next

End Sub

Open in new window

0
Hi Experts

I have a daily report that shows what our warehouse team has put into stock for the previous day - on Sheet 1 is :-

Column A has the date of the transactions
Column B as the hourly bracket
Column C has the actual posting time of the transaction
Column D has the location with which they have mad the transaction
Column E has the quantity that was posted to inventory

On Sheet 2 I have put some labels in the cells such as :
Quantity replenished before 2pm
Quantity replenished after 2pm
Number of locations replenished before 2pm
Number of locations replenished after 2pm

For the above on Sheet 2 Ive tried using the data on Sheet 1 and various Count If / Sum if combinations to get the results for the four summaries above on Sheet 2, however Im having difficulty in using the time as part of the formula

I've also tried to use SUMPRODUCT / FRREQUENCY as Column D has duplicates that need to be counted as 1 fulfilled location and not counted per entry

Is there a way using the time in Column C on Sheet 1 to a) sum the quantities put away before and after 2pm and b) count the number of locations that were used before and after 2pm

Any assistance would be greatly appreciated

J
Pickface-Replenishment--3-.xlsx
0
Hello Team

I have a worksheet named "Summary" which contains name ranges, 15 in total.

Each name range follows a name sequence of CCSum1, CCSum2, CCSum3 etc.  

Can you please advise, using VBA, how to best loop through each named range, e.g., get the code to identify CCSum1, run the code required to loop through each cell in the CCSum1 range  and then move onto CCSum2 etc.  The code for looping through each cell within the named range isn't required, just how to move from one name range to another.

Thanks in advance for any suggestions provided.

Sincerely

Dale
0
Hi,

I have below VBA code that massively converts csv to xls.

My problem that i have is that in my csv files, i have many values that as leading zero's.

Ex:0000000039,0000000039 WORLD ON EDGE

So to be able to keep the leading zero's, i must set the columns to NumberFormat = "@"

The problem i'm facing is that it does put the columns in TEXT format but the leadings zero's are gone.
Instead of seeing : 0000000039, i 'm getting 39.

I've tried: Columns("A:Z").NumberFormat = "@", Range("A:Z").NumberFormat = "@".


The file i have is only 1 macro. The macro ask to select a folder that contains csv file(s).

How can i make all the data in TEXT format so i can keep the leading zero's

Thanks for your help.


Sub CSVtoXLS()
    
    Dim xFd As FileDialog
    Dim xSPath As String
    Dim xCSVFile As String
    Dim xWsheet As String
    Application.DisplayAlerts = False
    Application.StatusBar = True
    xWsheet = ActiveWorkbook.Name
    Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
    xFd.Title = "Select a folder:"
    If xFd.Show = -1 Then
        xSPath = xFd.SelectedItems(1)
    Else
        Exit Sub
    End If
    If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\"
    xCSVFile = Dir(xSPath & "*.csv")
    Do While xCSVFile <> ""
        
        Application.StatusBar = "Converting: " & xCSVFile
        
        Workbooks.Open Filename:=xSPath & xCSVFile
        
        Columns("A:Z").NumberFormat = "@"
        
        

Open in new window

0
1.  I have a macro that I would like to run on multiple files in a folder.  Each file may have between 1 to 400 records in it.  2.  I will need the macro to be able to copy and paste values in Columns G, H, and I (values after macro runs) to the end of the file if the file no matter how many rows it has Again some files only have 1 record, some 10 records, etc.  See macro in the attached file.
Currently the macro removed 2 columns; adds 3 columns, do calculations in the 3 columns (G-I) and remove all columns after column i.
35.561.43.15.xlsx
0
I have a large number of excel files I need to open and run a macro to count columns and count non-blank and blank values on a row.  The first 8 columns are the same and then after ~ the columns per file various.  It could be 1 column to 45 columns.  Also a file could have only 1 record or it could have over 300 records.  I am trying to figure out a way to open all of the files at once apply the macros to remove a few columns and add 3 columns to hold the "total count", "blanks", and "completed" values, along with having  the output from each file combined into one larger file.  Any help would be greatly appreciated.
0
I have a table that is generated from a data source.  The table is based on a 6-month time frame.  My challenge is how to populate the table if I do not have all 6-months of available.  So if I have 3-months of data, then they those 3-months would be displayed reading left-to-right, with the oldest in the left most column.

I have played with nested IF statements, but with no success.

Attached is a small example of my data with a table showing 4 of the 6 months and one with all 6-months. Table_With_Moving_Data_EE.xlsx
Table_With_Moving_Data_EE.xlsx
0
Hello experts,

I have the following xlsx table attached.

I don't understand why the banded format is not properly propagated for column D.

Required option is already on.

20191017_155219-screenshot.png
Thank you for your help.
dummy-sheet-table-format-problem_20.xlsx
0
I have a report in excel where the total time is written like so:

100d2h54min29s

I was wondering / hoping someone had a formala to confert that data into a straight number of seconds.

IE:

8650469

Thank you,
0
Excel calendar from "MicrosoftMonthView" creating double calendar.
I HAVE ALSO TRIED MICROSOFT DATE AND TIME PICKER, IT DOES THE SAME THING, CREATES A DOUBLE.
I want only one single calendar, not two.
If you maximize then restore the second calendar window disappears, but I don't want to have the use do that everytime when they open.
Book1.xlsm
0