| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 --- |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Values in spreadsheets do not update | Excel Worksheet Functions | |||
| How to delete in batch the same color format letters in a cell | Excel Worksheet Functions | |||
| Update of cell values after insert row? | Excel Worksheet Functions | |||
| How does one update and transfer values between cells and not the. | Excel Discussion (Misc queries) | |||
| How does one update and transfer values between cells and not the. | Excel Discussion (Misc queries) | |||
Linear Mode
