Costpoint Import Upload information

Info on how to upload information into Costpoint.  Note to self, PDFs of these pages are also stored on Drive

https://help.deltek.com/Product/Costpoint/7.1.1/GA/PJPPREP.html

https://help.deltek.com/Product/Costpoint/7.1.1/GA/PJPPREP_Input_File_Information.html

http://help.deltek.com/PRODUCT/COSTPOINT/7.1.1/GA/PJPPREP_Step-by-Step_Procedures.html

Excel Hidden Names and External Links that Won't Break

Was getting error messages when opening an excel file (not macro enabled) warning of links to external files and links to what appeared to be sharepoint sites.

Excel wouldn't let me delete the external link via Data>Connections>Edit Links.  The link simply would not delete.

There didn't appear to be any bad references under Name Manager.

Ultimately it turned out to be:

1)  Data Validation which was referencing a list on an external file (the cause of the external link that I couldn't delete).

2)  Hundreds of hidden Names that weren't visible in Name Manager (I suspect the cause of what appeared to be links to sharepoint sites).

Resolving Item 1) Data Validation was cumbersome.  You had to go to each worksheet and had to find any Data Validation.  I did this using HOME>Editing>Find&Select>Go To Special>Find Special>Data Validation (All).  Once I found the range, I went to DATA>Data Tools>Data Validation>Data Validation.... and changed the settings to Allow>Any Value.  I still couldn't delete the external link so I moved on to seeing if there were other areas that could be linking to this external file.

Resolving Item 2) hidden Names.

I finally found a Macro at the following link which then made a TOOONNNNNNNSSSS (I think hundreds) of bad named references visible in the Name Manager  http://professor-excel.com/named-ranges-excel-hidden-names/

Sub unhideAllNames()
'Unhide all names in the currently open Excel file
    For Each tempName In ActiveWorkbook.Names
        tempName.Visible = True
    Next
End Sub


Once I deleted all the hidden Names I was then able to delete the external link.


Another tool I used to hunt down possible issues was FILE>Info>Check for Issues>Inspect Document

Count unique text values in excel range

Thanks to this site for giving me the solution to this challenge.
https://exceljet.net/formula/count-unique-text-values-in-a-range

Excerpt from the site:

Handling empty cells in the range

If any of the cells in the range are empty, and you want to use FREQUENCY instead of COUNTIF, you'll need use a more complicated array formula that includes IF:

{=SUM(IF(FREQUENCY(IF(data<>"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1))}

Note: because the logical test portion of the IF statement contains an array, the formula becomes an array formula that requires Control-Shift-Enter. This is why SUMPRODUCT has been replaced with SUM.

Too many cell formats in excel

For that infuriating "too many cell formats" error, I've found that sometimes, though not always, if you save down to Excel 97-2003 it seems to fix it.  But it's hit or miss.  It can also destroy everything.

Another more involved process is as follows, however, is also with risk….

This can completely destroy your excel file by removing ALL formatting, formulas, EVERYTHING and leave you with just raw useless text so SAVE another copy!!!!!!

Go to:  http://support.microsoft.com/kb/213904

Under the "Resolution" section click on "Remove Styles Add-In"

***Note, excel should be open***

Click on "Downloads" tab

Download: "RemoveStyles.xlam for Excel 2007 and newer"

You will get a download notification, and should click "Open"

You should now see a new button on the excel Home tab

****Highly HIGHLY recommend saving another copy of your excel file before proceeding in case something goes haywire you can still go back to your original file*****

Click on the "Remove Styles" button, and hope for the best.

-------

You know you've downloaded and installed the Remove Styles add-in before but now the button isn't showing on the home tab.

Go to File > Options > Add-Ins > Manage: Excel Add-Ins and click Go…

You will see a list of Add-Ins and likely the Remove Extra Styles is "checked".  You will want to "uncheck" Remove Extra Styles" then click OK, then go back in using the above steps and "check" Remove Extra Styles and click OK.  This should cause the button to reappear on the Home tab.

-----------------------------

OTHER THINGS TO TRY

Below ideas are not "confirmed" fixes, but things I have tried in the past that have seemed to help resolve the error

-Remove unnecessary conditional formatting: Home tab > Conditional Formatting drop down > Clear Rules from Selected Cells (or you can Clear Rules from Entire Sheet)

-Remove unnecessary drop down lists from Data Validation:  Select applicable cells > Data tab >

Parse Characters from a Cell

(LEFT(B55,22))

01302.03.3001.000.1000 - OY3 LABOR SUNK COSTS

will pull:  01302.03.3001.000.1000

Insert Excel File Name into a Cell

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

source of information:  http://mrspreadsheets.com/1/post/2013/01/excel-function-tutorial-7-insert-the-current-file-name-into-a-cell-with-getcell.html

Ostensible Subcontractor Affiliation links

http://smallgovcon.com/tag/ostensible-subcontractor-affiliation/

http://www.acq.osd.mil/osbp/councils/marc/resources/201111-small-business-teaming.pdf

http://www.cbh.com/teaming-agreements-and-the-ostensible-contractor-rule/

http://www.ecfr.gov/cgi-bin/text-idx?c=ecfr&tpl=/ecfrbrowse/Title13/13cfr121_main_02.tpl

http://www.ncmahq.org/docs/default-source/default-document-library/articles/cm0911---14-22

Kudos - 12 July 2014

Steve Baird regarding major proposal effort - Amy, thanks... The way you built your spreadsheet really assisted us in running all the different options in a very time sensitive environment ....it was a pleasure.

Excel formula

Old Salary X % Reduction = New Salary

When you only know the new salary and % reduction that was used, the formula to derive the old salary is:

<New Salary> / ( 1 - <Percent Reduction>)

Kudos - 25 November 2013

Deb Aitkin - I so very much appreciate your comments.  I heartily agree that Amy is a great asset to our company!  I know that MCTP has always been one of those "moving target" types of contracts and it really keeps her on her toes.  THanks again.

Norm Greczyn - I am the PM for the Cubic portion (as a sub) of the MCTP contract. Recently we went through the development of a proposal for OY 2 of this contract and then, a little over a week out from the start of the OY, we had to embark upon an extensive revision. I would like to command Amy Hunter for outstanding work on this endeavor, which was far from easy. I have now worked with Amy for two years as the PM here, and she has always been a tremendous asset: very precise, always willing to take the time to explain things to me, and tireless in her pursuit of excellence. I am very happy to have her working this contract. She takes great care of Cubic and its employees!

Remove Extra Spaces From Excel

http://theapptimes.com/excel-2010-how-to-remove-extra-spaces-in-cells/

Add comments to excel formulas

lifehacker.com-add-comments-to-a-formula-in-excel

Allowable mileage per day

C5060 ALLOWABLE PER DIEM (FTR §302-4.200)
A. Travel of 12 or fewer hours (12 Hour Rule). A per diem
allowance must not be paid when the official travel
period is 12 or fewer hours (FTR §302-11.2).
B. POC Use to the GOV'T's Advantage. When POC use for PDT is
authorized, the per diem allowance is the
lesser of the:
1. Result of allowing 1 day of travel time for each 350
miles of official distance between the old and new PDSs
or authorized points. If the excess is 51 miles or more after dividing
the total number of miles by 350, one
additional day of travel time is allowed. When the total official
distance is 400 miles or less, 1 day's travel time
is allowed (par. C5060-C), or
2. Actual travel time in full days (e.g., 9 days and 3
hours is 10 days).

Source: Joint Travel Regulations - current as of 18 March 2013

Kudos - Jan 2013

1/29/13 - Ron Abney

I'm writing to tell you about the extraordinary job Amy has done since
she has been working in our Orlando office. Having worked with Amy
when she operated from the Lacey office during my first year as MTSS
PM, I was well aware of her comprehensive and insightful knowledge of
the MTSS contract in particular and of contracting procedures and
regulations in general. Consequently, I was very happy when she
decided to come to Orlando because I believed that having in-office
access to her knowledge, experience and insight would be immensely
beneficial to my entire team. Amy, however, has exceeded my
expectations of the benefits she would provide. As I had anticipated,
she has been a remarkable resource for contract advice and she has
fostered an environment of increased trust, respect and confidence
between my office personnel and the government contracts personnel
they deal with. But she has also been a superb team player who has
gone above and beyond to provide team members with explanations,
background and guidance about contractual processes and resolution of
contract issues. Her unremitting commitment to excellence and her
exacting attention to detail have been positive and motivating
influences for all of my employees. She has been a leader and a
mentor and in both roles, she has been the epitome of professionalism.
It is indeed a pleasure working with someone of her caliber.
Sincerely,
Charles R. Abney

SCA Computer Professional Exemption Criteria

Consideration of Computer Professional vs Non-Exempt

NOTE - this information and these values were accurate in 2010 and may no longer be valid or applicable in any way.  Obviously, this is a process only as the comparison to job duties / job descriptions is not provided in the below explanation.

 

I compared the job duties outlined in the job descriptions to the duties listed in the FLSA's computer professional exemption test.  I believe that under Federal law all positions would be exempt based on the job duties and the overall nature of the jobs.  As far as California regs, I found that the computer professional exemption would apply as well.  However, the minimum hourly rate for a California Computer professional is much higher than the minimum hourly rate established under the FLSA.  For 2010, Computer professionals must earn a minimum fixed salary of $79,587.50 per year or $37.94 per hour for all hours worked in addition to meeting the duties requirements outlined below.

Update: Additional information can be found at this link http://www.dir.ca.gov/dlse/dlseWagesAndHours.html under the heading "Exemption for computer software employee (Labor Code Section 515.5)"



vlookup on multiple criteria

This site explains how to perform vlookup on multiple criteria using an array

http://support.microsoft.com/kb/59482

I modified the forumla slightly to provide a "0" response if one of the lookup values is blank, thus preventing a N/A# error

{=IF(R5="",0,(INDEX('Rate Table'!$F$5:$F$63,MATCH(A5&D5&R5,'Rate Table'!$B$5:$B$63&'Rate Table'!$E$5:$E$63&'Rate Table'!$A$5:$A$63,0)))}

The absolute key here is that you cannot just hit ENTER, you must hit ENTER+SHIFT+CTRL in order for excel to recognize this as an array formula.  You do not enter the "squiggly" brakets, excel will do that automatically if the formula is entered correctly.

ITAR stuff

Current as of 16 May 2012

For ITAR Violations, in addition to the penalties to the company, the individual employee may be faced with: 

Willful Violations:  "Individual - A fine of up to $250,000 or imprisonment for up to ten years, or both, for each violation."
Knowing Violations:  "Individual - A fine of up to the greater of $50,000 or five times the value of the exports or imprisonment for up to five years, or both, for each violation."

Note that a single case may involve multiple violations and these penalties are for each violation.

source:  http://www.law.cornell.edu/uscode/html/uscode50a/usc_sec_50a_00002410----000-.html
TITLE 50, APPENDIX App. > EXPORT > § 2410 Violations

Kudos Received

8/30/11 Tom McCabe

Amy, sorry my flight was late and could not review. But I would not jhave changed a thing. Perfect presentation and more importantly, perfect facilitation today. Thank you for the professional competence you showed today in leading the team through your very complex and challenging ares. I think youmade a huge difference I'm making the workshop a resounding success. Got nothing but positive feedback from some newly opened eyes!

VR,

Tommy

12/10/10 Wayne Gibbons

Amy….my sincere thanks for your level of support to this office over these past five weeks.  Your daily coordination with our govt. counterparts and this office in coming to successful closure on a multitude of contract mods for both MTSS and SCETC/ATG has been no small task.     Compounding the timely execution of the contract mods was  my short tenure here and the absence of our operations officer.  In spite of these personnel handicaps, I believe we were successful in meeting govt. requirements…….due in large measure to your support.  Again, thanks!

Wayne

11/21/10 Jim Hawn

Just wanted to tell you that Amy has been doing heroic work on the ATG contract, dealing with all the changes and many frustrations as we've gone through this process.  Well deserving of some kudos from above.

Jim

Headers in Excel

Notes on how to set up "dynamic" headers in Excel so you can update the variable data within a worksheet and through running a Macro will then update in each page header.  This allows you to avoid having to update the header data individually on each worksheet.

-------

DO NOT RENAME THIS WORKSHEET TAB - IT MUST BE "Sheet4"

 

Information to include in cell A3:  "Proposal Dated: 21 November 2011"  Note, this RightHeader information MUST remain in cell A3

 

To insert this information into your header:

1) SAVE your file

2) SAVE AS a new revision (note, you must select "Save as type" as "Excel Macro-Enabled Workbook")

*These two steps are important because this "header process" is done by MACRO

There is no way to UNDO a MACRO once it has been run             

You can overright the information by changing the text in cell A3 and then rerunning the MACRO            

But, if something goes amiss and messes up all the other page formatting you will be very upset that you skipped these two steps, I promise.

3) Click on the "Developer" tab

4) Click on the "Macros" button

5) Select:

"ThisWorkbook.PrintReport"

6) Click "Run"

Note, if your Developer tab isn't showing:

Right click on any other tab (ie Page Layout)

Select "Customize the Ribbon"

On the right side of the window, make sure the check box beside "Developer" is checked           

 

The macro code should be added to "ThisWorkbook" under the "Genaral" tab as a "Print Report" and is written as follows:

 

Sub PrintReport()

    Dim wks As Worksheet

    Dim ftr

    ftr = Sheet4.Range("A3").Value

    For Each wks In Worksheets

        With wks.PageSetup

            .RightHeader = "&""Times New Roman,Italic""" & ftr

        End With

    Next wks

End Sub

JTR info which I routinely cite

In accordance with Joint Travel Regulations (JTR) Volume 2, C4553 paragraph C2, laundry expenses are separately allowable and not included as part of the M&IE allowance.  An excerpt from this section reads as follows:

NOTE: The cost for clothing laundry, dry-cleaning and pressing is a separately reimbursable expense in addition to per diem/AEA when travel is within CONUS and requires at least 4 consecutive nights TDY/PCS lodging in CONUS. The cost for laundry/dry-cleaning/ pressing clothing is not a separate reimbursable travel expense for travel OCONUS and is included as a reimbursable expense within the AEA authorized/approved for OCONUS travel.

In accordance with JTR Volume 2, C2000 paragraph B, receipts are not required for expenses under $75.  An excerpt from this section reads as follows:

A. General. A traveler must exercise the same care and regard for incurring GOV'T paid expenses as would a prudent person traveling at personal expense.

 B. Receipts. IAW DoDFMR 7000.14-R, Volume 9, a traveler must maintain records/receipts for:

1. Individual expenses of $75 or more, and

2. All lodging costs.

----------------------------------------------------------
Note: these allowances and paragraph references valid as of 11 May 2012.  Source: http://www.defensetravel.dod.mil/Docs/perdiem/JTR%28Ch1-7%29.pdf