Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit entry in one cell dependant on another cell
Hi,
My task is to find if a row contains a certain word in the X column. However the entries in column X are strings. To solve this, in a blank column (column AB), I used an IF formula to return a 'yes' or 'no' dependant on whether it found the word in column X. From this result I now need to alter the values in columns D and G if a 'Yes' is found in the new column, AB (i.e Column X contains the word I want to find.) Otherwise, if a 'no' is found in AB then nothing is changed. I realise that I could use a VLOOKUP formula but this would change the rows if a 'no' was found, which I dont want it to do. Any help would be much appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit entry in one cell dependant on another cell
Why not a simple If in D & G
=IF(AB2="Yes",changes,"") -- __________________________________ HTH Bob "Duguid1" wrote in message ... Hi, My task is to find if a row contains a certain word in the X column. However the entries in column X are strings. To solve this, in a blank column (column AB), I used an IF formula to return a 'yes' or 'no' dependant on whether it found the word in column X. From this result I now need to alter the values in columns D and G if a 'Yes' is found in the new column, AB (i.e Column X contains the word I want to find.) Otherwise, if a 'no' is found in AB then nothing is changed. I realise that I could use a VLOOKUP formula but this would change the rows if a 'no' was found, which I dont want it to do. Any help would be much appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit entry in one cell dependant on another cell
I cant use any function in the D or G columns because there is already data
in them which i dont want changed unless a 'yes' is found in column AB. Using a function clears all the data in the cell if a 'no' is found in AC. "Bob Phillips" wrote: Why not a simple If in D & G =IF(AB2="Yes",changes,"") -- __________________________________ HTH Bob "Duguid1" wrote in message ... Hi, My task is to find if a row contains a certain word in the X column. However the entries in column X are strings. To solve this, in a blank column (column AB), I used an IF formula to return a 'yes' or 'no' dependant on whether it found the word in column X. From this result I now need to alter the values in columns D and G if a 'Yes' is found in the new column, AB (i.e Column X contains the word I want to find.) Otherwise, if a 'no' is found in AB then nothing is changed. I realise that I could use a VLOOKUP formula but this would change the rows if a 'no' was found, which I dont want it to do. Any help would be much appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit entry in one cell dependant on another cell
Then it will have to be VBA. Up for that?
-- __________________________________ HTH Bob "Duguid1" wrote in message ... I cant use any function in the D or G columns because there is already data in them which i dont want changed unless a 'yes' is found in column AB. Using a function clears all the data in the cell if a 'no' is found in AC. "Bob Phillips" wrote: Why not a simple If in D & G =IF(AB2="Yes",changes,"") -- __________________________________ HTH Bob "Duguid1" wrote in message ... Hi, My task is to find if a row contains a certain word in the X column. However the entries in column X are strings. To solve this, in a blank column (column AB), I used an IF formula to return a 'yes' or 'no' dependant on whether it found the word in column X. From this result I now need to alter the values in columns D and G if a 'Yes' is found in the new column, AB (i.e Column X contains the word I want to find.) Otherwise, if a 'no' is found in AB then nothing is changed. I realise that I could use a VLOOKUP formula but this would change the rows if a 'no' was found, which I dont want it to do. Any help would be much appreciated |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit entry in one cell dependant on another cell
yeh, I thought it may have to be done with VBA but my knowledge of writing
codes isnt the best.....any suggestions? "Bob Phillips" wrote: Then it will have to be VBA. Up for that? -- __________________________________ HTH Bob "Duguid1" wrote in message ... I cant use any function in the D or G columns because there is already data in them which i dont want changed unless a 'yes' is found in column AB. Using a function clears all the data in the cell if a 'no' is found in AC. "Bob Phillips" wrote: Why not a simple If in D & G =IF(AB2="Yes",changes,"") -- __________________________________ HTH Bob "Duguid1" wrote in message ... Hi, My task is to find if a row contains a certain word in the X column. However the entries in column X are strings. To solve this, in a blank column (column AB), I used an IF formula to return a 'yes' or 'no' dependant on whether it found the word in column X. From this result I now need to alter the values in columns D and G if a 'Yes' is found in the new column, AB (i.e Column X contains the word I want to find.) Otherwise, if a 'no' is found in AB then nothing is changed. I realise that I could use a VLOOKUP formula but this would change the rows if a 'no' was found, which I dont want it to do. Any help would be much appreciated |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit entry in one cell dependant on another cell
Private Sub Worksheet_Calculate() Dim LastRow As Long Dim cell As Range With Me LastRow = .Cells(.Rows.Count, "AB").End(xlUp).Row For Each cell In .Range("AB1").Resize(LastRow) If cell.Value = "Yes" Then .Cells(cell.Row, "D").Value = "whatever" .Cells(cell.Row, "G").Value = "whatever else" End If Next cell End With End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "Duguid1" wrote in message ... yeh, I thought it may have to be done with VBA but my knowledge of writing codes isnt the best.....any suggestions? "Bob Phillips" wrote: Then it will have to be VBA. Up for that? -- __________________________________ HTH Bob "Duguid1" wrote in message ... I cant use any function in the D or G columns because there is already data in them which i dont want changed unless a 'yes' is found in column AB. Using a function clears all the data in the cell if a 'no' is found in AC. "Bob Phillips" wrote: Why not a simple If in D & G =IF(AB2="Yes",changes,"") -- __________________________________ HTH Bob "Duguid1" wrote in message ... Hi, My task is to find if a row contains a certain word in the X column. However the entries in column X are strings. To solve this, in a blank column (column AB), I used an IF formula to return a 'yes' or 'no' dependant on whether it found the word in column X. From this result I now need to alter the values in columns D and G if a 'Yes' is found in the new column, AB (i.e Column X contains the word I want to find.) Otherwise, if a 'no' is found in AB then nothing is changed. I realise that I could use a VLOOKUP formula but this would change the rows if a 'no' was found, which I dont want it to do. Any help would be much appreciated |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit entry in one cell dependant on another cell
bob, that is brilliant, thankyou!
Elaborating on that...i may have to search for more than one word in Column X and change values in columns D& G dependant on which word is present. The way im doing it at the moment is to insert a new column to display 'yes' or 'no' for each word, I can then change the values in your code to whatever I ant printed in D & G. Is there a quicker way of doing this? maybe using a text box to enter the word I want to search into my formula? "Bob Phillips" wrote: Private Sub Worksheet_Calculate() Dim LastRow As Long Dim cell As Range With Me LastRow = .Cells(.Rows.Count, "AB").End(xlUp).Row For Each cell In .Range("AB1").Resize(LastRow) If cell.Value = "Yes" Then .Cells(cell.Row, "D").Value = "whatever" .Cells(cell.Row, "G").Value = "whatever else" End If Next cell End With End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- __________________________________ HTH Bob "Duguid1" wrote in message ... yeh, I thought it may have to be done with VBA but my knowledge of writing codes isnt the best.....any suggestions? "Bob Phillips" wrote: Then it will have to be VBA. Up for that? -- __________________________________ HTH Bob "Duguid1" wrote in message ... I cant use any function in the D or G columns because there is already data in them which i dont want changed unless a 'yes' is found in column AB. Using a function clears all the data in the cell if a 'no' is found in AC. "Bob Phillips" wrote: Why not a simple If in D & G =IF(AB2="Yes",changes,"") -- __________________________________ HTH Bob "Duguid1" wrote in message ... Hi, My task is to find if a row contains a certain word in the X column. However the entries in column X are strings. To solve this, in a blank column (column AB), I used an IF formula to return a 'yes' or 'no' dependant on whether it found the word in column X. From this result I now need to alter the values in columns D and G if a 'Yes' is found in the new column, AB (i.e Column X contains the word I want to find.) Otherwise, if a 'no' is found in AB then nothing is changed. I realise that I could use a VLOOKUP formula but this would change the rows if a 'no' was found, which I dont want it to do. Any help would be much appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show or blank out a cell dependant on an selection in another cell | Excel Discussion (Misc queries) | |||
restricting entry into a cell based on entry to a previous cell | New Users to Excel | |||
Row Manipulation / duplicate dependant on cell value... and perform calculation on another cell... | Excel Programming | |||
How do I auto-fill a cell dependant on the value of another cell? | Excel Worksheet Functions | |||
vba to edit outlook contacts from cell entry | Excel Programming |