Highlight required areas of S & T and use Edit Replace:
Find !W
Replace !X
Click Replace All

U & V not quite so simple but doable with same principle:

Do two Edit Replaces:
Find (AF
Replace (AG
Click Replace All

Find (AE
Replace (AF
Click Replace All

Important to do in correct order for U & V.

Alternatively, if W is empty, copy cells from V and paste into W and then CUT from W back into V. This may have an impact elsewhere on formulas referring to V. If W is not empty insert column at W and then delete column after Copy/Cut actions.

thanks Rob for the suggestion.
but i am tired of doing all these find, replace, copy and paste. it is too time consuming as i have other worksheets to compile too. i need to save time for analysis instead of wasting time on the data entry work.

can anyone help ?

0

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Before I suggest a VBA solution, what do you mean by "formula update EXCLUDE those shaded cells within the column" ?

0

ceneiqeAuthor Commented:

"formula update EXCLUDE those shaded cells within the column" = if there are cells that are highlighted with a colour then DO NOT update the formula and this applies to only Column S, T and Vin Sheet1.

Assuming value 22 in S1, this will use the value in Sheet2!W4. Increase S1 to 23 and it will use X4.

Not knowing the scenario, I have assumed OFFSET from col A, col W is 22 cols to the right. You can make the value in S1 more relevant if so required. For example, assume sheet2 was a finance report and col W referred to period 9, therefore assume col O would be period 1. Above formula could be:

=(OFFSET(Sheet2!$O4,0,$S$1-1,1,1)*$A4)/1000 and value of S1 would be 9 to represent period 9 reporting.

Bizarre coincidence that I chose col O when I then notice that the sum on sheet2 starts at col O.

To adjust the SUM on sheet2 it would be:

=SUM(OFFSET($O4,0,0,1,$J$1)) where J1 contains number of columns required.

See attached with one example for each of the columns S to V using OFFSET and in col J on sheet 2. One change to S1 on sheet1 will change all formulas.

This macro will update all your formulas on the two sheets:

Option ExplicitSub Fix_Formulas() Dim r As Integer Dim lngLR As Long lngLR = Cells.SpecialCells(xlLastCell).Row Sheets("Sheet1").Select For r = 4 To lngLR If Range("S" & r).Interior.Pattern = xlNone Then Range("S" & r).Formula = "=(Sheet2!X" & r & "*$A" & r & ")/1000" Range("T" & r).Formula = "=((AF" & r & "-Sheet2!X" & r & ")*$A" & r & ")/1000" Range("U" & r).Formula = "=AG" & r & "/AF" & r & "-1" Range("V" & r).Formula = "=((AG" & r & "-AF" & r & ")*$A" & r & ")/1000" 'or, using relative referencing 'Cells(r, 19).FormulaR1C1 = "=(Sheet2!RC[5]*$RC1)/1000" 'Cells(r, 20).FormulaR1C1 = "=((RC[12]-Sheet2!RC[5])*RC1)/1000" 'Cells(r, 21).FormulaR1C1 = "=RC[12]/RC[11]-1" 'Cells(r, 22).FormulaR1C1 = "=((=RC[12]-RC[11])*RC1)/1000" End If Next r Sheets("Sheet2").Select For r = 4 To 130 Range("J" & r).Formula = "=SUM(O" & r & ":X" & r & ")" 'or, using relative referencing 'Cells(4, 10).FormulaR1C1 = "=SUM(RC[5]:RC[14]" Next rEnd Sub

On Sheet1, it will update all the formulas where there is no shading (.Interior.Pattern = xlNone). On Sheet2, it updates all cells from row 4 to 130. NOTE: It is only checking for shading in column S; the assumption being that if S is shaded, the entire row is also. Having not seen your workbook, this is where I started.

There are alternate blocks of code that are commented out that show the same formulas, but using R1C1 method to create them. They are more concise, but harder to interpret. However, I included them for your reference.

i mentioned "For column U, update whole of the column. "
which means for column U, just update the formula regardless whether the cell is highlighted or not.

I'm sorry that I missed that instruction about column U. Here is the updated code:

Option ExplicitSub Fix_Formulas() Dim r As Integer Dim lngLR As Long lngLR = Cells.SpecialCells(xlLastCell).Row Sheets("Sheet1").Select For r = 4 To lngLR If Range("S" & r).Interior.Pattern = xlNone Then Range("S" & r).Formula = "=(Sheet2!X" & r & "*$A" & r & ")/1000" End If If Range("T" & r).Interior.Pattern = xlNone Then Range("T" & r).Formula = "=((AF" & r & "-Sheet2!X" & r & ")*$A" & r & ")/1000" End If If Range("V" & r).Interior.Pattern = xlNone Then Range("V" & r).Formula = "=((AG" & r & "-AF" & r & ")*$A" & r & ")/1000" End If Range("U" & r).Formula = "=AG" & r & "/AF" & r & "-1" End If Next r Sheets("Sheet2").Select For r = 4 To 130 Range("J" & r).Formula = "=SUM(O" & r & ":X" & r & ")" Next rEnd Sub

Option ExplicitSub Fix_Formulas() Dim r As Integer Dim lngLR As Long lngLR = Cells.SpecialCells(xlLastCell).Row Sheets("Sheet1").Select For r = 4 To lngLR If Range("S" & r).Interior.Pattern = xlNone Then Range("S" & r).Formula = "=(Sheet2!X" & r & "*$A" & r & ")/1000" End If If Range("T" & r).Interior.Pattern = xlNone Then Range("T" & r).Formula = "=((AF" & r & "-Sheet2!X" & r & ")*$A" & r & ")/1000" End If If Range("V" & r).Interior.Pattern = xlNone Then Range("V" & r).Formula = "=((AG" & r & "-AF" & r & ")*$A" & r & ")/1000" End If Range("U" & r).Formula = "=AG" & r & "/AF" & r & "-1" Next r Sheets("Sheet2").Select For r = 4 To 130 Range("J" & r).Formula = "=SUM(O" & r & ":X" & r & ")" Next rEnd Sub

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Highlight required areas of S & T and use Edit Replace:

Find !W

Replace !X

Click Replace All

U & V not quite so simple but doable with same principle:

Do two Edit Replaces:

Find (AF

Replace (AG

Click Replace All

Find (AE

Replace (AF

Click Replace All

Important to do in correct order for U & V.

Alternatively, if W is empty, copy cells from V and paste into W and then CUT from W back into V. This may have an impact elsewhere on formulas referring to V. If W is not empty insert column at W and then delete column after Copy/Cut actions.

Thanks

Rob H