Is it possible to automatically set a password for all excel files? Is there a setting w/in the options to do something like this or would it have to be done with custom VBA?
Re: Merge dynamic ranges for Pivot Table
Suppose you have to have source data on 2 sheets. ie, you are being forced to use a pivot table to report on 70,000+ records and the level of detail of the source data cannot be summarized. How do you merge 2 tables with the same columns, on separate sheets, allowing for the creation of pivot tables on other worksheets?
Hopefully this is an easy one, but I haven't found an answer to it.
When you enter a the path of a file in a Word doc, which is located on a network and you enter something like...
Microsoft breaks the link at the space between New and Files. How can you enter this so it recognizes and preserves the link?
Re: Deactivate 4000+ Hyperlinks
You can also paste the data to a new sheet, using Paste Special-Values.
Re: macro to F2 on a cell and enter
Another approach is to do a find and replace for contents of the cells. For the numbers treated as text problem, where you want the numbers treated as numbers, you can record a macro to find the numbers 0-9 and replace them with themselves. This will fire the edit cell, event.
Re: Creating a Folder, Exist?
The sample posted earlier works fine, unless you have multiple levels of folders to create, in which case you need to loop thru each level to check for existence and create if needed.
It would look something like this.Code
BAKDIR = "D:\Excel\Test\Backup\" X = Len(BAKDIR) For Counter = 4 To X Y = InStr(Counter, BAKDIR, "\") ZDIR = Mid(BAKDIR, 1, Y) 'Create FileSystemObject Set FSO = CreateObject("Scripting.FileSystemObject") 'Check if directory/folder already exists ... If NOT Create it. If Not FSO.FolderExists(ZDIR) Then FSO.CreateFolder (ZDIR) End If Counter = Y Next Counter
Re: The new getpivotdata function takes too much space
IMO the 2K version of GETPIVOTDATA sucks compared to the XP version. Maybe you could set parameters based on cells in your file, so your formula doesn't have long text strings, but cell references.
Re: Text to Number Syntax
I wrote a script that will find/replace the numbers 0-9 on whatever my current selection is. This, like the *1 approach, initiates an Excel event that updates the value to be recognized as a number.
Re: Automte report generation
I've adjusted the summary table and the organization of the source data to allow you to plug in the value for the current week and have the previous 3 weeks calculate and sum the source data for your graph.
I did mess up the graph, so you'll have to fix that. Also, I didn't use any named ranges for the SUMIFs that I used in the summary table. So keep in mind this is a rough revision that could and should be refined to meet your needs.
Hope this helps.
Re: Automte report generation
Could you post a small sample of the file? I imagine it may require slight modifications to how the data is stored and presented.
Re: Stop Vlookup Returning #N/A!Quote from Dave Hawley
1) Place the VLOOKUP's in any Column, say Column A
2) In Column B use: = IF(ISNA(A1),"",A1) and copy down.
3) Hide column A and use Column B as your VLOOKUP Results.
See my wrintings on Efficient Excel Spreadsheet Design for more ways and details.
Dave in the following thread, you mention using conditional formatting to hide the #N/A result.
Could you post an example of the condition setting?
Re: sumif error using dynamic rangesQuote from tinyjack
It makes no difference if they are 'dynamic' or just plain named ranges, the formula should work as written if the ranges are refering to the right cells.
Could you attach the file?
Here is the file. I put the formula examples next to the appropriate cells, three examples all looking for the first value in the SDD column.
Thanks for the help
Re: sumif error using dynamic rangesQuote from shades
Then they are not dynamic ranges.
Here is what I mean by using dynamic named range (Go to Insert menu, then Name, define name):
When I set it up this way, it worked fine.
Yes your are right. The problem with using this volatile function is I have alot of similar ranges and can't afford the processing overhead.
So it shouldn't matter how I name the ranges it should work but it doesn't.
I am using a SUMIF function that utilizes dynamic named ranges for the lookup and sum arguments and I am not getting the correct result.
=SUMIF(PortfolioSDD,$B10,PortfolioDlrs) is returning 0
=SUMIF(Portfolio!A:A,B10,Portfolio!H:H) is returning 300,000 (which is correct)
the PortfolioSDD range is A3:A289 and the PortfolioDlrs range is H3:H289
What could be causing this issue?