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

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

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

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

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

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

Macro to insert formula result into range with zero values in
 

Doctor G,

This should not be a problem because I have already matched instruments
previously. Now I olnly need to place the instruments that did not match
this week/ie they are instruments not used last week and not already in the
list. Please help if you can.

JPS

"DoctorG" wrote:

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

Macro to insert formula result into range with zero values in
 

I cannot visualize what the spreadsheet looks like, what information is your
unique key for your vlookup and last but not least the sequencing of the
inputs. By this I mean where and when you input the necessary info for last
week and this week and what will be taken out or added next week. Do you keep
week numbers, dates?

If you don't mind, set up a "written" 3 instrument spreadsheet (put "Soso"
in A1, Vlookup(vxvxvxvx...) in G1 and so on) that I can follow to duplicate
what you have and I will be glad to give it a try.

"JPS" wrote:

Doctor G,

This should not be a problem because I have already matched instruments
previously. Now I olnly need to place the instruments that did not match
this week/ie they are instruments not used last week and not already in the
list. Please help if you can.

JPS

"DoctorG" wrote:

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

Macro to insert formula result into range with zero values in
 

would you like me to email you the spreadsheet.? I could probably clean it
up and get it to you.

"DoctorG" wrote:

I cannot visualize what the spreadsheet looks like, what information is your
unique key for your vlookup and last but not least the sequencing of the
inputs. By this I mean where and when you input the necessary info for last
week and this week and what will be taken out or added next week. Do you keep
week numbers, dates?

If you don't mind, set up a "written" 3 instrument spreadsheet (put "Soso"
in A1, Vlookup(vxvxvxvx...) in G1 and so on) that I can follow to duplicate
what you have and I will be glad to give it a try.

"JPS" wrote:

Doctor G,

This should not be a problem because I have already matched instruments
previously. Now I olnly need to place the instruments that did not match
this week/ie they are instruments not used last week and not already in the
list. Please help if you can.

JPS

"DoctorG" wrote:

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

Macro to insert formula result into range with zero values in
 

By all means. Use my gthalass hotmail account.

"JPS" wrote:

would you like me to email you the spreadsheet.? I could probably clean it
up and get it to you.

"DoctorG" wrote:

I cannot visualize what the spreadsheet looks like, what information is your
unique key for your vlookup and last but not least the sequencing of the
inputs. By this I mean where and when you input the necessary info for last
week and this week and what will be taken out or added next week. Do you keep
week numbers, dates?

If you don't mind, set up a "written" 3 instrument spreadsheet (put "Soso"
in A1, Vlookup(vxvxvxvx...) in G1 and so on) that I can follow to duplicate
what you have and I will be glad to give it a try.

"JPS" wrote:

Doctor G,

This should not be a problem because I have already matched instruments
previously. Now I olnly need to place the instruments that did not match
this week/ie they are instruments not used last week and not already in the
list. Please help if you can.

JPS

"DoctorG" wrote:

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