Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
I have a 40 year Capital Item Replacement Program.
This formula is in Row 4 , Columns B thru AP =if(ISNA(VLOOKUP(H2,Sheet7!$C$19:$C$D28,2,False)), 0,VLOOKUP(H2,Sheet7!$C$19:$D$28,2,False)) Row 2, Column B thru AP =2002 to 2042. Therefore B2=2002 & H2 = 2008 etc. The number in the reference cells on Page 7 is variable depending on a number input into another cell on Page 7. For instance an article installed in 1995 to be replaced in 13 years would be replaced in 2008. So far it works and puts a number in cell H4. My question, how do I get it to put a number in the 26th & 39th year? Any help appreciated. Thank You -- Duane Platt |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
Did come across your earlier posting in .newusers, but couldn't grasp your
set-up/issue. Think your setup description is incomplete. Explain and illustrate your set-up in Sheet7, eg paste what you have in the vlookup's table array Sheet7!$C$19:$D28. I can presume that C19:C28 in Sheet7 possibly contains the lookup years, eg: 2002, 2003 . but I don't know what value that the vlookup is returning, ie what's in D19:D28, and how the vlookup/its result relates to your question: .. how do I get it to put a number in the 26th & 39th year? Also, is there a possible inconsistency somewhere in that the vlookup table array houses only 10 lookup years C19:C28 but you have 41 lookup years placed in B2:AP2 in your formula sheet, viz: 2002 - 2042 (your vlookup is looking for exact match, hence there should be at least 41 lookup years in Sheet7's table array, no?) Could you also explain / illustrate further on this part: The number in the reference cells on Page 7 is variable depending on a number input into another cell on Page 7. For instance an article installed in 1995 to be replaced in 13 years would be replaced in 2008. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Duplatt" wrote in message ... I have a 40 year Capital Item Replacement Program. This formula is in Row 4 , Columns B thru AP =if(ISNA(VLOOKUP(H2,Sheet7!$C$19:$C$D28,2,False)), 0,VLOOKUP(H2,Sheet7!$C$19:$D$28,2,False)) Row 2, Column B thru AP =2002 to 2042. Therefore B2=2002 & H2 = 2008 etc. The number in the reference cells on Page 7 is variable depending on a number input into another cell on Page 7. For instance an article installed in 1995 to be replaced in 13 years would be replaced in 2008. So far it works and puts a number in cell H4. My question, how do I get it to put a number in the 26th & 39th year? Any help appreciated. Thank You -- Duane Platt |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
Max. Thank you for responding
This is the range on page 7. There are several ranges like this. The program is for tracking when to replace Capital items for our condo association. This particular range is for 'Porch Rails' the 13 is a variable. if changed to 14 then column D would change correspondingly. Sheet 3 lists all the items to be replaced in the A column Sheet 3 row 2 lists all the years fromm 2002 to 2042 Each cell below row 2 has the Vlookup formula. It works fine but, only for the years listed on sheet 7. C19:D28. I think that by adding more columns for the lookup range it would work. I don't know how to add additional Vlookup's to the formula Any help appreciated Merry Christmas - Duane C D Porch Rails 13 Built Replace Quantity 19 1995 2008 4 1996 2009 7 1997 2010 4 1998 2011 1 1999 2012 8 2000 2013 3 2001 2014 2 2002 2015 2 2003 2016 0 28 2004 2017 0 "Max" wrote: Did come across your earlier posting in .newusers, but couldn't grasp your set-up/issue. Think your setup description is incomplete. Explain and illustrate your set-up in Sheet7, eg paste what you have in the vlookup's table array Sheet7!$C$19:$D28. I can presume that C19:C28 in Sheet7 possibly contains the lookup years, eg: 2002, 2003 . but I don't know what value that the vlookup is returning, ie what's in D19:D28, and how the vlookup/its result relates to your question: .. how do I get it to put a number in the 26th & 39th year? Also, is there a possible inconsistency somewhere in that the vlookup table array houses only 10 lookup years C19:C28 but you have 41 lookup years placed in B2:AP2 in your formula sheet, viz: 2002 - 2042 (your vlookup is looking for exact match, hence there should be at least 41 lookup years in Sheet7's table array, no?) Could you also explain / illustrate further on this part: The number in the reference cells on Page 7 is variable depending on a number input into another cell on Page 7. For instance an article installed in 1995 to be replaced in 13 years would be replaced in 2008. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Duplatt" wrote in message ... I have a 40 year Capital Item Replacement Program. This formula is in Row 4 , Columns B thru AP =if(ISNA(VLOOKUP(H2,Sheet7!$C$19:$C$D28,2,False)), 0,VLOOKUP(H2,Sheet7!$C$19:$D$28,2,False)) Row 2, Column B thru AP =2002 to 2042. Therefore B2=2002 & H2 = 2008 etc. The number in the reference cells on Page 7 is variable depending on a number input into another cell on Page 7. For instance an article installed in 1995 to be replaced in 13 years would be replaced in 2008. So far it works and puts a number in cell H4. My question, how do I get it to put a number in the 26th & 39th year? Any help appreciated. Thank You -- Duane Platt |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
Afraid it's still tough to figure out what's actually happening over there
Perhaps you could upload a working sample of your actual* file via a free filehost & post the link to it in response here? You could use either of these free filehosts: http://www.freefilehosting.net/ http://cjoint.com/index.php *desensitize it as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Duplatt" wrote in message ... Max. Thank you for responding This is the range on page 7. There are several ranges like this. The program is for tracking when to replace Capital items for our condo association. This particular range is for 'Porch Rails' the 13 is a variable. if changed to 14 then column D would change correspondingly. Sheet 3 lists all the items to be replaced in the A column Sheet 3 row 2 lists all the years fromm 2002 to 2042 Each cell below row 2 has the Vlookup formula. It works fine but, only for the years listed on sheet 7. C19:D28. I think that by adding more columns for the lookup range it would work. I don't know how to add additional Vlookup's to the formula Any help appreciated Merry Christmas - Duane C D Porch Rails 13 Built Replace Quantity 19 1995 2008 4 1996 2009 7 1997 2010 4 1998 2011 1 1999 2012 8 2000 2013 3 2001 2014 2 2002 2015 2 2003 2016 0 28 2004 2017 0 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
Thank you Max
It's a little too late for me to attempt that tonight. Will construct something tomorrow and post Duane "Max" wrote: Afraid it's still tough to figure out what's actually happening over there Perhaps you could upload a working sample of your actual* file via a free filehost & post the link to it in response here? You could use either of these free filehosts: http://www.freefilehosting.net/ http://cjoint.com/index.php *desensitize it as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Duplatt" wrote in message ... Max. Thank you for responding This is the range on page 7. There are several ranges like this. The program is for tracking when to replace Capital items for our condo association. This particular range is for 'Porch Rails' the 13 is a variable. if changed to 14 then column D would change correspondingly. Sheet 3 lists all the items to be replaced in the A column Sheet 3 row 2 lists all the years fromm 2002 to 2042 Each cell below row 2 has the Vlookup formula. It works fine but, only for the years listed on sheet 7. C19:D28. I think that by adding more columns for the lookup range it would work. I don't know how to add additional Vlookup's to the formula Any help appreciated Merry Christmas - Duane C D Porch Rails 13 Built Replace Quantity 19 1995 2008 4 1996 2009 7 1997 2010 4 1998 2011 1 1999 2012 8 2000 2013 3 2001 2014 2 2002 2015 2 2003 2016 0 28 2004 2017 0 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
Hi Max
I posted to www.freefilehosting.net/dowmload/39ck4 Sheet 7 and 3A are my concern. Sheet 3A is automatically updated from Sheet 7. Sheet 3 is manually updated. (an option) No line item is expected to be replaced in less than 13 years. I probably need to add 2 more columns for each catagory range on sheet 7 for additional years. Example, for a unit built in 1995 the porch rails would br expected to be replaced in 2008 - 2021 & 2034. I don't know how to alter the Vlookup formulas on Sheet 3A to reflect the added columns on Sheet 7. Any advise appreciated -- Duane "Duplatt" wrote: Thank you Max It's a little too late for me to attempt that tonight. Will construct something tomorrow and post Duane "Max" wrote: Afraid it's still tough to figure out what's actually happening over there Perhaps you could upload a working sample of your actual* file via a free filehost & post the link to it in response here? You could use either of these free filehosts: http://www.freefilehosting.net/ http://cjoint.com/index.php *desensitize it as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Duplatt" wrote in message ... Max. Thank you for responding This is the range on page 7. There are several ranges like this. The program is for tracking when to replace Capital items for our condo association. This particular range is for 'Porch Rails' the 13 is a variable. if changed to 14 then column D would change correspondingly. Sheet 3 lists all the items to be replaced in the A column Sheet 3 row 2 lists all the years fromm 2002 to 2042 Each cell below row 2 has the Vlookup formula. It works fine but, only for the years listed on sheet 7. C19:D28. I think that by adding more columns for the lookup range it would work. I don't know how to add additional Vlookup's to the formula Any help appreciated Merry Christmas - Duane C D Porch Rails 13 Built Replace Quantity 19 1995 2008 4 1996 2009 7 1997 2010 4 1998 2011 1 1999 2012 8 2000 2013 3 2001 2014 2 2002 2015 2 2003 2016 0 28 2004 2017 0 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
Think your earlier link should have read:
http://www.freefilehosting.net/download/39ck4 (you had a typo there in "download") Here's a suggested set-up using index/match which should do it here .. Illustrated in this sample: http://cjoint.com/?mxixGcos1o Cyclic Replacement via Index n Match.xls In Sheet7, For Porch Rails, Insert 2 new columns D & E to cater for the next 2 cycles of replacements, eg: Replace2 & Replace3 Place in D19: =C19+$F$17 Copy D19 across to E19, fill down to E28 Then in Sheet3A, Put in B4: =IF(ISNA(MATCH(B2,Sheet7!$C$19:$C$28,0)), IF(ISNA(MATCH(B2,Sheet7!$D$19:$D$28,0)), IF(ISNA(MATCH(B2,Sheet7!$E$19:$E$28,0)),"", INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$E$19:$E$ 28,0))), INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$D$19:$D$ 28,0))), INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$C$19:$C$ 28,0))) Copy B4 across to AP4. This would return the results that you seek. Just repeat the same construct accordingly for the other lines. In the sample, I've done it for the Gutters - Single, Double & Quad lines as a further example. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Duplatt" wrote: Hi Max I posted to www.freefilehosting.net/dowmload/39ck4 Sheet 7 and 3A are my concern. Sheet 3A is automatically updated from Sheet 7. Sheet 3 is manually updated. (an option) No line item is expected to be replaced in less than 13 years. I probably need to add 2 more columns for each catagory range on sheet 7 for additional years. Example, for a unit built in 1995 the porch rails would br expected to be replaced in 2008 - 2021 & 2034. I don't know how to alter the Vlookup formulas on Sheet 3A to reflect the added columns on Sheet 7. Any advise appreciated -- Duane |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
Max
That is super Thank you & Merry Christmas Duane "Max" wrote: Think your earlier link should have read: http://www.freefilehosting.net/download/39ck4 (you had a typo there in "download") Here's a suggested set-up using index/match which should do it here .. Illustrated in this sample: http://cjoint.com/?mxixGcos1o Cyclic Replacement via Index n Match.xls In Sheet7, For Porch Rails, Insert 2 new columns D & E to cater for the next 2 cycles of replacements, eg: Replace2 & Replace3 Place in D19: =C19+$F$17 Copy D19 across to E19, fill down to E28 Then in Sheet3A, Put in B4: =IF(ISNA(MATCH(B2,Sheet7!$C$19:$C$28,0)), IF(ISNA(MATCH(B2,Sheet7!$D$19:$D$28,0)), IF(ISNA(MATCH(B2,Sheet7!$E$19:$E$28,0)),"", INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$E$19:$E$ 28,0))), INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$D$19:$D$ 28,0))), INDEX(Sheet7!$F$19:$F$28,MATCH(B2,Sheet7!$C$19:$C$ 28,0))) Copy B4 across to AP4. This would return the results that you seek. Just repeat the same construct accordingly for the other lines. In the sample, I've done it for the Gutters - Single, Double & Quad lines as a further example. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Duplatt" wrote: Hi Max I posted to www.freefilehosting.net/dowmload/39ck4 Sheet 7 and 3A are my concern. Sheet 3A is automatically updated from Sheet 7. Sheet 3 is manually updated. (an option) No line item is expected to be replaced in less than 13 years. I probably need to add 2 more columns for each catagory range on sheet 7 for additional years. Example, for a unit built in 1995 the porch rails would br expected to be replaced in 2008 - 2021 & 2034. I don't know how to alter the Vlookup formulas on Sheet 3A to reflect the added columns on Sheet 7. Any advise appreciated -- Duane |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup to insert num in mult. columns
Welcome, Duane.
Merry Christmas -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Duplatt" wrote in message ... Max That is super Thank you & Merry Christmas Duane |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can no longer insert new columns & unhide hidden columns | Excel Worksheet Functions | |||
unable to insert columns in excel, insert- columns (disabled) | Excel Discussion (Misc queries) | |||
insert columns macro is putting 2 columns instead of 1 | Excel Worksheet Functions | |||
Excel contact list-going from one row to mult. columns | Excel Discussion (Misc queries) | |||
I want to be able to insert 1 Excel Wkbk (w mult tabs) into anothe | Excel Worksheet Functions |