| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Skip cell if X nonexistent, populate next cell
OK here's my dilemma; hope I explain myself clearly as I'm new at
this. I have a worksheet that pulls the names and IDs of casino players enrolled in different groups from a database. Every person in the group is assigned a player ID. Not every person plays, however. I'm compiling a report that shows actual and theoretical win for each player. Table X stores player names and IDs, table Y stores player statistics. I calculate actual and theoretical win from table Y, set up a pivot table, and link 2 other columns to the pivot table to display actual and theoretical win. Problem is, only players who actually play have any records in table Y. The bosses want to see ALL persons enrolled in group X, whether they played or not. With my limited knowledge of Excel, I have 2 options: A. I modify the report to show ONLY those persons in the group who actually played. B. I show all persons in the group, but the player name/IDs and actual/ theoretical win figures will no longer match up. e.g group 234 includes the following, modified to show only persons who played: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Everyone's play is correctly attributed to him/her, but when I modify the report to include persons who did NOT play, Mr. Jolly Green Giant now appears on the list, and has Big Baby's figures incorrectly attributed to him, Big Baby in turn gets Moby Dick's data, and so on and so on. Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $1.20 $3.75 53087 Baby Big $28.10 $83.75 53088 Dick Moby How can I extract actual and theoretical win from the pivot table (which contains ONLY those players who played, along with their stats), match it up with the corresponding player IDs and names on the report (which contains ALL player IDs/names whether they played or not) and instruct Excel to skip or better yet populate with '$0.00' those cells that belong to players who did NOT play? As in Mr. Jolly Green Giant would now be included on the report, but would have '$0.00' in his 'Theo Win' and 'Actual Win' columns: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $0.00 $0.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Really hope I'm being clear enough about what I need, as I said, I'm new to this, but trying to be precise and provide all necesssary information. Any guidance is deeply appreciated. Thanks, |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip cell if X nonexistent, populate next cell
It is unclear how you are linking to addtiional columns to the pivot table,
but if you are getting the wrong results, you apparently are not doing it correctly. Maybe you are using a vlookup and not going for an exact match? -- Regards, Tom Ogilvy "prodeji" wrote: OK here's my dilemma; hope I explain myself clearly as I'm new at this. I have a worksheet that pulls the names and IDs of casino players enrolled in different groups from a database. Every person in the group is assigned a player ID. Not every person plays, however. I'm compiling a report that shows actual and theoretical win for each player. Table X stores player names and IDs, table Y stores player statistics. I calculate actual and theoretical win from table Y, set up a pivot table, and link 2 other columns to the pivot table to display actual and theoretical win. Problem is, only players who actually play have any records in table Y. The bosses want to see ALL persons enrolled in group X, whether they played or not. With my limited knowledge of Excel, I have 2 options: A. I modify the report to show ONLY those persons in the group who actually played. B. I show all persons in the group, but the player name/IDs and actual/ theoretical win figures will no longer match up. e.g group 234 includes the following, modified to show only persons who played: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Everyone's play is correctly attributed to him/her, but when I modify the report to include persons who did NOT play, Mr. Jolly Green Giant now appears on the list, and has Big Baby's figures incorrectly attributed to him, Big Baby in turn gets Moby Dick's data, and so on and so on. Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $1.20 $3.75 53087 Baby Big $28.10 $83.75 53088 Dick Moby How can I extract actual and theoretical win from the pivot table (which contains ONLY those players who played, along with their stats), match it up with the corresponding player IDs and names on the report (which contains ALL player IDs/names whether they played or not) and instruct Excel to skip or better yet populate with '$0.00' those cells that belong to players who did NOT play? As in Mr. Jolly Green Giant would now be included on the report, but would have '$0.00' in his 'Theo Win' and 'Actual Win' columns: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $0.00 $0.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Really hope I'm being clear enough about what I need, as I said, I'm new to this, but trying to be precise and provide all necesssary information. Any guidance is deeply appreciated. Thanks, |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip cell if X nonexistent, populate next cell
On Feb 1, 12:18 pm, Tom Ogilvy
wrote: It is unclear how you are linking to addtiional columns to the pivot table, but if you are getting the wrong results, you apparently are not doing it correctly. Maybe you are using a vlookup and not going for an exact match? -- Regards, Tom Ogilvy "prodeji" wrote: OK here's my dilemma; hope I explain myself clearly as I'm new at this. I have a worksheet that pulls the names and IDs of casino players enrolled in different groups from a database. Every person in the group is assigned a player ID. Not every person plays, however. I'm compiling a report that shows actual and theoretical win for each player. Table X stores player names and IDs, table Y stores player statistics. I calculate actual and theoretical win from table Y, set up a pivot table, and link 2 other columns to the pivot table to display actual and theoretical win. Problem is, only players who actually play have any records in table Y. The bosses want to see ALL persons enrolled in group X, whether they played or not. With my limited knowledge of Excel, I have 2 options: A. I modify the report to show ONLY those persons in the group who actually played. B. I show all persons in the group, but the player name/IDs and actual/ theoretical win figures will no longer match up. e.g group 234 includes the following, modified to show only persons who played: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Everyone's play is correctly attributed to him/her, but when I modify the report to include persons who did NOT play, Mr. Jolly Green Giant now appears on the list, and has Big Baby's figures incorrectly attributed to him, Big Baby in turn gets Moby Dick's data, and so on and so on. Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $1.20 $3.75 53087 Baby Big $28.10 $83.75 53088 Dick Moby How can I extract actual and theoretical win from the pivot table (which contains ONLY those players who played, along with their stats), match it up with the corresponding player IDs and names on the report (which contains ALL player IDs/names whether they played or not) and instruct Excel to skip or better yet populate with '$0.00' those cells that belong to players who did NOT play? As in Mr. Jolly Green Giant would now be included on the report, but would have '$0.00' in his 'Theo Win' and 'Actual Win' columns: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $0.00 $0.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Really hope I'm being clear enough about what I need, as I said, I'm new to this, but trying to be precise and provide all necesssary information. Any guidance is deeply appreciated. Thanks,- Hide quoted text - - Show quoted text - Apologies, I guess I was indeed not being clear. Umm... Basically I have a column X that has say 25 rows. Elsewhere on the spreadsheet I have column X appearing again paired with column Y and containing only those rows that have corresponding data in column Y. This reducies column X to say 20 rows. I now need to match up the values from column Y with the corresponding values in the original column X, inserting '$0.00' or something similar in the rows where the original column X does not have a corresponding value in column Y. Hope this is clearer, thanks, |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip cell if X nonexistent, populate next cell
Assuming the first column X values are starting in A1 and the second are
starting in M1, amounts in N1 (down to row 20), as an example, =if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False)) entered in B1, then drag fill down 25 rows. -- Regards, Tom Ogilvy "prodeji" wrote: On Feb 1, 12:18 pm, Tom Ogilvy wrote: It is unclear how you are linking to addtiional columns to the pivot table, but if you are getting the wrong results, you apparently are not doing it correctly. Maybe you are using a vlookup and not going for an exact match? -- Regards, Tom Ogilvy "prodeji" wrote: OK here's my dilemma; hope I explain myself clearly as I'm new at this. I have a worksheet that pulls the names and IDs of casino players enrolled in different groups from a database. Every person in the group is assigned a player ID. Not every person plays, however. I'm compiling a report that shows actual and theoretical win for each player. Table X stores player names and IDs, table Y stores player statistics. I calculate actual and theoretical win from table Y, set up a pivot table, and link 2 other columns to the pivot table to display actual and theoretical win. Problem is, only players who actually play have any records in table Y. The bosses want to see ALL persons enrolled in group X, whether they played or not. With my limited knowledge of Excel, I have 2 options: A. I modify the report to show ONLY those persons in the group who actually played. B. I show all persons in the group, but the player name/IDs and actual/ theoretical win figures will no longer match up. e.g group 234 includes the following, modified to show only persons who played: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Everyone's play is correctly attributed to him/her, but when I modify the report to include persons who did NOT play, Mr. Jolly Green Giant now appears on the list, and has Big Baby's figures incorrectly attributed to him, Big Baby in turn gets Moby Dick's data, and so on and so on. Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $1.20 $3.75 53087 Baby Big $28.10 $83.75 53088 Dick Moby How can I extract actual and theoretical win from the pivot table (which contains ONLY those players who played, along with their stats), match it up with the corresponding player IDs and names on the report (which contains ALL player IDs/names whether they played or not) and instruct Excel to skip or better yet populate with '$0.00' those cells that belong to players who did NOT play? As in Mr. Jolly Green Giant would now be included on the report, but would have '$0.00' in his 'Theo Win' and 'Actual Win' columns: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $0.00 $0.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Really hope I'm being clear enough about what I need, as I said, I'm new to this, but trying to be precise and provide all necesssary information. Any guidance is deeply appreciated. Thanks,- Hide quoted text - - Show quoted text - Apologies, I guess I was indeed not being clear. Umm... Basically I have a column X that has say 25 rows. Elsewhere on the spreadsheet I have column X appearing again paired with column Y and containing only those rows that have corresponding data in column Y. This reducies column X to say 20 rows. I now need to match up the values from column Y with the corresponding values in the original column X, inserting '$0.00' or something similar in the rows where the original column X does not have a corresponding value in column Y. Hope this is clearer, thanks, |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip cell if X nonexistent, populate next cell
On Feb 1, 1:27 pm, Tom Ogilvy
wrote: Assuming the first column X values are starting in A1 and the second are starting in M1, amounts in N1 (down to row 20), as an example, =if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False)) entered in B1, then drag fill down 25 rows. -- Regards, Tom Ogilvy "prodeji" wrote: On Feb 1, 12:18 pm, Tom Ogilvy wrote: It is unclear how you are linking to addtiional columns to the pivot table, but if you are getting the wrong results, you apparently are not doing it correctly. Maybe you are using a vlookup and not going for an exact match? -- Regards, Tom Ogilvy "prodeji" wrote: OK here's my dilemma; hope I explain myself clearly as I'm new at this. I have a worksheet that pulls the names and IDs of casino players enrolled in different groups from a database. Every person in the group is assigned a player ID. Not every person plays, however. I'm compiling a report that shows actual and theoretical win for each player. Table X stores player names and IDs, table Y stores player statistics. I calculate actual and theoretical win from table Y, set up a pivot table, and link 2 other columns to the pivot table to display actual and theoretical win. Problem is, only players who actually play have any records in table Y. The bosses want to see ALL persons enrolled in group X, whether they played or not. With my limited knowledge of Excel, I have 2 options: A. I modify the report to show ONLY those persons in the group who actually played. B. I show all persons in the group, but the player name/IDs and actual/ theoretical win figures will no longer match up. e.g group 234 includes the following, modified to show only persons who played: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Everyone's play is correctly attributed to him/her, but when I modify the report to include persons who did NOT play, Mr. Jolly Green Giant now appears on the list, and has Big Baby's figures incorrectly attributed to him, Big Baby in turn gets Moby Dick's data, and so on and so on. Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $1.20 $3.75 53087 Baby Big $28.10 $83.75 53088 Dick Moby How can I extract actual and theoretical win from the pivot table (which contains ONLY those players who played, along with their stats), match it up with the corresponding player IDs and names on the report (which contains ALL player IDs/names whether they played or not) and instruct Excel to skip or better yet populate with '$0.00' those cells that belong to players who did NOT play? As in Mr. Jolly Green Giant would now be included on the report, but would have '$0.00' in his 'Theo Win' and 'Actual Win' columns: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $0.00 $0.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Really hope I'm being clear enough about what I need, as I said, I'm new to this, but trying to be precise and provide all necesssary information. Any guidance is deeply appreciated. Thanks,- Hide quoted text - - Show quoted text - Apologies, I guess I was indeed not being clear. Umm... Basically I have a column X that has say 25 rows. Elsewhere on the spreadsheet I have column X appearing again paired with column Y and containing only those rows that have corresponding data in column Y. This reducies column X to say 20 rows. I now need to match up the values from column Y with the corresponding values in the original column X, inserting '$0.00' or something similar in the rows where the original column X does not have a corresponding value in column Y. Hope this is clearer, thanks,- Hide quoted text - - Show quoted text - Hi again Tom, I took the code you suggested and modified it to reflect cell values on my worksheet (I'll put them side by side for comparison): =if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False)) =if(iserror(match($L7,$H$3:$I$300,0),0,Vlookup($L7 ,$H$3:$I $300,2,False)) I don't know if I have some step/procedure done incorrectly or omitted altogether but I got an 'The formula you typed contains an error' message both when using my modified version and the original. Any thoughts? Thanks, |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip cell if X nonexistent, populate next cell
=if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False))
doesn't close the iserror function. =if(iserror(match($A1,$M$1:$N$20,0)),0,Vlookup($A1 ,$m$1:$N$20,2,False)) -- Regards, Tom Ogilvy "prodeji" wrote in message oups.com... On Feb 1, 1:27 pm, Tom Ogilvy wrote: Assuming the first column X values are starting in A1 and the second are starting in M1, amounts in N1 (down to row 20), as an example, =if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False)) entered in B1, then drag fill down 25 rows. -- Regards, Tom Ogilvy "prodeji" wrote: On Feb 1, 12:18 pm, Tom Ogilvy wrote: It is unclear how you are linking to addtiional columns to the pivot table, but if you are getting the wrong results, you apparently are not doing it correctly. Maybe you are using a vlookup and not going for an exact match? -- Regards, Tom Ogilvy "prodeji" wrote: OK here's my dilemma; hope I explain myself clearly as I'm new at this. I have a worksheet that pulls the names and IDs of casino players enrolled in different groups from a database. Every person in the group is assigned a player ID. Not every person plays, however. I'm compiling a report that shows actual and theoretical win for each player. Table X stores player names and IDs, table Y stores player statistics. I calculate actual and theoretical win from table Y, set up a pivot table, and link 2 other columns to the pivot table to display actual and theoretical win. Problem is, only players who actually play have any records in table Y. The bosses want to see ALL persons enrolled in group X, whether they played or not. With my limited knowledge of Excel, I have 2 options: A. I modify the report to show ONLY those persons in the group who actually played. B. I show all persons in the group, but the player name/IDs and actual/ theoretical win figures will no longer match up. e.g group 234 includes the following, modified to show only persons who played: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Everyone's play is correctly attributed to him/her, but when I modify the report to include persons who did NOT play, Mr. Jolly Green Giant now appears on the list, and has Big Baby's figures incorrectly attributed to him, Big Baby in turn gets Moby Dick's data, and so on and so on. Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $1.20 $3.75 53087 Baby Big $28.10 $83.75 53088 Dick Moby How can I extract actual and theoretical win from the pivot table (which contains ONLY those players who played, along with their stats), match it up with the corresponding player IDs and names on the report (which contains ALL player IDs/names whether they played or not) and instruct Excel to skip or better yet populate with '$0.00' those cells that belong to players who did NOT play? As in Mr. Jolly Green Giant would now be included on the report, but would have '$0.00' in his 'Theo Win' and 'Actual Win' columns: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $0.00 $0.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Really hope I'm being clear enough about what I need, as I said, I'm new to this, but trying to be precise and provide all necesssary information. Any guidance is deeply appreciated. Thanks,- Hide quoted text - - Show quoted text - Apologies, I guess I was indeed not being clear. Umm... Basically I have a column X that has say 25 rows. Elsewhere on the spreadsheet I have column X appearing again paired with column Y and containing only those rows that have corresponding data in column Y. This reducies column X to say 20 rows. I now need to match up the values from column Y with the corresponding values in the original column X, inserting '$0.00' or something similar in the rows where the original column X does not have a corresponding value in column Y. Hope this is clearer, thanks,- Hide quoted text - - Show quoted text - Hi again Tom, I took the code you suggested and modified it to reflect cell values on my worksheet (I'll put them side by side for comparison): =if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False)) =if(iserror(match($L7,$H$3:$I$300,0),0,Vlookup($L7 ,$H$3:$I $300,2,False)) I don't know if I have some step/procedure done incorrectly or omitted altogether but I got an 'The formula you typed contains an error' message both when using my modified version and the original. Any thoughts? Thanks, |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Skip cell if X nonexistent, populate next cell
On Feb 2, 11:44 pm, "Tom Ogilvy" wrote:
=if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False)) doesn't close the iserror function. =if(iserror(match($A1,$M$1:$N$20,0)),0,Vlookup($A1 ,$m$1:$N$20,2,False)) -- Regards, Tom Ogilvy "prodeji" wrote in message oups.com... On Feb 1, 1:27 pm, Tom Ogilvy wrote: Assuming the first column X values are starting in A1 and the second are starting in M1, amounts in N1 (down to row 20), as an example, =if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False)) entered in B1, then drag fill down 25 rows. -- Regards, Tom Ogilvy "prodeji" wrote: On Feb 1, 12:18 pm, Tom Ogilvy wrote: It is unclear how you are linking to addtiional columns to the pivot table, but if you are getting the wrong results, you apparently are not doing it correctly. Maybe you are using a vlookup and not going for an exact match? -- Regards, Tom Ogilvy "prodeji" wrote: OK here's my dilemma; hope I explain myself clearly as I'm new at this. I have a worksheet that pulls the names and IDs of casino players enrolled in different groups from a database. Every person in the group is assigned a player ID. Not every person plays, however. I'm compiling a report that shows actual and theoretical win for each player. Table X stores player names and IDs, table Y stores player statistics. I calculate actual and theoretical win from table Y, set up a pivot table, and link 2 other columns to the pivot table to display actual and theoretical win. Problem is, only players who actually play have any records in table Y. The bosses want to see ALL persons enrolled in group X, whether they played or not. With my limited knowledge of Excel, I have 2 options: A. I modify the report to show ONLY those persons in the group who actually played. B. I show all persons in the group, but the player name/IDs and actual/ theoretical win figures will no longer match up. e.g group 234 includes the following, modified to show only persons who played: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Everyone's play is correctly attributed to him/her, but when I modify the report to include persons who did NOT play, Mr. Jolly Green Giant now appears on the list, and has Big Baby's figures incorrectly attributed to him, Big Baby in turn gets Moby Dick's data, and so on and so on. Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $1.20 $3.75 53087 Baby Big $28.10 $83.75 53088 Dick Moby How can I extract actual and theoretical win from the pivot table (which contains ONLY those players who played, along with their stats), match it up with the corresponding player IDs and names on the report (which contains ALL player IDs/names whether they played or not) and instruct Excel to skip or better yet populate with '$0.00' those cells that belong to players who did NOT play? As in Mr. Jolly Green Giant would now be included on the report, but would have '$0.00' in his 'Theo Win' and 'Actual Win' columns: Plyr ID Last Name First Name Theo Win Actual Win 53082 Duck Donald $0.60 $10.00 53084 Man Super $26.30 $70.00 53085 Giant Jolly Green $0.00 $0.00 53087 Baby Big $1.20 $3.75 53088 Dick Moby $28.10 $83.75 Really hope I'm being clear enough about what I need, as I said, I'm new to this, but trying to be precise and provide all necesssary information. Any guidance is deeply appreciated. Thanks,- Hide quoted text - - Show quoted text - Apologies, I guess I was indeed not being clear. Umm... Basically I have a column X that has say 25 rows. Elsewhere on the spreadsheet I have column X appearing again paired with column Y and containing only those rows that have corresponding data in column Y. This reducies column X to say 20 rows. I now need to match up the values from column Y with the corresponding values in the original column X, inserting '$0.00' or something similar in the rows where the original column X does not have a corresponding value in column Y. Hope this is clearer, thanks,- Hide quoted text - - Show quoted text - Hi again Tom, I took the code you suggested and modified it to reflect cell values on my worksheet (I'll put them side by side for comparison): =if(iserror(match($A1,$M$1:$N$20,0),0,Vlookup($A1, $m$1:$N$20,2,False)) =if(iserror(match($L7,$H$3:$I$300,0),0,Vlookup($L7 ,$H$3:$I $300,2,False)) I don't know if I have some step/procedure done incorrectly or omitted altogether but I got an 'The formula you typed contains an error' message both when using my modified version and the original. Any thoughts? Thanks,- Hide quoted text - - Show quoted text - WOW. Well I feel like a COMPLETE... Thanks guy :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to populate a cell with numeric value based on textselected from pull down in adjacent cell? | Excel Worksheet Functions | |||
| Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
| Skip cell if X nonexistent, populate next cell | Excel Discussion (Misc queries) | |||
| auto populate cell based on previous cell drop down list selectio. | Excel Discussion (Misc queries) | |||
| skip cell if blank go on to use another cell in calculation | Excel Programming | |||
Linear Mode
