Posts by 6StringJazzer

    If I understand correctly, you are copying a sheet from a template file called Formulas into a new workbook. The sheet has formulas that reference another sheet in Formulas. After the copy, instead of referencing the sheet in the new workbook, they have an external references back to the Formulas workbook. You want those formulas to reference the corresponding sheet in the new workbook instead.


    I have not found a way to do this during the copy/paste sheet operation, but since you are doing this with a macro you can add code to the macro to clean up after the sheet is pasted.


    Code
    1. Worksheet("New Sheet").Cells.Replace _
    2. What:="[Formulas.xlsx]!", _
    3. Replacement:="", _
    4. LookAt:=xlPart, _
    5. FormulaVersion:=xlReplaceFormula2

    Replace the red parts with the actual names.

    Re: Indirectly referring to named column range within an array formula? Can this be d


    The expression INDIRECT("$A$3") returns a string, not a range. It returns the string "AM1_BP_Late_Qual", not the named range AM1_BP_Late_Qual. There is a subtle difference. Let's take a piece of your formula to illustrate. It's like the difference between


    [INDENT]IF(AM1_BP_Late_Qual="*",
    [/INDENT]

    and


    [INDENT]IF("AM1_BP_Late_Qual"="*",
    [/INDENT]

    Clearly the second one doesn't work, but that's what you're getting with INDIRECT("$A$3").


    To return a range you need to use INDIRECT on the range name that the first INDIRECT returns. It's a little hard to explain in a forum post, but try this formula:


    {=IFERROR(INDEX(Data,SMALL(IF(INDIRECT(INDIRECT("$A$3"))="*",ROW(INDIRECT(INDIRECT("$A$3")))),ROW(1:1))-9,1),"")}

    Re: Help to change file path


    Make this change. The string you provided is a full qualification for a cell that would appear in a formula, but you just want the actual Windows file path for this use.



    Code
    1. FilePath = "D:\Test\Data\"

    Re: locking and unlocking a row of cells


    I am assuming that by "lock" you mean format them as locked, the same as if you went to Format, Lock. Locking is only effective when the worksheet has been protected. However, if the sheet is already protected you must unlock it first.


    The following code must go in the module for the sheet containing the cells to lock. If the sheet has a password, you must add it to the calls to Unprotect and Protect.


    [VBA]
    Private Sub test()



    Dim ReProtect As Boolean

    If ProtectContents Then
    ReProtect = True
    Unprotect
    End If

    If Range("E8") = "" Then
    Range("E27:AA27").Locked = True
    End If

    If ReProtect Then
    Protect
    End If



    End Sub


    [/VBA]

    Re: Looking to set Print Area to 2 pages and set the Page Break at a specific row


    There is no problem with your code. The dotted-line page break is automatic based on how many lines of data will fit on one page. The page break you are trying to set is after the page has filled and moved to the next one. Normally I would say to use narrower margins to fit more on a page, but your margins are already very small at 0.1". You are simply trying to get more on a page than will physically fit.

    Re: Increment number in image name such as DSCI3000.jpg


    My suggestion would be to put the first number in a cell (let's say B1). In your case it will be 3001.


    Then in A1, use this formula and copy down as many rows as desired:


    ="DSCI" & TEXT($B$1 + ROW() - 1,"0000")


    The "- 1" subtracts the row number of the first row, so if you start in a different row than row 1, change that number.


    The use of TEXT forces the number to be 4 digits. If you start with 3001 this won't be an issue, but the same formula will work even if you start at 0.