Copy/paste Formulas Only

Re: Copy/paste Formulas Only

Have you tried Copy > PasteSpecial > Formulas?

Re: Copy/paste Formulas Only

The I understand you correctly, the blank cells in any given column should have forecast formulas, and the hard values are actuals, and you want to enter the forecast formulas into the blank cells only.

If that's correct, then select the range of interest in a given column (say, I), and then do Edit | GoTo | Special, Blanks, selecting all the blank cells in the range. Note the first cell selected in the column, and type in the formula with appropriate cell references for that cell. Don't hit Enter, do Cntl Enter. Now all the blank cells have the same formula with adjusted references.

Then do the same for the other formulas in the other columns.

If all the formulas are the same (maybe using some combination of mixed and absolute cell references), then you can enter them all at once by selecting the entire area and following the procedure above.

Re: Copy/paste Formulas Only

Quote from markc

Thanks Roy -

That picks up hard coded values and pastes them over the current value
I need to be able to skip the hard inputs

Thanks
-marc

PasteSpecial > Values copies values

PasteSpecial > Formlas copies Formulas

Re: Copy/paste Formulas Only

I don't know if I am reading this right, but I think I can help you get where you are heading. You want to be able to update just cells that are formulas, right? If so, here is a UDF that I wrote to mark them as formula or value. Create a column for use only as a sorter. Then put the formulaOrNot UDF in it referencing the column you want to update. I sort by the column that I use the UDF in. That allows me to group ones that are formulas with ones that are formulas for the copy / paste update.

Function formulaOrNot(rangeInQuestion As Range)
    
    For Each one In rangeInQuestion
        i = i + 1
    Next
    
    For Each cell In rangeInQuestion
        If cell.Formula Like "=*" Then j = j + 1
    Next
    
    If i = j Then formulaOrNot = "F" Else formulaOrNot = "N"
    
End Function

It can take either a cell reference or a range reference.

HTH's

Re: Copy/paste Formulas Only

Open this workbook to see if it is what you are looking for. Once you sort ascending in the sorter column you can copy and paste your formula update to to just the ones with an "F" beside them.

Re: Copy/paste Formulas Only

As far as your absolute reference question goes you just need a sequence column to be be able to get them back in the original order. Check out the new column.