Excel how to change a value automatically
Solved/Closed
Rjn239

Oct 4, 2010 at 09:14 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022  Oct 11, 2010 at 09:05 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022  Oct 11, 2010 at 09:05 AM
Related:
 Excel how to change a value automatically
 Excel how to change date format dd.mm.yyyy  Guide
 Excel author name change  Guide
 Excel formula to change text color based on value ✓  Forum  Excel
 Excel copy data to another sheet automatically  Guide
 Excel formula to change cell color based on text without conditional formatting  Forum  Excel
3 replies
TrowaD
Posts
2913
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 21, 2022
541
Oct 5, 2010 at 10:29 AM
Oct 5, 2010 at 10:29 AM
Hi Rjn239,
So sheet 1 could look like this:
Henkie  06123456789
Jopie  06987456321
Kees  06789654123
Now you need to place the names behind the numbers or add a extra set of names behind the numbers like:
06123456789  Henkie
06987456321  Jopie
06789654123  Kees
or
Henkie  06123456789  Henkie
Jopie  06987456321  Jopie
Kees  06789654123  Kees
Now you have got things set up for the VLOOKUP function.
Create an extra column on sheet 2 and use VLOOKUP to retreive the name corresponding to the number.
Not exactly what you asked for, but the easiest way to get it done imo.
For your second question you could use an array formula.
At the end of sheet 2 you could enter the names again with a formula in the cell next to it which could look like this:
=SUM(IF(D2:D50="Henkie",1,0))
An array formula needs to be confirmed by hitting Ctrl+Shift+Enter.
Is this helpfull to you?
Best regards,
Trowa
So sheet 1 could look like this:
Henkie  06123456789
Jopie  06987456321
Kees  06789654123
Now you need to place the names behind the numbers or add a extra set of names behind the numbers like:
06123456789  Henkie
06987456321  Jopie
06789654123  Kees
or
Henkie  06123456789  Henkie
Jopie  06987456321  Jopie
Kees  06789654123  Kees
Now you have got things set up for the VLOOKUP function.
Create an extra column on sheet 2 and use VLOOKUP to retreive the name corresponding to the number.
Not exactly what you asked for, but the easiest way to get it done imo.
For your second question you could use an array formula.
At the end of sheet 2 you could enter the names again with a formula in the cell next to it which could look like this:
=SUM(IF(D2:D50="Henkie",1,0))
An array formula needs to be confirmed by hitting Ctrl+Shift+Enter.
Is this helpfull to you?
Best regards,
Trowa
TrowaD
Posts
2913
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 21, 2022
541
Oct 7, 2010 at 10:03 AM
Oct 7, 2010 at 10:03 AM
Hi Ryan,
lookup_value = The value (phone number) you want to lookup.
table_array = The table of content containing the value.
col_index_num = The column number which contains the value (name) you want to retrieve.
range_lookup = [optional] false: find the exact value. True or empty: find the closest match.
Now you might understand why I wanted you to rearrange you data like:
06123456789  Henkie
06987456321  Jopie
06789654123  Kees
or
Henkie  06123456789  Henkie
Jopie  06987456321  Jopie
Kees  06789654123  Kees
Since the data you want to lookup needs to be in front of the data you want to retrieve.
So on your second sheet you have a list (eg. column D) of phone numbers. Insert an empty column next to them.
Then your formula would look like this:
=VLOOKUP(D1,Sheet1!$A$1:$B$3,2,FALSE)
So D1 contains the phone number you want to look up.
Sheet1!$A$1:$B$3 contains the list of phone numbers and names.
The number 2 tells excel to retrieve the data from the second column.
FALSE: find exact match.
The $ signs makes the array absolute, which doesn't change the array when dragged down.
Hopefully this makes sense.
Feel free to ask if something is still unclear.
Best regards,
Trowa
lookup_value = The value (phone number) you want to lookup.
table_array = The table of content containing the value.
col_index_num = The column number which contains the value (name) you want to retrieve.
range_lookup = [optional] false: find the exact value. True or empty: find the closest match.
Now you might understand why I wanted you to rearrange you data like:
06123456789  Henkie
06987456321  Jopie
06789654123  Kees
or
Henkie  06123456789  Henkie
Jopie  06987456321  Jopie
Kees  06789654123  Kees
Since the data you want to lookup needs to be in front of the data you want to retrieve.
So on your second sheet you have a list (eg. column D) of phone numbers. Insert an empty column next to them.
Then your formula would look like this:
=VLOOKUP(D1,Sheet1!$A$1:$B$3,2,FALSE)
So D1 contains the phone number you want to look up.
Sheet1!$A$1:$B$3 contains the list of phone numbers and names.
The number 2 tells excel to retrieve the data from the second column.
FALSE: find exact match.
The $ signs makes the array absolute, which doesn't change the array when dragged down.
Hopefully this makes sense.
Feel free to ask if something is still unclear.
Best regards,
Trowa
Just a quick query in regards to the formula you gave ryan:
=SUM(IF(D2:D50="Henkie",1,0))
I tried this formula with my own work, when I put this formula into cell A1 for example it all works lovely, but when I drag this down to put the same formula into A2,3,4, and 5 etc the D2:D50 range increases everytime, for example:
In cell A1 the primary cell the formula is =SUM(IF(D2:D50="Henkie",1,0))
In cell A2 =SUM(IF(D3:D51="Henkie",1,0))
In cell A3 =SUM(IF(D4:D52="Henkie",1,0))
In cell A4 =SUM(IF(D5:D53="Henkie",1,0))
How would I stop the range increasing? so in cell A2,3,4,5 it would still read =SUM(IF(D2:D50="Henkie",1,0)) just by dragging it down? Thanks, John.
=SUM(IF(D2:D50="Henkie",1,0))
I tried this formula with my own work, when I put this formula into cell A1 for example it all works lovely, but when I drag this down to put the same formula into A2,3,4, and 5 etc the D2:D50 range increases everytime, for example:
In cell A1 the primary cell the formula is =SUM(IF(D2:D50="Henkie",1,0))
In cell A2 =SUM(IF(D3:D51="Henkie",1,0))
In cell A3 =SUM(IF(D4:D52="Henkie",1,0))
In cell A4 =SUM(IF(D5:D53="Henkie",1,0))
How would I stop the range increasing? so in cell A2,3,4,5 it would still read =SUM(IF(D2:D50="Henkie",1,0)) just by dragging it down? Thanks, John.
TrowaD
Posts
2913
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 21, 2022
541
Oct 11, 2010 at 09:05 AM
Oct 11, 2010 at 09:05 AM
Hi John,
You will need to make your cell reference absolute by adding a $ symbol.
You can do this to the column and/or row by manually adding a $ symbol or after selecting the range and hitting F4 a couple of times untill the desired result is visible.
In your case you want your row to be absolute so the formula would look like:
=SUM(IF(D$2:D$50="Henkie",1,0))
Best regards,
Trowa
You will need to make your cell reference absolute by adding a $ symbol.
You can do this to the column and/or row by manually adding a $ symbol or after selecting the range and hitting F4 a couple of times untill the desired result is visible.
In your case you want your row to be absolute so the formula would look like:
=SUM(IF(D$2:D$50="Henkie",1,0))
Best regards,
Trowa
Oct 5, 2010 at 11:01 AM
I am going to give the VLookup formula ago, but was wondering if you could give me advice on using the formula.
On sheet 1 I have set it up as follows (eg):
Henkie  06123456789
Jopie  06987456321
Kees  06789654123
Now on sheet 2 I have clicked on the Vlookup formula and it pops up with a box for me to fill in (excel 2007) what would I enter in the following:
lookup_value
table_array
col_index_num
range_lookup
and the =SUM(IF(D2:D50="Henkie",1,0)) formula worked perfect thanks again :)
Ryan.