| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to insert formula result into range with zero values in cell
I have a column with possible Values A thru F for example. I need to insert
any of A thru F (whatever is there) into another range where there are cells equal to zero in that range (column). There can only be one copy of A thru F in the new range. Please help if you can, I am blocked on this project. I appreciate any help given. If this is more appropriate in worksheet functions, please advise. Posted only in programming. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to insert formula result into range with zero values in cell
I am having trouble visualizing what you are doing. Can you explain it a bit
clearer? "JPS" wrote in message ... I have a column with possible Values A thru F for example. I need to insert any of A thru F (whatever is there) into another range where there are cells equal to zero in that range (column). There can only be one copy of A thru F in the new range. Please help if you can, I am blocked on this project. I appreciate any help given. If this is more appropriate in worksheet functions, please advise. Posted only in programming. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to insert formula result into range with zero values in
I hope this helps. I have finally derived a 31 row column of
percussion/instruments/vocals that I use weekly for a large music production. I have compared them to last weeks list and they have been matched in the same location/row number that they occupied last week so that the musical instrument cabling doesn't change if the musical instrument is being used again this week. So I now have another column of musical instruments, vocalists and percussion instruments that are being used this week that are in need of placement. Row numbers 1-18 are percussion, row 19-24 are instruments, and rows 25-31 are vocals. The extras for the week have been identified as percussion, instrument, and vocal. Now I need to put them where they belong with some overlap if needed, ie, row 1-19 could be percussion, or row 18-26 could be instruments. I finally derived this formula to get rid of the #N/A that was coming up in my vlookup when I would get no hit/match =IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),0,VLOOKUP($H 2,$F$2:$G$50,2,0)) I end up with cells containing zero where I am not using what was used last week. "William Benson" wrote: I am having trouble visualizing what you are doing. Can you explain it a bit clearer? "JPS" wrote in message ... I have a column with possible Values A thru F for example. I need to insert any of A thru F (whatever is there) into another range where there are cells equal to zero in that range (column). There can only be one copy of A thru F in the new range. Please help if you can, I am blocked on this project. I appreciate any help given. If this is more appropriate in worksheet functions, please advise. Posted only in programming. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to insert formula result into range with zero values in
Is this better
=IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),"",VLOOKUP($ H2,$F$2:$G$50,2,0)) -- HTH Bob Phillips "JPS" wrote in message ... I hope this helps. I have finally derived a 31 row column of percussion/instruments/vocals that I use weekly for a large music production. I have compared them to last weeks list and they have been matched in the same location/row number that they occupied last week so that the musical instrument cabling doesn't change if the musical instrument is being used again this week. So I now have another column of musical instruments, vocalists and percussion instruments that are being used this week that are in need of placement. Row numbers 1-18 are percussion, row 19-24 are instruments, and rows 25-31 are vocals. The extras for the week have been identified as percussion, instrument, and vocal. Now I need to put them where they belong with some overlap if needed, ie, row 1-19 could be percussion, or row 18-26 could be instruments. I finally derived this formula to get rid of the #N/A that was coming up in my vlookup when I would get no hit/match =IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),0,VLOOKUP($H 2,$F$2:$G$50,2,0)) I end up with cells containing zero where I am not using what was used last week. "William Benson" wrote: I am having trouble visualizing what you are doing. Can you explain it a bit clearer? "JPS" wrote in message ... I have a column with possible Values A thru F for example. I need to insert any of A thru F (whatever is there) into another range where there are cells equal to zero in that range (column). There can only be one copy of A thru F in the new range. Please help if you can, I am blocked on this project. I appreciate any help given. If this is more appropriate in worksheet functions, please advise. Posted only in programming. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to insert formula result into range with zero values in
Bob,
Thank-you, but problem wasn't with the formula. I do want the zero instead of the #n/a. The issue is inserting the extra unplaced instruments mentioned above in the cells egual to zero. Can you help with that? JPS "Bob Phillips" wrote: Is this better =IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),"",VLOOKUP($ H2,$F$2:$G$50,2,0)) -- HTH Bob Phillips "JPS" wrote in message ... I hope this helps. I have finally derived a 31 row column of percussion/instruments/vocals that I use weekly for a large music production. I have compared them to last weeks list and they have been matched in the same location/row number that they occupied last week so that the musical instrument cabling doesn't change if the musical instrument is being used again this week. So I now have another column of musical instruments, vocalists and percussion instruments that are being used this week that are in need of placement. Row numbers 1-18 are percussion, row 19-24 are instruments, and rows 25-31 are vocals. The extras for the week have been identified as percussion, instrument, and vocal. Now I need to put them where they belong with some overlap if needed, ie, row 1-19 could be percussion, or row 18-26 could be instruments. I finally derived this formula to get rid of the #N/A that was coming up in my vlookup when I would get no hit/match =IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),0,VLOOKUP($H 2,$F$2:$G$50,2,0)) I end up with cells containing zero where I am not using what was used last week. "William Benson" wrote: I am having trouble visualizing what you are doing. Can you explain it a bit clearer? "JPS" wrote in message ... I have a column with possible Values A thru F for example. I need to insert any of A thru F (whatever is there) into another range where there are cells equal to zero in that range (column). There can only be one copy of A thru F in the new range. Please help if you can, I am blocked on this project. I appreciate any help given. If this is more appropriate in worksheet functions, please advise. Posted only in programming. Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to insert formula result into range with zero values in
Do you mean that you want to be able to add more instruments to the second
list but only as long as they aren't already there? A "unique" approach that doesn't allow duplicate instruments on the second column? "JPS" wrote: Bob, Thank-you, but problem wasn't with the formula. I do want the zero instead of the #n/a. The issue is inserting the extra unplaced instruments mentioned above in the cells egual to zero. Can you help with that? JPS "Bob Phillips" wrote: Is this better =IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),"",VLOOKUP($ H2,$F$2:$G$50,2,0)) -- HTH Bob Phillips "JPS" wrote in message ... I hope this helps. I have finally derived a 31 row column of percussion/instruments/vocals that I use weekly for a large music production. I have compared them to last weeks list and they have been matched in the same location/row number that they occupied last week so that the musical instrument cabling doesn't change if the musical instrument is being used again this week. So I now have another column of musical instruments, vocalists and percussion instruments that are being used this week that are in need of placement. Row numbers 1-18 are percussion, row 19-24 are instruments, and rows 25-31 are vocals. The extras for the week have been identified as percussion, instrument, and vocal. Now I need to put them where they belong with some overlap if needed, ie, row 1-19 could be percussion, or row 18-26 could be instruments. I finally derived this formula to get rid of the #N/A that was coming up in my vlookup when I would get no hit/match =IF(ISNA(VLOOKUP($H2,$F$2:$G$50,2,0)),0,VLOOKUP($H 2,$F$2:$G$50,2,0)) I end up with cells containing zero where I am not using what was used last week. "William Benson" wrote: I am having trouble visualizing what you are doing. Can you explain it a bit clearer? "JPS" wrote in message ... I have a column with possible Values A thru F for example. I need to insert any of A thru F (whatever is there) into another range where there are cells equal to zero in that range (column). There can only be one copy of A thru F in the new range. Please help if you can, I am blocked on this project. I appreciate any help given. If this is more appropriate in worksheet functions, please advise. Posted only in programming. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SUMIF result including values not specified in sum-range | Excel Worksheet Functions | |||
| How do I compare a result to a range of values? | Excel Worksheet Functions | |||
| Macro to insert a formula based on a range | Excel Discussion (Misc queries) | |||
| Range of Values Returns One Result | Excel Worksheet Functions | |||
| Question: Cell formula or macro to write result of one cell to another cell | Excel Programming | |||
Hybrid Mode
