Posts by eduboys

Whenever I copy from an application that contains text that has a space " ", the text to columns feature seems like it is automatically turned on in excel.

For example, if I were to copy and paste "Hello World" from notepad to excel, I would receive two columns in excel, one with the word "Hello" and one with the word "World". Any idea what would cause this to happen, or how to fix it?

(Please Close) - This was a memory issue that caused this to occur.

Re: Navigating Between A List And The Remainder Of A Worksheet

If I am in a view that shows columns J through column P, with the list ending at column P, and I use an arrow key to move to column Q, then my view shifts to show me columns Q through column Z rather than showing me columns K through column Q as would be expected if there was no list in the spreadsheet.

It seems to happen only when the list is an exported list from SharePoint, not when I create the list within Excel.

If I setup columns containing data next to a list (Excel 2003), and navigate between the list and the remainder of the spreadsheet, excel always presents a new view that repositions the viewable area in the spreadsheet. This also presents itself as a problem when I navigate between worksheets and/or applications. Is there anyway to change Excel's behavior to keep the same position on the screen? (Freezing panes will not work, nor will splitting windows).

Thanks in advance.

Re: Exclude Network Path In PivotTable Based on Sharepoint

Has anyone else experienced a similar problem?

Re: Exclude Network Path In PivotTable Based on Sharepoint

The problem is that all the text prior to the "=WhatIf" causes the formula to break. When I attempt to refresh the data, excel spits out an error "Cannot open PivotTable source file '2008%What%If%Budget.xls'"

I have an excel file linked to a sharepoint 2007 network space. Everytime I open the file, my pivot tables will not refresh, because the source of the pivottable seems to update automatically to include the network drive name on them which somehow causes a problem. Is there anyway to solve this? I put a snippet of the source in the pivottable as an example.

"http ... /PMO/Shared%20Documents/Finance/Budget/2008/2008%20What%20If%20Budget.xls'!=WhatIf"

Is it possible to use an "If Statement" in a pivot table calculated field? I always seem to get the false condition even if I believe the statement to be true.

if(PivotTableField = 1, "Good", "Bad")

The code would always return "Bad" no matter if PivotTableField = 1 or not. I saw there was an older thread on the same issue, but nobody ever gave a concrete answer.

Re: Workbook_beforesave Not Acting Properly

Amazing!

Any idea why all of a sudden the events would be disabled as I've never written any code using that functionality? Could it be due to a plugin that was installed on my machine? Hyperion Essbase possibly?

I have the following code in the "ThisWorkbook" section of an excel file.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Application.ScreenUpdating = False
    UpdateInitiativeData
    UpdateInitiativeList
    Application.ScreenUpdating = True
    MsgBox "Successful"


End Sub

Where UpdateInitiativeData and UpdateInitiativeList are two subs also in the "ThisWorkbook" section.

For some reason when I save the file, nothing runs. The message box does not even popup. Any suggestions on how to fix this? I've even tried Taking all the code out of the "ThisWorkbook" section except for:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    MsgBox "Successful"


End Sub

And I've had no luck with that either. No message box popup.

Thanks in advance!

Re: Generating different products/sums by linking to other table/worksheet

You can do exactly that!

'    Col A      Col B   Col C
1) Work Type  Time   Abbr 
2) Fun work      4      FW 
3) Boring work   7      BW

If this was your table you could do a VLOOKUP to find the amount of time for the type of work.

=Vlookup("Fun Work", $A$1:$C$3,2,FALSE)

You would then need to multiply that lookup, by the units of work, in this case 10 for "Fun Work"

This formula will look up the amount of time it takes for each corresponding work type. If you wanted to list the abbreviation you could use this formula:

=Vlookup(Work Type, $A$1:$C$3,3,FALSE)

Re: Slow File - Using Sum If Array Formulas - Any Suggestions Appreciated

A database will not work for this.

What about concatenating several cells, and using that in a sumif() formula instead. Will that make it any faster?

I use some sum if array formulas (i.e. {=SUM(IF(ResourceDataProjectNumber=$B4,IF($A4=ResourceDataManager,ResourceDataJanuary)))} - These formulas use dynamic ranges - Thanks OzGrid for introducing me to these gems of excel.

I need to use these formulas as I am contrasting time that we are forecasting for individuals to actual time booked by these individuals. As this time originates from from two totally different data sources (I cannot combine them), I have no idea how to display this in a pivottable.

Since there is so much data, and so many formulas (6474 array formulas). These formulas are searching through a tremendous amount of data (41000 cells from one data source - and 23,000 cells in another). The sheet is extremely slow to calculate (Roughly 2.5 minutes - I am on a 1.8 ghz intel machine (not sure what type of processor) with 512 megs of ram).

Any suggestions as to how to speed this up?

Re: Update link without open resource file

Not sure if this is feasible, but instead of linking, I have imported worksheets from other excel books to ensure consistent data across several workbooks as I always have trouble linking excel files. (I tend to map drives and this causes problems if other users open the workboosk)

Data -> Import External Data -> Import Data

Re: 1024 Cahracter linit on visible cells and printing

Does anyone know off the top of their head if this will be addressed in Excel 2007?

I am doing a good deal of reporting with excel, and I am now hitting a bunch of the limitations!

Re: Autofit Problems with a Row that includes a formula referencing another cell

I set them to the same column width. They are set to wrap text. For some reason it is just not working. I think I'm going to have to just copy and paste values on an ongoing basis as these files are updated every 2 weeks. Not that big of a deal I guess.

Centering across columns is not an option as this is a form that is filled out by others.

I have a document that I created that has merged cells. In order to autofit the rows of this document, I referenced the merged cells in singular cells that are not visible to the users of the document. (They are not hidden)

It had been working relatively nicely, but now the autofit function is not working correctly. If I copy and paste values into the cell it will work, but this would not be my ideal solution.

Any suggestions? I have tried playing with the number format, which works for the most part if I set it to "&" - text. It doesn't work for some longer cells with line breaks in them.

Re: Linking to another excel workbook (Import Data)

I'm actually not using links. I'm importing the sheet through the data tab. I have had major issues using links in excel sheets (network permissions), and I've found that this solution is working out much better for me, except for the read only problem.

Re: List of .xls files in directory

I've used this snippet to open all files in a directory. The line I stated should give you the filenames of each file in a directory that you could store however you'd like. Definitely some extra code in there, but you should be able to salvage something from it.

Dim Test, sPath As String
Dim Folder, oFSO As Object
On Error Resume Next


Set wbCodeBook = ThisWorkbook


sPath = "Your Path"
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = oFSO.GetFolder(sPath)


With Application.FileSearch
    .NewSearch
     'Change path to suit
    .LookIn = Folder
    .FileType = msoFileTypeExcelWorkbooks
    .SearchSubFolders = False
    .Filename = "*.xls"
    If .Execute > 0 Then 'Workbooks in folder
        For lCount = 1 To .FoundFiles.Count 'Loop through all.
            'This next line should get you the filename of all excel workbooks int he directory
            Workbooks.Name(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
         Next lCount
    End If
End With

I am importing data from an outside excel file to several other files. I requested that the data be refreshed every time I open the file that references the outside excel file.

Whenever I have a file open that references the outside excel file, I can only open the outside excel file in a read-only state. Why is this, and is there any way around this issue?

Re: pivot table advice

You may also want to have the pivot tables reference each other in the wizard, instead of always using the dynamic range. This will cut down on the size of your excel file.