Microsoft Excel Help

Question by

Ian Bell

1d agoSolved

Speed up Calcs using INDIRECT function.

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

27 Comments

Learn Ruby Fundamentals

LVL 12

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

LVL 1

Question by

Kevin

2d agoSolved

Automate Microsoft Excel WorkBook

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 …

8 Comments

Question by

SRMoxy

2d agoSolvedPrivate

VLOOKUP Won't Work

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.docx SAMPLE-Problem-Vlookup.xlsmThanks!

5 Comments

LVL 7

Question by

gudii9

2d ago

excel date time

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

2 Comments

Question by

Svgmassive

2d ago

Copy non contiguous range

I would like to copy the non contiguous date range to another column
Book2.xlsb

7 Comments

Question by

Svgmassive

2d ago

Transpose Data

I have imported data that i would like to arrange in the format attatched

Thanks
TRANFER.xlsb

2 Comments

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

…

6 Comments

LVL 1

Question by

Mik Mak

2d agoSolved

Strange Excel value transfer without cell referencing

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

9 Comments

LVL 1

Question by

LD16

2d agoPrivate

Excel VBA: create excel gantt chart related to multiple phases

Hello experts,
I have a list of task which are related to multiple phases

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.

9 Comments

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

1 Comment

Why Diversity in Tech Matters

LVL 12

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

2 Comments

LVL 3

Question by

Bill Herde

3d agoSolved

Excel link hell

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.

2 Comments

Question by

Ian Bell

3d agoSolved

Speed up Standard-Deviation formula

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

7 Comments

LVL 1

Question by

John Carney

3d agoSolved

Removing duplicate strings from a a cell

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

6 Comments

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

1 Comment

Question by

Nancy Therrien

3d agoSolved

For Next Loop, not working

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

…

8 Comments

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 Comments

LVL 1

Question by

JDCam

3d ago

Excel Conditional Running Total

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 ?

13 Comments

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

6 Comments

Exploring ASP.NET Core: Fundamentals

LVL 12

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

4 Comments

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

4 Comments

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

6 Comments

Question by

snhandle

4d ago

Excel formula no vba

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

12 Comments

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!

8 Comments

Question by

nav2567

4d agoPrivate

compare excel columns.

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

7 Comments

Microsoft Excel

Related Topics

Microsoft Excel

Related Topics