Free Microsoft Excel 2013 Quick Reference

- If statement Checking previous table array
- Calculate Retention By Dividing Count By Multiple Criteria
- Search For Files Containing Specific Text In Cell
- Custom Formats
- Sum Total Of Calculated Field In PivotTable
- Overwriting My Data
- VBA Code for sorting and filling in information from data table
- Formula to put certain information cells into table
- Printing - Have to set print area 1 column further than necessary
- Ever wished there was an 'Applications' Collection ?!!!
- I NEED HELP w/ Condidtional Formatting a range in a formula!
- HELP!! timing based data aquisition from the same cell !!!!
- Should be a simple formula.....Help!
- Skip columns when importing *.csv
- Problems with csv files!!!
- Formula Please!
- List all variations in a column
- How to report values in a column based on content in a different column
- Pivot Charts - Reapplying formats
- Move column headers to multiple rows

I am trying to make this formula works so that it performs on th e previous tworksheet a similar check to the one it is currently performing on the same worksheet.

I currently use this code.

VB:This code works, it performs the check on the same table.Lr = Range("A" & Rows.Count).End(xlUp).Row If ActiveCell.Value = "" Then Selection.FormulaArray = _ "=IF(RC[-9]=""DEBIT"",IF(MAX(IF(R2C3:R" & Lr & "C3 =RC[-14],IF(LEFT(R2C8:R" & Lr & "C8,3)=""INT"",IF(R2C13:R" & Lr & "C13=RC[-4], IF(R2C8:R" & Lr & "C8"""",ROW(R2C13:R" & Lr & "C13),0),0),0),0))=0,"""",""COR""),"""")" End IfIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Now I am trying to make it work so that it checks for the array in the previous table.

I tried:

VB:But it doesnt work.Lr = Range("A" & Rows.Count).End(xlUp).Row Set ws1 = Worksheets(Worksheets.Count) Worksheets(Worksheets.Count - 2).Activate ws2 = ActiveSheet.Name Lr2 = Range("A" & Rows.Count).End(xlUp).Row ws1.Select If ActiveCell.Value = "" Then Selection.FormulaArray = _ "=IF(RC[-9]=""DEBIT"",IF(MAX(IF('" & ws2 & "'!R2C3:R" & Lr2 & "C3 =RC[-14],IF(LEFT('" & ws2 & "'!R2C8:R" & Lr2 & "C8,8)=""INT"",IF('" & ws2 & "'!R2C13:R" & Lr2 & "C13=RC[-4], IF('" & ws2 & "'!R2C8:R" & Lr & "C8"""",ROW('" & ws2 & "'!R2C13:R" & Lr & "C13),0),0),0),0))=0,"""",""COR""),"""")" End IfIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

It shows the following msg "Unable to set the fomula array property..."

I have also tried to do on excel the formula, but once i select the range in the previous sheet, the formula dont work.

Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â =IF(H14="DEBIT",IF(MAX(IF('Transactions 30-04'!C:C=C14,IF(LEFT('Transactions 30-04'!$H$2:$H$154,8)="INT",IF('Transactions 30-04'!$M$2:$M$154=M14,IF('Transactions 30-04'!$H$2:$H$154"",ROW('Transactions 30-04'!$M$2:$M$154),0),0),0),0))=0,"","COR"),"")Â

What am I doing wrong here?

Thanks for the help!

I come again to this excellent forum to ask a question, this time on pivot tables.

How can I make a calculated field that can refer to a previous value in the table? That is to say, if you have values grouped by e.g. weeks of the year as columns, then in the column for week 2, I want to be able to include in a calculated field a derivation of the value from week 1.

Perhaps the attached file will make more sense!

I have several branch locations - N, S, E and W. They all run events every week (col b) that have an "existing" amount of business and a "new" amount.

I want to pivot to track this over time as per the attached. You'll see I have a successful basic pivot showing New and Existing per week.

What I need to do is add a column next to the New and Existing - presumably a calculated field - where the sum for week "n" is

=(week N existing - week N new)/(week n - 1 existing)

I saw an article at http://blogs.msdn.com/excel/archive/...ted-items.aspx which gave me an idea that

"It turns out you can use a special syntax to refer to "next" and "previous" items in a field.

In this example, the PivotTable author had actual financial results for FY02 – FY04, and wanted to create a forecast for the next fiscal year based on the actual for those three years. You can see in the screenshot above that they used the following formula: =IF(Year[-1]=0,Year[-2],2*Year[-1]-Year[-2])."

But it looks like from experimenting that that syntax only works with calculated *items*, not calculated fields that I need.

I tried various formula including the [-1] syntax, and playing with using a dummy field set as a custom calculation type "difference from" but got nowhere. On the latter, it seems even though I can get it to display the differences, trying to calculate on that includes the underlying whole number, not the displayed difference. Lookup functions like GETPIVOTDATA are not apparently allowed within a pivot table itself.

The dataset is going to be pretty huge and changing regularly, so wanted to avoid array formulae and manual paste-values etc. would be a pain. I know how to do it via array formulae, but it is hugely slow and inflexible.

Does anyone know if it's possible? Huge gratitude if so! Am using Excel 2003.

Thanks, do let me know if the question isn't clear!

Adam

For example, if I create a new file, and in the first cell type in "123456".

Then save the file to the folder c:Temp

Then in Windows Explorer, right-click on C:Temp and select "Search . . ."

Then under "A word or phrase in the file", type in: 123456

It will NOT find the file ! ! It will find a txt file or a doc file, but not xls files.

I thought maybe it fails because that is a number.

But I have hundreds of Excel files with Integer numbers stored in "General" format cells and I desperately need to be able to search them to see which files contain certain numbers.

It all started with some files that were sent to me - they work PERFECTLY for searching cells for numbers. So strange - yet if I open the file then save it . . . no changes or edits at all, just Open and Save . . . then the search fails for all numbers in that file.

This is so perplexing that I have placed 2 zip Sample files on my website for you to Download

http://www.infocellar.com/Samples.zip

Unzip the two files to any folder.

One is named "Sample-Works.xls" - Then do a Windows Search for any number in the file, here is one to try that is in the two files: 174026

And it will list "Sample-Works.xls" but it will NOT list "Sample-Fails.xls", even though BOTH FILES ARE IDENTICAL !! I copied the same file twice and then named them. The only difference is that I opened the file Sample-Fails.xls and then immediately Saved it, which as I stated earlier, makes it fail all Searches from that point forward.

Also please try this - using Windows Explorer, copy the good file and then redo the Search. You will see both the good files show up as having "174026". Then open the copy you made and save it - now that file will fail to show up in the Search ! !

I did try this on my other PC, by the way, and the same Problem exists.

I also tried numerous Search Utilities (Agent Ransack, File Boss, PowerDesk File Finder, Turbo Search, etc) and they all have the SAME problem. There is simply no way to Search an Excel file for a number unless it is one of those files that were emailed to me a while back.

Any ideas? Anyone know how I can search Excel files and find the files that contain specific Integers ? TIA

ls

A cells format is made up from 4 different part known as "Sections" They are, from left to right

Format for positive numbersFormat for negative numbersFormat for zero valuesFormat for text

The semi-colon ; is what is used to seperate these Sections. For example a Custom Format of:

General;-General;General;

Would stop any text that is entered in that cell from being displayed. We could replace General with any format, e.g 0.00etc

In this case it is the use of the lastSemi-Colon (in the Text Section) that is preventing text from being seen.

If we used the format:

;;;

Nothing would show Except error values like #VALUE!, #N/A etc. We need to know about these

Let's suppose we have a cell that we only want to show when/if the number is positive, all negative numbers should not be seen. The positive numbers should also only show 2 decimal places. We could use

0.00;;0.00

If it were zeros we want hidden we could use:

0.00;-0.00;

One very good way to understand how Excels formats work is to format any cell as Curreny with negative being red and preceded with a negative sign and 2 decimal places (optional). Now select Custom and you should see

$#,##0.00;[Red]-$#,##0.00

As you can see, Excel is being told to make all negatives red ([Red]) and preceded with the negative sign. We can easly change this, so that, it stays red but uses parenthesis rather than the negative sign, i.e

$#,##0.00;[Red](-$#,##0.00)

As you can see, that while Custom formats can be confusing, they become far less confusing once we have grasped the concept of Sections

i want to show only what account makes a positive sales for a specific product and i want to get the total number of accounts who have the positive sales. there are multiple entries in my database, the condition is if an account have positive sales of a specific product regardless on how many times the account bought the result should be 1 (meaning one buying account)

i've tried to use a calculated field in my pivot table, i get the correct result per account but when it comes to grand total its not getting the sum of the total number of accounts

here is the sample of what i need to get..

you can see that there are multiple entries but it shows only 1 per account when it has at least 1 positive sales of a specific product and 0 if it hasn't any sales/negative... and for the total it adds the number of accounts to get the total number of buying accounts..

If you see the attached sheet, the first worksheet shows data in its original form. When the user presses the button (which calls the code below) a new table is generated on Sheet 2 which should present the data in a different table.. It is supposed to put all classroom bookings in my chart. However because some of the dates are the same (even though for different classroms) it is overwriting and therefore only some of the bookings appear.....does that make sense????

Here is the code:

VB:Does anyone know what the problem is? I am a complete novice to VBA!!!!ConvertData() Dim c As Range, Srng As Range Dim i As Integer, startcol As Integer, endcol As Integer Dim startdate As Date, enddate As Date Dim rowno As Long Dim ws As Worksheet Dim legend As Range, f As Variant Set legend = Range("Legend") startdate = Application.Min(Range("B3", Range("B" & Rows.Count).End(xlUp))) ' this searches for the smallest start date enddate = Application.Max(Range("C3", Range("C" & Rows.Count).End(xlUp))) ' this searches for the largest end date date Set ws = Worksheets("AvailabilityChart") 'choose Sheet you want the table Set Srng = ws.Range("A3") 'choose where you want the table here Srng.Offset(, 1).Value = Format(startdate, "D-MMM") 'this writes the smallest start date 'then it goes through the following loop to write all dates up to the largest end date Do i = i + 1 Srng.Offset(, i + 1).Value = Format(startdate + i, "DD-MMM") Loop Until Srng.Offset(, i + 1).Value = enddate 'writes the room numbers down the side for existing bookings Range("D3", Range("D" & Rows.Count).End(xlUp)).AdvancedFilter _ Action:=xlFilterCopy, CopyToRange:=Srng.Offset(1), Unique:=True 'this loop goes through and enters all bookings in the chart For Each c In Range("E3", Range("E" & Rows.Count).End(xlUp)) rowno = Application.WorksheetFunction.Match(c.Offset(, -1).Value, ws.Range(Srng.Offset(1) _ , ws.Cells(Rows.Count, Srng.Column).End(xlUp))) + Srng.Row startcol = (c.Offset(, -3).Value - startdate) + Srng.Column + 1 endcol = (c.Offset(, -2).Value - startdate) + Srng.Column '+ 1 'Copy name and then color as appropriate With ws.Cells(rowno, startcol).Resize(1, (endcol - startcol) + 1) .Value = c.Value f = Application.Match(c.Offset(, 3), legend, 0) If IsNumeric(f) Then .Interior.ColorIndex = legend.Cells(f).Interior.ColorIndex End If End With Next c 'puts borders all around the chart and in between the rows With Srng.CurrentRegion .Borders(xlEdgeTop).LineStyle = xlContinuous .Borders(xlEdgeTop).Weight = xlMedium .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeLeft).Weight = xlMedium .Borders(xlEdgeBottom).LineStyle = xlContinuous .Borders(xlEdgeBottom).Weight = xlMedium .Borders(xlEdgeRight).LineStyle = xlContinuous .Borders(xlEdgeRight).Weight = xlMedium .Borders(xlInsideVertical).LineStyle = xlContinuous .Borders(xlInsideHorizontal).LineStyle = xlContinuous End With 'formats of the table Set Srng = ws.Range("A2:IV2") Srng.Font.Bold = True Set Srng = ws.Range("A1:A100") Srng.Font.Bold = True End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I work at a hotel, and every day I have to hand-write coupons for breakfast for each of our guests who arrive for certain local accounts that we have, and I need a separate coupon for each day they are staying. As you can imagine, this is very time consuming.

I had a similar situation a few months ago with guests who arrived and were part of our frequent stay program, and someone was kind enough to help me by writing a VBA code to run a macro to solve that problem. Unfortunately that macro will not work in this situation, as the report that I run from my system for this is not in the same format.

Since the people who will be using this spreadsheet and macro are my employees and may not know excel as well as I do, I need to do this in as few steps as possible. They are already familiar with importing data from another excel file and using Ctrl Q to run the macro. My coupon workbook is saved as password protected, with a prompt to open as read-only so that it can’t be messed up by someone by accident. (Password for attached file is password, no caps)

Currently we run a report from our reservation system, export it to excel, and save it to the desktop. Then we open the workbook that has the breakfast coupons in it. On the first tab they select import data and then select the saved excel file to import from. Then they go to the second tab in the coupons workbook and run the macro by using Ctrl Q. This creates the right number of coupons for each guest based on the number of days that they are staying with us.

In this first instance the report that I run has already sorted the guests, so it only includes the guests who I need coupons for. For this new set of coupons I need to not only account for the number of days a guest is staying, but for the coupons to be run only for certain guests based on the “code” that their reservation was booked under, as the report I can run from my system for this will not sort them first.

The report that I run from my system shows all arrivals for a given day or set of days. Each arrival will have a special code which shows what account they booked their reservation with.

I need a macro that will create coupons only for people fitting a certain set of codes. Also, I will need to be able to modify the list of included codes later in case we add any new codes or lose any current ones.

I have attached a copy of the files and reports that I am currently using.

The first file is the coupon workbook that I use. (Password is password, no caps) - Sorry I could not include this. Try as I might, I could not get it to fit the file size, not sure why. I will be happy to email this to someone who is interested!

The second file shows the report that we use for our frequent stay guests and import into the coupon workbook to make the coupons for them. (I show this just so you can see what already works) - Sorry, There is a maximum of 2 attachments per post. Again, I will be happy to email!

The third file is the report which I have which shows arrivals based on their booking “code”. This is listed in the far right column (the C-, L-, or S- before the 3 letter code can be ignored).

The fourth file is a list which shows what special rate codes include breakfast (all those which say BF). Guests who are listed in the third file, which shows arrivals based on their booking code, would need to be sorted using this information, to have coupons made for them.

Please note: All names have been changed and company names removed from these files for privacy purposes.

I know this is rather complicated, but I have attached all of the current files I have in the hopes that it will make things easier if someone chooses to help me.

Thank you in advance to anyone who has the chance to assist me with this!

I'll try to be a bit more specific, I have 350 rows of barecodes. Col A counts the barecodes Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â countif($B1:B$1,B1)Â . Col B is the barcode ex: 06950290B0524123840022747. Col C shows me the code Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â if(B1="",0,MID(B1,5,5)*1)Â ex: 50290. Col D gives me the date Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â if(B1="",0,MID(B1,12,5)*1)Â ex: 52412. Col E gives me code and date together Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â (C1&D1)*1Â ex: 50290052412. Col F counts all of the different codes/dates Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â countif(E:E,E1)Â ex:116 (in this case). Col G counts each individual barecode incase there are duplicates Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â countif(B:B,B1)Â . Col H gives me the item # Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â if(B1="",0,RIGHT(B1,5)*1)Â ex: 22747. Col I shows if an item is missing Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â if(H1=H2-1,"OK","ERROR")Â . Col J shows the entire date Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â MID(B2,11,6)Â ex: 052412. Col K, Col L and Col M are for checking between different codes/dates aka batches Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â if(J1=J2,0,1)Â for Col K, and for Col L Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â if(C1=C2,0,1)Â . Col M Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â SUM(K1:L1)

Â .

Col N is the column I am referring to, it sorts out the different batches by code/date based on all the barecodes that match the criteria Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â if(M1 0,MID(B1,5,12),0)Â . Col O would show the amount of each batch Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Â Z100Â if(M1 0,F1,"")Â so 116. All formulas are extended 350 rows.

Here is where my problem lies, I am trying to find a way to read only the batches from Col N and the amounts from Col O and put them in a table without having to make the table 350 rows big. My table has to be small and only show the number of batches that from Col N and O that meet the criteria. As of now out of 350 rows, there are batches and amounts in Col N and O in rows 116, 195, 210,225,226,254,270,334,349 and 350, the rest have zeros based on the formula, however these rows will not always be the ones that meet the criteria So I want a table to show those 10 batches and 10 amounts in 10 rows with a column for batches and another column for amounts one after another; So the first batch will show up in row 1 on the table then the second batch will show up in row 2 and so on, is that possible, are there some formulas that will allow me to do this? The table doesn't have to be on the same page but I already know how to reference another sheet, I just don't want the table to have to be 350 rows to show the batches where the criteria is met.

Thank you

Minolta QMS 3300. when I selected an HP printer as the default printer the

problem was resolved. Strange but true!!!

Stuart

"keepITcool" > wrote in message

ft.com...

>

> I've seen this before.. I think the problem is connected to

> the Scaling and hidden columns..

>

> Try to circumvent by changing the column width of B and G

> from hidden (=0) to 0.1

>

> --

> keepITcool

> | www.XLsupport.com | keepITcool chello nl | amsterdam

>

>

>

>> Hope you folks can help me out with a strange one.

>>

>> I have several worksheets formatted in exactly the same way as

>> follows:

>>

>>

>> Col A - width 4

>> Col B - hidden

>> Col C - width 4

>> Col D - Width 108

>> Col E - Width 3

>> Col F - Width 11

>> Col G - Hidden

>> Col H - Width 11 & Empty

>>

>> My print range should be Cols A:G (I have used page setup to set the

>> scaling to fit 1 page wide by [blank] pages tall, thus each sheet will

>> print as many pages as required depending on number of rows]

>>

>> When I have the print range set to A:G only columns A:E show on the

>> print preview (and also on the actual print out) and when I make print

>> area A:F only A:C show on the print preview.

>>

>> To get A:G printed I have to set the print area to A:H.

>>

>> In all 3 of the examples above when I look at the print preview there

>> is a blank area on the right hand side of the page where the missing

>> columns SHOULD be but aren't... ie the size of the page seems to be

>> formatted correctly to include all the columns i want but they just

>> don't print unless 1 extra column is selected in the print area.

>>

>> Has anyone out there experienced this problem before?

>> Thanks in advance.

>>

>> Stuart

This subject was discussed here http://www.mrexcel.com/board2/viewto...=communicating but was abandoned.

Also, let me just say that the method I've attempted here to do this is by no means an elegant robust solution.It's actually just a hackworkaround which also requires quite a bit of setting up and can also be prone to errors if not handled carefully.

For a robust solution, the only way I know of is by registering and using a third party type lib : OLELIB.TLB written in C with which one can get a pointer to every object in the ROT but then again, what we want here is a VBA-Only solution !!

Set Up required - Follow these 2 STEPS :

STEP1- Create a workbook , place the following code in the 'ThisWorkBook' Module , save it as an AddIn and name it : AppsCollection.xla

AddIn Code

Code:

Option Explicit Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _ (ByVal lpClassName As String, ByVal lpWindowName As String) As Long Private bSkipCloseEvent As Boolean Private Sub Workbook_Open() Dim oWB As Workbook Dim lHwnd As Long Dim vAddInLocations As Variant Dim sMaxInstances As String 'check if there is a max allowed # of xl instances 'in the registry sMaxInstances = GetSetting("XLInstances", "MaxAllowed", "Max=") 'if there is,check how many xl instances are already open If Val(sMaxInstances) 0 Then vAddInLocations = GetAllSettings("MyRunningApps", "AddInLocations") 'if this xl instance is going to exceed the allowed # 'then set a flag,inform the user and close it If UBound(vAddInLocations, 1) >= Val(sMaxInstances) - 1 Then GoTo exitHere End If End If 'however,if it's ok to open this instance then see if it is the first one. 'if it is not,save this addin temporarly under a unique name 'this will permit to retrieve a pointer to the parent application ! lHwnd = FindWindow("XLMAIN", Application.Caption) If Me.ReadOnly Then Me.SaveAs Environ("temp") & Application.PathSeparator & _ CStr(lHwnd) & "_" & Me.Name End If 'always make an entry in the registry so the addin path 'can be later retrieved by the 'Applications' class SaveSetting "MyRunningApps", "AddInLocations", CStr(Me.Name), CStr(Me.FullName) Exit Sub 'we got here means there are more than the allowed # of instances 'quit and get out now skipping the Before_Close event handler exitHere: bSkipCloseEvent = True MsgBox "You have exceed the maximum number of allowed instances", vbExclamation Application.Quit End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) 'if user is closing this xl instance delete its corresponding 'registry entry to indicate it's no longer running If bSkipCloseEvent Then Exit Sub DeleteSetting "MyRunningApps", "AddInLocations", CStr(Me.Name) 'if it's not the first instance delete the associated temp addin If Me.Name "AppsCollection.xla" Then KillMyself Me End If End Sub Private Sub KillMyself(oWB As Workbook) Dim sPathName As String sPathName = oWB.FullName oWB.ChangeFileAccess xlReadOnly Kill sPathName oWB.Saved = True End Sub

Now, install this addin before you can use the Class shown in the next step.

STEP2- Create a workbook , add a Class Module to it's VBProject and name the Class : clsAppsCollection.

Place the following code in this Class Module :

Code:

Option Explicit Private Declare Function ShowWindow Lib "user32" _ (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long Private Declare Function GetComputerName Lib "kernel32" Alias _ "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long Private Const MAX_COMPUTERNAME_LENGTH As Long = 31 Private oColApps As Collection Private ap As Application Private byMaxInstances As Byte Private WithEvents wbCloseEvent As Workbook Public Property Get Count() As Long Call UpdateAppsCollection Count = oColApps.Count End Property Public Property Let MaximumInstancesAllowed(ByVal vNewValue As Byte) 'store this value in the registry so it can be accessed by all 'other running xl applications SaveSetting "XLInstances", "MaxAllowed", "Max=", CStr(vNewValue) byMaxInstances = vNewValue End Property Public Property Get MaximumInstancesAllowed() As Byte MaximumInstancesAllowed = byMaxInstances End Property Public Function OpenNew _ (Optional visible As Boolean = True, Optional WBPathName As Variant) As Excel.Application Dim sErrMsg As String If Not WeAreExceedingMaxXLInstances Then 'if there is 'WBPathName' this xl instance will open it If Not IsMissing(WBPathName) Then If Len(Dir(WBPathName)) 0 Then Set OpenNew = OpenNewXLApp(visible, WBPathName) Else 'wrong pathname entered ?, warn the user and get out sErrMsg = sErrMsg & "Check the spelling of the path name." MsgBox "Path name : ' " & WBPathName & " ' not found." & vbCrLf _ & vbCrLf & sErrMsg, vbExclamation End If Else Set OpenNew = OpenNewXLApp(visible) End If End If End Function Public Function Item(NameOrIndex As Variant) As Excel.Application 'this routine allows to reference the applications by their index # 'or the name of any of its children workbooks for easier use of this proprety Dim i As Byte Dim oWBk As Workbook Call UpdateAppsCollection If Not IsNumeric(NameOrIndex) Then For i = 1 To oColApps.Count For Each oWBk In oColApps(i).Workbooks If UCase(oWBk.Name) = UCase(NameOrIndex) Then Set Item = oColApps(i) Exit Function End If Next oWBk Next i End If Set Item = oColApps(NameOrIndex) End Function Public Sub ActivateApp(App As Excel.Application) 'this is just a nice convinience 'when you want to quickly activate one 'particular xl instance Call UpdateAppsCollection If App.visible Then App.WindowState = xlMaximized AppActivate App.Caption End If End Sub Public Property Get HostComputer() As String 'property similar to the standard Parent prop Dim lLen As Long Dim sBuffer As String 'Create a buffer lLen = MAX_COMPUTERNAME_LENGTH + 1 sBuffer = Space(lLen) 'Get the computer name GetComputerName sBuffer, lLen 'get only the actual data sBuffer = Left(sBuffer, lLen) 'Show the computer name HostComputer = sBuffer End Property Private Sub Class_Initialize() 'set up the 'Before_Close' event sink Set wbCloseEvent = ThisWorkbook End Sub Private Sub wbCloseEvent_BeforeClose(Cancel As Boolean) 'make sure we delete the registry entry that 'stores the # of xl instances once the class workbook is closed On Error Resume Next DeleteSetting "XLInstances" End Sub Private Sub UpdateAppsCollection() 'this routine updates the current # of insatnces 'and assigned their respective pointers to the module 'level collection. 'this routine is run every time a member of this class 'is executed. this is vital to keep upto date with 'the changing number of open xl instances. Dim i As Byte Dim vAddInLocations As Variant Set oColApps = New Collection vAddInLocations = GetAllSettings("MyRunningApps", "AddInLocations") For i = LBound(vAddInLocations, 1) To UBound(vAddInLocations, 1) oColApps.Add GetObject((vAddInLocations(i, 1))).Parent Next i End Sub Private Function WeAreExceedingMaxXLInstances() As Boolean Dim vAddInLocations As Variant vAddInLocations = GetAllSettings("MyRunningApps", "AddInLocations") If byMaxInstances 0 Then If UBound(vAddInLocations, 1) >= byMaxInstances - 1 Then WeAreExceedingMaxXLInstances = True End If End If End Function Private Function OpenNewXLApp _ (Optional ByVal bVisible As Boolean = True, Optional ByVal sWBPathName As Variant) As Excel.Application Dim oNewApp As Application Dim sAddInPathName As String Set oNewApp = CreateObject("EXCEL.APPLICATION") oNewApp.UserControl = True sAddInPathName = AddIns("AppsCollection").FullName oNewApp.Workbooks.Open sAddInPathName Call UpdateAppsCollection oNewApp.visible = bVisible If Not IsMissing(sWBPathName) Then oNewApp.Workbooks.Open sWBPathName End If Set OpenNewXLApp = oNewApp End FunctionThat's it.

Here are two examples you can try once the AddIn has been insatlled & loaded and the Class has been added to your project.

Start a few instances of Excel and run the test routine below :

Example 1

Code:

'In a Standard Module Option Explicit Private Applications As clsAppsCollection Sub Test() 'open a few xl applications instances and 'run this code to create an 'Applications' collection 'and display info about each xl instance Dim App As Excel.Application Dim i As Byte, j As Byte Dim sMsg As String Set Applications = New clsAppsCollection With Applications For i = 1 To .Count sMsg = sMsg & " Application: " & i & vbCrLf sMsg = sMsg & "--------------" & vbCrLf sMsg = sMsg & " WorkBooks Count: " & _ Applications.Item(i).Workbooks.Count & vbCrLf & vbCrLf For j = 1 To .Item(i).Workbooks.Count With .Item(i).Workbooks(j) sMsg = sMsg & " WorkBook: " & j & " Sheets Count : " _ & .Sheets.Count & vbCrLf End With Next j sMsg = sMsg & vbCrLf & vbCrLf Next i End With MsgBox sMsg End Sub

Example 2:

Code:

'In a Standard Module Option Explicit Private Applications As clsAppsCollection Sub Test2() 'allow no more than 3 xl instances @ a time 'and use the OpenNew Method to open one Set Applications = New clsAppsCollection With Applications .MaximumInstancesAllowed = 3 .OpenNew End With End Sub

A word of caution for anyone trying this code! The code in this project makes use of the Registry to store temporary entries inside the 'VB and VBA program Settings' Key during the AddIn auto_Open event. I believe this is safe but if you happen to edit the code, subtle errors can arise and can be tricky to locate. You may have to manually delete the above temp reg entries by running the RegEdit.exe Applet.

The MaximumInstancesAllowed Property can be particularly troublesome if edited !

Tested in XL 2002 Office XP.

Any feedback would be much appreciated.

Regards.

1. If B10=100 blue formatting needs to appear, simple enough, in conditional format 1 I did: =IF($B10=100,1)

2. If D10=100 green formatting needs to appear, again, simple enough, in conditional format 2 I did: =IF($D10=100,1)

3. If O10 is not equal to R10 orange formatting needs to appear. This seemed simple enough, I did =IF($O10$R10,1) which worked in theory, except the problem is some of the numbers are only a couple of cents off. I don't care if that is the case, they are basically equal so I need to make the formatting show if 010 is not within $1 of R10. I can't figure out how to make a range, I tried =IF($R10-1>$O10>$R10+1,1) but this didn't work. If anyone has any ideas it would be wonderful. Thank you so much for your help!

SO!, what i´m trying to do is:

1.- i have 2 data entryes

2.- #1: in Hz and #2 in Mw

3.- each data changes every one second

4.- each data from each cell needs to be puted in a formula to obtain a sumatory or promedy or max´s or min´s etc (by example =MIN (A1))

BUT WHAT I NEED IS OBTAIN THE DATA FROM CELL A1 AT THE SECOND 1 , GET THE DATA FROM A1 AT THE SECOND 2 AND ONLY THEN OBTAIN THE RESULT OF THAT FORMULA

(= MIN (DATA FROM SEC 1, DATA FROM SEC 2))

that is the =MIN value based on a minimal change in the obtained data, based in a change of one second.

once i obtain that data i´ll treat that cell like if was 2 data sources, or two diferent values, because in the main formula, a number of variables is needed, if i put number of variables =1 the result is not succesfull, BESIDES my system values changes every one second

so, there is a form to put that data in a diferent cell to obtain a new column of data?????

______________________________

d __________________________e

the 1st sec data___________the 2th second data

whit that i can promediate the data and make my calculations!!!

PLEASE I KNOW YOU KNOW! HELP ME !

THANKS A LOT

OK, what I'm doing is basically tallying up a series of tournaments in order to show overall winners. I have a formula to do that- that is no problem.

What I need to do however is for every one of these individual tournaments is to get a score. The first tournament there are 64 entrants and I want to gte a score out of 128.

The first person #1 should have a score of 127 (just to bring it on par with the other tournaments of 128 I am comparing it with.

The second: score 125

The second last: score 3

Last score 1 (just to make it one better that if the tournament was not entered at all.

To do this i have used the formula =129-1*(2*C5) (where C5 was their place in the tournament).

The problem I have is that I am using a bakers dozen numbers of tournaments and in each one slightly different people contested. Some people were in every tournaments, some were in a few and others may have only been in one.

The trouble I have is that when a person did not enter a tournament he gets a placing of "0", which means that point wise he gets 129, which is better than first place.

In actuality I want him to get 0. How can i put some kind of limit so that the calculation is only done if the placing is greater than 0 (ie 1 and up)? But if the placing is zero, then the points will come to be zero)?

Sorry if that was hard to follow, you probably didn't need to know the background of what I needed, but there it is.

Please help me someone.

Cheers.

My code works just fine if the file is named *.txt. But if I try this on the same file named *.csv ALL the columns import. Also, I need to import some "number" columns as text. Again, works fine on the *.txt, but not the *.csv.

Can anyone offer some insight on how to get this to work. (a solution other than renaming the file first )

Thank you

Code:

file = Application.GetOpenFilename() Workbooks.OpenText Filename:=file, Origin:=437, StartRow:=1 _ , DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _ , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 9), _ Array(3, 9), Array(4, 1), Array(5, 1), Array(6, 2), Array(7, 2), Array(8, 9), Array(9, 1), _ Array(10, 1)), TrailingMinusNumbers:=Trueps. the file I importing is 48 columns, I only show few above to make it easier to read.

I am trying to create a database to be used for SQL and I am using excel to make this happen. One of the requirements that I must do is convert the .xls file into .csv file. So, I have all the data with the calculations and I already know that if you saved the file as csv with the calculations, it will show as text only.... So, I copied the entire worksheet and used paste special and clicked on "values and number formats". Doing it this way eliminates the formulas and just display the values in the sheet to be saved as .csv format. After saving the file as .csv and then opened it a few days later, most of the data changed dramatically and I can't figure out why. The Averages I used are displayed as 0 (zero) in several columns. The formulas that I used to calculate in the original excel sheet are now displaying as 0 when there should be a number of a finished product.

Does anyone else know why it's doing that? If I am doing it wrong, please let me know the correct steps so I can correct it as soon as I can.

Thanks! I'm using Excel 2003 in XP environment.

I am trying to find records that appear more than once in my sheet and then copy just one of the record copies to another location. Using Col O which is the tax parcel number if there are 2 or more parcel numbers such as O6,O7,and O8 I need to grab or highlite just the first entire row O6 or have all the 1st record copied to another location. I do need the entire row though because I am making mailing labels from the record.

Explanation:

These are people who are deliquent on their property taxes. Each record that they have is one year of taxes owed. I only want people who have at least 2 years of late taxes. But I only need to have one of their records to make a mailing label. Any help will be greatly appreciated.

Thanks

Galen

*removed by Admin

I'm using excel 2007 currently and am trying to write a list.

I've importerd a selection of data from a database we use. Column A shows the part number, Column B shows the quantity and column c shows cost. The Parts number we use are codes set out like 12345-12345-12. And in total we have over 200 varieties of this number, each variant relates to a product we make here on site.

If possible, (and i'm not sure this is) I would like a formula that would list each code found in the imported data, once. So effectivly the same results you see when you have set a filter up. You can click on the filter arrow and see each different variation used.

So if the first five lines in column a were:-

00000-00000-01

00000-00000-01

00000-00000-02

00000-10000-03

00000-00000-01

Then in column D i would like to see:

00000-00000-01

00000-00000-02

00000-10000-03

If any of you have any ideas at all (bar the only one i've found so far, manually type them in) then i'd love to hear it and would appreciate your help.

I hope I've made that clear enough, If not then please, please, please, ask any questions you need to.

Thank you very much in advance for any help you, much venerated, people can offer me. And if you can't help me then thanks foir reading this far at aleast :D

Ricky

Please see attached picture of what I am trying to achieve. Please bear with me while i try and explain as best i can the rationale behind this, and then I'll ask the question.

I have a s/sheet in which there are 2 user inputs:

1. the user enters numerical values in column "E"

2. the user selects a value from a pulldown menu in cell "H10" to match the input in column "E".

For example (as shown in the picture), if "3" is selected in "H10", then the user would insert values in column "E" (green boxes) corresponding to multiples of "3", ie. days 0,3,6,9,12,15.... as shown in column "D" (grey boxes).

As another example, if "5" were selected in "H10", then entries would be made in days 0,5,10,15,20....

In other words, for any value in "H10", the corresponding MULTIPLES of that number are referred to in column "D" in order to complete the relevant entries in column "E"

Once this is done, a series of (corrected) calculations are made corresponding to ALL days included in column "D" and the results presented in column "F".

One requirement of the s/sheet is to reproduce the grey (column "D") and pink (column "F") boxes into a NEW table (columns "I" & "J") such that it is easier to see the relationship between the days (in this case, multiples of 3, ie. 3,6,9,12..) and the corrected values corresponding to those days (ie, pink boxes in column "J").

For this, i have used the formula that is highlighted in the formula box at the top of the screen. Thus, for any user input into "H10", this table automatically shows the relevant multiples and the corresponding corrected numbers.

OK, so far so good.

The problem is this: i want to recognise cell "E47" as the last cell in column "E" containing a numerical value (via user input), and then report ONLY its partnered values in cells "D47" and "F47" up until that point, ie. the grey and pink cells shown in columns "I" and "J". You will notice that the current formula will provide values for ALL multiples of "3" and not just up to "27".

I cannot just simply copy/reproduce what is shown in columns D/E/F into a new table to achieve this because the user input in column "E" has the potential to always vary, BOTH in time (ie, total number of days) AND in multiplicity (eg, 0,2,4,6... or 0,4,8,12... or 0,7,14,21,28.....).

So what i need is to identify the last cell in column "E" that contains a numerical value, and then report its partnered values only up to that point.

Can this be achieved with the current OFFSET formula i have?

i've tried incorporating several things but to no avail. Many apologies for the long-winded explanation, but i am trying to make it easier on whomever has to try and decipher this!

Many thanks in advance.

I welcome others to add their thoughts or experiences on making it even easier. I am sure there is enough knowledge on this board to allow others a great start to building this type of macro.

Interesting Food for Thought

Recording the macro is fairly straight forward. But when you want to re-use the macro for similar graphs you have a few issues. I was able to build the macro with some "re-usability" with only a few small changes to make it work on similar graphs.

Details

1) The active chart name "Chart 1" etc, needs to be changed (Is there a way to have this dynamic?).

2) If the number of series in the chart can change from chart to chart. It is a good idea to build in a loop to apply formatting to the number of series.

3) My chart applied trend lines. Therefore it added to the legend. I didnt want to show the trend in the legend so I had to develop a way to delete them. I used a similar loop getting the count of series.

4) When changing line weights etc. When you go above 6 or 7 pt I think, the tick marker automatically switches to a solid block (I couldnt get it to change back). I originally had a loop to change the marker style but the issue caused me to make it all the same.

Anyway...here is the code...

Sub Reapplygraphformat()

'

' Reapplygraphformat Macro

' Reapply graph format to closed defect duration graph.

'

' Keyboard Shortcut: Ctrl+r

'

Dim LegendCounter As Integer 'a counting variable for number in legend

Dim SeriesCounter As Integer 'a counting variable for number in series

Dim ColorHolder As Integer 'a counting variable for number of Color

ActiveSheet.ChartObjects("Chart 2").Activate

SeriesCounter = 1

Do Until SeriesCounter > ActiveChart.SeriesCollection.Count

If SeriesCounter = 1 Then '

ColorHolder = 5

Else

If SeriesCounter = 2 Then

ColorHolder = 7

Else

If SeriesCounter = 3 Then

ColorHolder = 6

Else

If SeriesCounter = 4 Then '

ColorHolder = 8

Else

If SeriesCounter = 5 Then '

ColorHolder = 9

End If

End If

End If

End If

End If

ActiveChart.SeriesCollection(SeriesCounter).Select

With Selection.Border

.ColorIndex = 57

.Weight = xlMedium

.LineStyle = xlContinuous

End With

With Selection

.MarkerBackgroundColorIndex = xlAutomatic

.MarkerForegroundColorIndex = xlAutomatic

.MarkerStyle = xlX

.Smooth = False

.MarkerSize = 5

.Shadow = False

End With

ActiveChart.SeriesCollection(SeriesCounter).Trendlines.Add(Type:=xlLinear, Forward:=0, _

Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select

ActiveChart.SeriesCollection(SeriesCounter).Trendlines(1).Select

With Selection.Border

.ColorIndex = ColorHolder

.Weight = xlThin

.LineStyle = xlContinuous

End With

SeriesCounter = SeriesCounter + 1

Loop

LegendCounter = ActiveChart.SeriesCollection.Count * 2

Do Until LegendCounter = ActiveChart.SeriesCollection.Count

ActiveChart.Legend.Select

ActiveChart.Legend.LegendEntries(LegendCounter).Select

Selection.Delete

LegendCounter = LegendCounter - 1

Loop

End Sub

Thanks for any additions!!

I have a spreadsheet which has an output of order numbers and order details from different Suppliers. The problem with the spreadsheet in its current format is that I am unable to use data filter and choose all orders by a particular supplier.

Column A is blank.

B1 has the supplier name

Row 2 is blank

Row 3 is blank

B4:H10 has the order data in it

H11 has the total of that suppliers orders. nothing else in Row 11

B12 has a new supplier name

Rows 13 & 14 are blank

B15:H19 has the order data

H20 has the total of orders, nothing else in that row.

This format just repeats itself, some suppliers have more orders than others and sometimes it's not 2 rows of blank data after the supplier name, its only 1 blank row. Not sure why this happens but cant change it.

I think that a way to be able to do this is to somehow get the Supplier name to move from column B to column A but to also place it beside all of the order numbers for that particular supplier. Then data filter could be used to show all orders by one Supplier.

I’m guessing there will be some way to do this using VBA, which is a bit like black magic to me. I can understand it once it is written but I am totally lost at writing it from scratch.

The other problem with this sheet is that for some of the suppliers you get the Supplier name in column B, 2 blank rows and then the data. For other suppliers you only get 1 blank row then the data, not sure why this is.

At the same time in column H at the end of each data set there is a cell with a total for each supplier, I would like to take this out as if everything is going into a table format which can be data filtered then this is no longer required.

Please see the attached spreadsheet which hopefully makes my explanation seem slightly clearer! I’m using excel 2007 but have saved the example in 2003 format.

No luck finding an answer? You could always try Google.