[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Excel

135K

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

Hi,
I would like help to amend a formula to speed up calculations with an INDIRECT function.
Please refer to attached spreadsheet.
Many Thanks
Ian
Indirect-Function.xlsx
0
Learn Ruby Fundamentals
LVL 12
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Good Afternoon,

I have a workbook in excel 2013 that I use to make a log of my duties each day.

At the moment to automate the form a bit, I am using some strings for some of the cells that I was able to figure out from online forums.

I would like the form to be a bit more automated and read online that macros can achieve this.

I have the workbook attached (OPLOG.xlsm) and would be grateful if someone can create a macro for me which will achieve what I want it to do.

Please note:

1.      The worksheet cannot be changed in any way and must remain the same with the layout, so adding more columns or moving things around is NOT an option.
2.      I would like all strings I have already in certain cells to be controlled by the macro (I will list what I want below). That way there will be no accidental deletion if I accidentally delete the code from the cell. So once the macro works there would be no need to have the strings in the cells anymore.
3.      I am not interested at this time of any suggestions or other applications that will accomplish this etc, I would just like to make this form work in the way I have listed below using a macro behind the scenes for it.
4.      All highlights, illustrations and red writing in the screenshots below are just there to give a clearer picture of what I want the macro to accomplish.

My requests for the Macro:

1.      Upon launch of workbook I would like the processing date to automatically set to today’s date, this …
0
MS Excel 2016 - My Vlookup wont work!  Please help -  simply trying to find a cost center on the tab and bring back the plant name.   I've done it on another workbook and it worked - but on this one it won't?  I've check formatting and tried xlsx & xlsm?    What am I doing wrong!?  :)     Sample-Problem---VLOOKUP.docxSAMPLE-Problem-Vlookup.xlsmThanks!
0
in excel how to represent below date and time stamp as it is like in database cell

2018-11-13 06:13:15.891


when i copy past it shows as 13 or something

how to import sql server results value including heading to excel without loosing date formatting etc
any tips or tutorial videoes around this?

Please advise
0
I would like to copy the non contiguous date range to another column
Book2.xlsb
0
I have imported data that i would like to arrange in the format attatched

Thanks
TRANFER.xlsb
0
I am not an SME in VBA. I understand the basics, the issue here is this code was written by a former employee.  It works in Windows 7 (Using Excel Object Library 14.0) and does not work on Windows 10.  I get the following error:

Run time error '9':
Subscript out of range

The error starts on line 154, but I have no idea why?  It is in bold within the code..

"wkb.Sheets(2).Range("A1:J1") = Array("Name", "Avaya Name", "Employment Type", "Tenure Type", "Inclusion Status", "Secret Code", "MRRs", "Combo CMRs", "Solo CMRs",
     " Avaya Total Hours")"
--------------------------------------------------------------------------------------------

Private Sub CommandButton5_Click()
Dim Wb As Workbook
Dim lastTeamRow, lastGreenReportRow, lastCanvasRow As Long
Dim GreenReportArray, GreenReportHistory As Variant
Dim c As Variant
Dim callArray(0 To 1) As Variant
Dim RphTenureTotal, RphNonTenureTotal As Double
Dim appExcel As Excel.Application
Dim wkb As Workbook
Dim index1, index2 As Integer
Dim FCMCallbackTotal As Long

CommandButton5.Enabled = False

Set Wb = ThisWorkbook
With Wb.Sheets("Teams")
    lastTeamRow = .Range("B" & .Rows.Count).End(xlUp).Row
End With

Team = LoadTeam(ReportType.GreenReportTeam)

' create array for Green report per employee
ReDim ReportArray(1 To lastTeamRow - 1, 1 To 8)

' create array for Green Report Array
ReDim GreenReportArray(1 To 1, 1 To 16)

' populate Green Report Array date
GreenReportArray(1, 1) = Format(Wb.Sheets("RxConsult 

Open in new window

0
We have a very weird problem with the attached Excel spreadsheet. Try and go to tab "Overtagne" in cell G72 and put in another number - ie 28000. This change will then be transferred to the other tabs - even though there's no references, and Theres no vba code in the file. If you after opening the file, starts with clicking on each of the tabs, and then returns to "Overtagne", and type in a number in cell G72 - nothing is transferred to the other tabs... - maybe a corrupt file, but very strange none the less ?
beregning-forbedringer-18131.xlsx
0
Hello experts,
I have a list of task which are related to multiple phases
gantt2.png
I am looking for an smart way to display the information through an excel gantt chart bar.
Could you please share some  excel  template or provide some advices concerning this need.
Thank you in advance for our help.
0
Hi,

Please help with a MAX (IF) condition formula without array if possible for Excel 2013.
Sample sheet attached.  I would like the result for each row as per column "L" in the attached sheet
Thanks
MAX-formula-sample.xlsx
0
Why Diversity in Tech Matters
LVL 12
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

I have some VBA code that opens up a url generated with variables which displays a table that can be copied and pasted into Excel. I need to do this for 22 airlines and 900 aircraft.  Everything has worked perfectly for the last year but the other day it broke down for a few of the airlines. The attached workbooks includes 2 of them for demonstration purposes.

As the workbook explains, if I hard code the url  for a specific aircraft (which I can't do for 900 aircraft), then it works. Hopefully, the problem is not a hidden issue in Chrome or flightaware for some airlines and there's a way to fix it within my code.

Note: you have to have Chrome installed on your computer for the code to work at all.

Thanks,
John
FlightawareGrab_EEQuestion.xlsm
0
I am having a problem with Microsoft Excel not opening links to a network location.
The cell contains the following.
=SUMIFS('\\servername\9 - FOLDER\P P G, Inc\Billing Invoices and Settlement\1809\[UUT PPG-201810.xlsx]UUT Details'!$J:$J,'\\servername\9 - FOLDER\P P G, Inc\Billing Invoices and Settlement\1809\[UUT PPG-201810.xlsx]UUT Details'!$A:$A,RIGHT(A47,2),'\\servername\9 - FOLDER\P P G, Inc\Billing Invoices and Settlement\1809\[UUT PPG-201810.xlsx]UUT Details'!$D:$D,RIGHT(H1,10))

And it will not work.
However if I open the file being referenced in the link in the same excel instance, the cell contents automatically change to

'=SUMIFS('[UUT PPG-201810.xlsx]UUT Details'!$J:$J,'[UUT PPG-201810.xlsx]UUT Details'!$A:$A,RIGHT(A47,2),'[UUT PPG-201810.xlsx]UUT Details'!$D:$D,RIGHT(H1,10))

and it starts working.

Any ideas what would keep it from working with the file still closed?  Path is verified correct.  Tried full quotes on pathname.
0
Hi,

I would like to have the attached set of formulas modified in order to
increase the speed of calculation and to avoid unexpected shut down of worksheets.
The worksheet has just in excess of 100,000 rows.
I've identified that Column E is the culprit.
The ultimate object of the set of formulas is to establish z scores as shown in yellow col F.
Please refer to attached sheet.
Many thanks
Ian
modify-stddev-formula.xlsx
0
Is it possible to determine if a given string occurs two or more times in a given cell, and if so then remove all occurrences of that string but one ? Ideally something as simple as an IF statement, like this:
Sub RemoveRNRDuplicates()
For Each cel Range([T6], [T10000].End(xlUp))
If "R/NR" occurs more than once then remove all other occurrences of "R/NR"
Next cel
End Sub

Open in new window

Thanks!
John
0
I have written a formula that displays if two conditions are met:

1) if column C = "FRA in date"
2) if column A and column B have dates that are NOT in the same month

Formula: =IF(C2="FRA in date",IF(MONTH(A2)<>MONTH(B2),"Yes","No"),"N/A")

I have then copied this formula all the way down the data set.

I have a countif formula on a separate tab that counts how many of the rows above are yes

Formula: =COUNTIF('Block Level'!D:D,"Yes")

I would like to just have one formula to provide me with the answer, i.e. not copying the first formula all the way down, just answer within the formula on the separate tab.


Thanks


Simon
Data.xlsx
0
Good Afternoon,
I'm trying to compare data from an Excel Sheets, I have to compare rows with each other.
My data is like :
1 aaaaaaa 19.5  15          louis
2 aaaaaaa  19.5               louis
2 aaaaaaa  19.5 15 a      louis
3 aaaaaaa  19.5               louis

I need to verify that all columns for each row are equal to the rows above.  Here is my code and it is not working.
Can you please help me.
If I don't take care of the 3rd columns it was doing good.

It always give me a NExt error or else without if

Thanks in advance.

dim i as Integer  ' row 6 to end
Dim j As Integer   ' columns F to U
   
   For i = lastrow To 6 Step -1
    
        If Cells(i, 2) = Cells(i - 1, 2) And Cells(i, 6) = Cells(i - 1, 6) Then
                 
		If Cells(i - 1, 23) = Cells(i, 23) Then
    
			For j = 6 To 22 Step 1
                    		If Cells(i, j) = Cells(i - 1, j) Then
  '  			Next j
                      
                      	Cells(i - 1, 1) = Cells(i - 1, 1) + Cells(i, 1)
                      	Rows(i).Delete Shift:=xlUp
            
                Else
                    
                End If
        Else                          ' put color  'met la ligne en couleur
                Rows(i).Select
                
        	        With Selection.Interior
                	 .Pattern = xlSolid
                    	.PatternColorIndex = xlAutomatic
                    	.Color = 65535
                    	.TintAndShade = 0
                    	

Open in new window

0
I'm using Goggle Maps API I learned from here.
https://chandoo.org/wp/distance-between-places-excel-maps-api/

This works fine when the UK Post Code is complete (eg. SE9 6AU). I find that sometimes partial Post Code works. (eg. SE9) but sometimes they don't.

Here's my problem. I got some Post Codes that are only partial - ie. only have the first part of the UK Post Code. How can I get Google to return the result assuming a default location that is at the centre of that Post Code?

Thanks
0
need a hand with an excel formula.
Here is very simplified view of my columns

Ord#	Line#	Backorder
100	1	0
100	2	1
100	3	0
100	4	1
101	1	0
102	1	1
103	1	0
103	2	1
104	1	0
104	2	0
105	1	1
105	2	0
105	3	1

Open in new window


the third column acts like a Boolean. 1= backorder, 0 = not backorder
My goal is to report, how many orders are affected by back orders?

in this example 3 orders contain backorders

How can I accomplish this total in Excel ?
0
Hi Experts

Hope you can help

Ive noticed some threads on here regarding this however none of them appear to have the specific criteria Im trying to reach as a result

basicaly, we ahve supplier packing lists that should contain 3 sizes of approved packaging sizes. By pasting in the detail, Column K performs a calculation (kindly provided previously by an expert on here) and when I performa  lookup to the actual sizes they should retrieve the result of YES or NO

Ive dicsovered on some supplier entries they have either used the exact dimensions or near to the sizes that obviously results in slightly different calculations

Ive tried to use the TRUE function to buld in a tolerance but is there a way via formula to return a YES if the dimensions caluculation is below or above 10%?

If tries combinations of using ABS with <=10 and using helper columns but pnly return #VALUE results

I have attached the file - The formula Im looking for is currently in Column L - any help would be greatly appreciated

J
SumDimensions.xlsm
0
Exploring ASP.NET Core: Fundamentals
LVL 12
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

I have an Excel sheet with many Ledger Numbers in Col B and many Accountant Codes in Col A

All the Ledger Numbers are in the  group of the Accountant Code at the top of the group (Col A)

Instead of the Ledger Codes going down the rows I want them going across the columns.

 I want the ledger numbers in Col B appearing alongside the Accountant Code in Col E wherever a new Accountant Code starts
Rows 11, 15 and 20 show the ideal layout.

How can I achieve this without copy / paste transpose repeatedly.
Thanks
Sideways-paste-ledgers.xlsx
0
When I use the below used range it selects over 1,000,000 rows ?  Why as I only have 5 rows of data...I even manually deleted row6+ and all Columns beyond COl D and still no luck..

Worksheets("Sheet1").Activate 
ActiveSheet.UsedRange.Select

Open in new window

0
I use the below array to copy multiple sheets into a new workbook but how can I copy multiple sheets into a new workbook for the used range in each worksheet

 
get name to save new workbook as. change reference as needed
    strSaveName = Worksheets("Sheet2").Range("a1").Value

    ' copy sheets to new workbook
    Sheets(Array("Sheet1", "Sheet3")).Copy
    ActiveWorkbook.SaveAs strSaveName

Open in new window

0
I have two columns like below

Column c      Column d
2.                      -
12.                     -
15.                    -
18.                    
25.                  

I want to create formula where when I have - in the last cell in column d then it should give me the column c data like in above example I want to get 15 number result of my formula
0
I am looking for a formula that will allow me to lookup multiple criteria and pull in multiple matches.  For example: My column headers are years, i.e. 2007, 2008, etc.  My row headers are the different project types, i.e. New, Old, In Queue, etc.  I also have 2 static combo boxes that correspond to City and State, which should also be part of the criteria and as the different city and state values from the drop-down change, the matches should changes as well.  I know how to use MATCH and INDEX for multiple criteria to bring back a single match, but what if I need to match multiple criteria to pull in multiple matches for that same criteria and pull the matches in on separate rows if there is more than one match?    I understand that I can use SMALL, but I've been playing around and have not been successful.  I would appreciate any assistance with the array formulas that you can provide.  Thanks!
0
Hello,

I have two columns of computer names - please see attached.  

I want to list computers which are in column 1 but not in column 2 and put the result in column 3

Please advise.  

Thanks.
test.xlsx
0

Microsoft Excel

135K

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.