Is there a way to delete and insert $ into a formula

[EXCELEXCEL]

I am a building a database. I need to remove $ from one part of a formula and add $ to another part of a formula.

I need to do this same action over and over again for the formula to calculate with the correct references?

How would one build this?

For example,

=PITimeDat(worksheet1!DY4,worksheet1!$A$5,"pi3","interpolated")

needs to become

=PITimeDat(worksheet1!$DY$4,worksheet1!A5,"pi3","interpolated")

The DY4 becomes fixed and A5 becomes unfixed.

Thank you in advance.

[EXCELEXCEL]

If there is a way to do this in one cell and then just repeat the action when clicking the next cell, that would also be just fine.

[EXCELEXCEL]

Without learning too much VB, I see that you can go to the developer window (excel 2010). Record a macro with "use relative references" and this will repeat the actions that you need to perform over and over.

[NoSparks]

If your database is formatted as a table, all you need do is alter the formula in the first row and Excel will automatically change the rest of the column.

If you really need to go the VBA route to copy the formula to cells,
type the formula you want into the first cell as per normal, hit Enter, then select the cell with the formula you just entered, go to the VBA environment immediate window and type

Code: Select all

? activecell.FormulaR1C1

What gets displayed will be what needs to be written to all the cells you want the formula copied to but... any quote marks will need to be doubled up.