After creating a personal workbook to hold my macros, it now opens whenever I start Excel. Is there a way to keep it hidden?
Thanks,
Rich
After creating a personal workbook to hold my macros, it now opens whenever I start Excel. Is there a way to keep it hidden?
Thanks,
Rich
Hello, Mike.
Not sure if this would help, but have you tried a pivot chart?
Rich
Thank you!
Rich
Thanks, xlite, but no.
What I want is what happens when you go to File>Open or Ctrl+O. A window pops up and you click on the file you want to open.
It's probably really simple, but I can't seem to figure it out.
Rich
xlite,
That would work, but I'd like it to open the OPEN FILE window so that the user would be able to get the file no matter where it was located.
Rich
I've been using the following code to open a file from my floppy drive. Unfortunately, the file is now too large to fit on the disk.
Sub ImportFile()
ChDir "A:\"
Workbooks.OpenText Filename:="A:\ALARMDET.TXT", Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array(10 _
, 1), Array(20, 1), Array(30, 1), Array(40, 1), Array(50, 1), Array(60, 1), Array(70, 1), _
Array(95, 1), Array(100, 1), Array(103, 1), Array(107, 1), Array(110, 1), Array(119, 1), _
Array(124, 1), Array(135, 1), Array(140, 1))
End Sub
Can someone show me how to modify the code to allow me to select the file from my hard drive?
Thanks,
Rich
Hi Sal
Select the DATE range.
When the range is highlighted, go to Conditional Formatting.
In condition 1, select "Formula Is" and enter
"G2>today()"
without the quotation marks. Then select the formatting changes you want.
Click on Add>> and in condition 2, select "Formula Is" and enter
"=g2>0.25*SUM(2:2)"
without the quotation marks. Again, select the formatting you want.
Condition 2 assumes there are no other values in the row you are totaling.
Hope it helps.
Rich
Hi want,
If I remember my Lotus days correctly, I think this is what you need...
Range("G3").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlDown)).Select
Hope it helps.
Rich
mikajake,
It will work by replacing the "SUM(D2:D7)" with your existing formula "+D8+D11+D14+D17+D20+D23" in the cell you total the frames.
You might try changing the total frames cell to show an error message if it sums higher than 50. Something like...
=IF(SUM(D2:D7)>50,"Error! "&SUM(D2:D7)&" total frames.",SUM(D2:D7))
...inserting the correct range in place of D2:D7.
You could also use a variation of the formula in Conditional Formatting to highlight the totals cell a different color when it's over 50.
For limiting each frame to 10 frames, setting up the data validation to - whole numbers equal to or less than 10 - should work fine.
Rich
Try adding this:
Sheets("Sheet1").Select
Sheets("Sheet1").Move After:=Sheets(4)
...using the appropriate worksheet name and number of sheets.
Rich
Hi Dave,
Highlight the cell in Sheet 2 that you wish to display the value from Sheet 1.
Press + to initiate a formula.
Navigate to and highlight the cell in Sheet 1 containing the value you wish to display on Sheet 2.
Press Enter.
In the cell on Sheet 2 you should now see a formula similar to this:
=+Sheet1!A1
where "Sheet1" is the name of the worksheet containing the desired value and "A1" trhe location of the cell containing the desired value.
Hope that helps.
Rich
Gentlemen, thank you all.
I was doing it properly, but didn't realize that the dates in a pivot table are text. I had to expand my formula to compensate.
Would anyone care to see if it can be improved upon?
=IF(ISNA(VLOOKUP(TEXT($A369,"mm/dd/yyyy"),ReportBase,4,FALSE)),0,VLOOKUP(TEXT($A369,"mm/dd/yyyy"),ReportBase,4,FALSE))
ReportBase = dynamic range of pivot table
Thanks
Tom,
I'm having trouble setting the range using the pivot table
Basically what I want to accomplish is this:
The pivot table's first column is all dates. The tables second, third, and fourth columns contain the information I want to retrieve.
When I substitute the dynamic range formula for "Pivot Table", the cell returns a NAME error.
Assuming my pivot table is located in a worksheet labeled "ProductionQ" and the data's left, top corner is cell A7, can you walk me through the dynamic range formula written in cells on a different worksheet?
ie VLOOKUP(A2,******,2,FALSE) where ****** is the Offset formula.
Rich
Is there a way to use a pivot table as the table array in a VLOOKUP formula?
Example formula:
VLOOKUP(a2,"Pivot Table",2,FALSE)
Thanks,
Rich
Hopefully, a workbook is attached.
Again, my thanks.
Rich
Andy,
Thanks, Andy, for the info.
The XY Chart Labeler add-in works, but the labels are static and it required a separate pivot table for the COST totals.
I really would like it all to happen with just one table and a click of the refresh button.
Rich
I am trying to create a 100% stacked column chart from an existing pivot table.
I have each column (MONTH) representing the % of the count of parts ORDERED within three PRIORITY catagories: Emergency, Rush and Rountine.
I would like the data labels for each point to show, not the count of parts ORDERED, but the sum of COST of each PRIORITY in each column (MONTH).
Is this possible using Excel 2000 or 97?
(All caps above indicate existing columns in the pivot table.)