Excel how to change a value automatically
Solved/Closed
Rjn239
-
Oct 4, 2010 at 09:14 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 11, 2010 at 09:05 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 11, 2010 at 09:05 AM
Related:
- Excel how to change a value automatically
- Transfer data from one excel worksheet to another automatically - Guide
- How to change author in excel - Guide
- How to change date format in excel - Guide
- How to change idm language - Guide
- Change computer name cmd - Guide
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
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
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
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
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
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.