Batch update cell values
 

I need to batch update all cell values in a sheet over the value of 200 to
become 200.

How can I accomplish this?

Batch update cell values
 

Use a helper sheet ..

Assuming source data in Sheet1, within A1:D20 (say)

In Sheet2,

Put in A1:
=IF(Sheet1!A1="","",IF(Sheet1!A1200,200,Sheet1!A1 ))
Copy A1 across to D1, fill down to D20
Then copy A1:D20, & paste and overwrite A1:D20 in Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Moshe Rosenberg" wrote:
I need to batch update all cell values in a sheet over the value of 200 to
become 200.

How can I accomplish this?

Batch update cell values
 

I am not sure what to do with Sheet2. When I put that into the A1 cell, then
what? What actually updates the data?

I would be grateful if you can clarify.

Thanks!

"Max" wrote:

Use a helper sheet ..

Assuming source data in Sheet1, within A1:D20 (say)

In Sheet2,

Put in A1:
=IF(Sheet1!A1="","",IF(Sheet1!A1200,200,Sheet1!A1 ))
Copy A1 across to D1, fill down to D20
Then copy A1:D20, & paste and overwrite A1:D20 in Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Moshe Rosenberg" wrote:
I need to batch update all cell values in a sheet over the value of 200 to
become 200.

How can I accomplish this?

Batch update cell values
 

As stated in my response, I assumed your source data was in Sheet1, within
A1:D20. Sheet2 is just a helper sheet, which is used to examine Sheet1's
source data and change the values as required, using a corresponding range
filled with the formulas as given. Hope that clarifies ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Moshe Rosenberg" wrote:
I am not sure what to do with Sheet2. When I put that into the A1 cell, then
what? What actually updates the data?

I would be grateful if you can clarify.

Thanks!

Batch update cell values
 

Thanks, that helped!

"Max" wrote:

As stated in my response, I assumed your source data was in Sheet1, within
A1:D20. Sheet2 is just a helper sheet, which is used to examine Sheet1's
source data and change the values as required, using a corresponding range
filled with the formulas as given. Hope that clarifies ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Moshe Rosenberg" wrote:
I am not sure what to do with Sheet2. When I put that into the A1 cell, then
what? What actually updates the data?

I would be grateful if you can clarify.

Thanks!

Batch update cell values
 

Delighted to hear that, Moshe.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Moshe Rosenberg" wrote in
message ...
Thanks, that helped!

Batch update cell values
 

oops, " .. paste .. " in line:
Then copy A1:D20, & paste and overwrite A1:D20 in Sheet1

should read: " .. paste special as values ... "
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Batch update cell values
 

Sub Make_200()
Dim cell As Range
For Each cell In Selection
If cell.Value 200 Then
cell.Value = 200
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Tue, 20 Feb 2007 07:52:15 -0800, Moshe Rosenberg
wrote:

I need to batch update all cell values in a sheet over the value of 200 to
become 200.

How can I accomplish this?