|
very repetive formula needs looking at
Hi All
=IF('Date Details'!C3=Itininary!L4,('Date Details'!C5),IF('Date Details'!D3=Itininary!L4,('Date Details'!D5),IF('Date Details'!E3=Itininary!L4,('Date Details'!E5),(0)))) In the above I am seaching along row 3 starting from c to Find a date match in Itininary L4 WHEN IT IS ACHIEVED RETURNS TEXT IN 'DATE DETAILS'! row 5. Trouble is row 3 goes thru 253 columms. Can this repetive formula me made shorter ,and in the process I learn More, or do i have to keep going 253 times? Thanks to all you helpers out there. I look forward to being able to answer a few questions myself Stew |
very repetive formula needs looking at
Hi Stew
try =HLOOKUP(Itininary!L4,'Date Details'!$C3:$IV4,2,0) -- Regards Roger Govier "stew" wrote in message ... Hi All =IF('Date Details'!C3=Itininary!L4,('Date Details'!C5),IF('Date Details'!D3=Itininary!L4,('Date Details'!D5),IF('Date Details'!E3=Itininary!L4,('Date Details'!E5),(0)))) In the above I am seaching along row 3 starting from c to Find a date match in Itininary L4 WHEN IT IS ACHIEVED RETURNS TEXT IN 'DATE DETAILS'! row 5. Trouble is row 3 goes thru 253 columms. Can this repetive formula me made shorter ,and in the process I learn More, or do i have to keep going 253 times? Thanks to all you helpers out there. I look forward to being able to answer a few questions myself Stew |
very repetive formula needs looking at
This is brilliant however when it finds value in L4 IT RETURNS THE VALUE IN
ROW 4 OF THAT COLUMN AND NOT ROW 5 AS IT SHOULD ANY THOUGHTS BEST STEW, AND THANKS FOR THIS "Roger Govier" wrote: Hi Stew try =HLOOKUP(Itininary!L4,'Date Details'!$C3:$IV4,2,0) -- Regards Roger Govier "stew" wrote in message ... Hi All =IF('Date Details'!C3=Itininary!L4,('Date Details'!C5),IF('Date Details'!D3=Itininary!L4,('Date Details'!D5),IF('Date Details'!E3=Itininary!L4,('Date Details'!E5),(0)))) In the above I am seaching along row 3 starting from c to Find a date match in Itininary L4 WHEN IT IS ACHIEVED RETURNS TEXT IN 'DATE DETAILS'! row 5. Trouble is row 3 goes thru 253 columms. Can this repetive formula me made shorter ,and in the process I learn More, or do i have to keep going 253 times? Thanks to all you helpers out there. I look forward to being able to answer a few questions myself Stew |
very repetive formula needs looking at
Just a guess, but would it be
=HLOOKUP(Itininary!L4,'Date Details'!$C3:$IV5,3,0) ? --JP On Sep 18, 11:01*am, stew wrote: This is brilliant however when it finds value in L4 IT RETURNS THE VALUE IN ROW 4 OF THAT COLUMN AND NOT ROW 5 AS IT SHOULD ANY THOUGHTS BEST STEW, AND THANKS FOR THIS "Roger Govier" wrote: Hi Stew try =HLOOKUP(Itininary!L4,'Date Details'!$C3:$IV4,2,0) -- Regards Roger Govier "stew" wrote in message ... Hi All =IF('Date Details'!C3=Itininary!L4,('Date Details'!C5),IF('Date Details'!D3=Itininary!L4,('Date Details'!D5),IF('Date Details'!E3=Itininary!L4,('Date Details'!E5),(0)))) In the above I am seaching along row 3 starting from c to Find a date match in Itininary L4 WHEN IT IS ACHIEVED RETURNS TEXT IN 'DATE DETAILS'! *row 5. Trouble is row 3 goes thru 253 columms. Can this repetive formula me made shorter ,and in the process I learn More, or do i have to keep going 253 times? Thanks to all you helpers out there. I look forward to being able to answer a few questions myself Stew- Hide quoted text - - Show quoted text - |
very repetive formula needs looking at
Just change:
$IV4,2 to: $IV5,3 Hope this helps. Pete On Sep 18, 4:01*pm, stew wrote: This is brilliant however when it finds value in L4 IT RETURNS THE VALUE IN ROW 4 OF THAT COLUMN AND NOT ROW 5 AS IT SHOULD ANY THOUGHTS BEST STEW, AND THANKS FOR THIS "Roger Govier" wrote: Hi Stew try =HLOOKUP(Itininary!L4,'Date Details'!$C3:$IV4,2,0) -- Regards Roger Govier "stew" wrote in message ... Hi All =IF('Date Details'!C3=Itininary!L4,('Date Details'!C5),IF('Date Details'!D3=Itininary!L4,('Date Details'!D5),IF('Date Details'!E3=Itininary!L4,('Date Details'!E5),(0)))) In the above I am seaching along row 3 starting from c to Find a date match in Itininary L4 WHEN IT IS ACHIEVED RETURNS TEXT IN 'DATE DETAILS'! *row 5. Trouble is row 3 goes thru 253 columms. Can this repetive formula me made shorter ,and in the process I learn More, or do i have to keep going 253 times? Thanks to all you helpers out there. I look forward to being able to answer a few questions myself Stew- Hide quoted text - - Show quoted text - |
very repetive formula needs looking at
Roger JP and Pete
Thank you Stew "Pete_UK" wrote: Just change: $IV4,2 to: $IV5,3 Hope this helps. Pete On Sep 18, 4:01 pm, stew wrote: This is brilliant however when it finds value in L4 IT RETURNS THE VALUE IN ROW 4 OF THAT COLUMN AND NOT ROW 5 AS IT SHOULD ANY THOUGHTS BEST STEW, AND THANKS FOR THIS "Roger Govier" wrote: Hi Stew try =HLOOKUP(Itininary!L4,'Date Details'!$C3:$IV4,2,0) -- Regards Roger Govier "stew" wrote in message ... Hi All =IF('Date Details'!C3=Itininary!L4,('Date Details'!C5),IF('Date Details'!D3=Itininary!L4,('Date Details'!D5),IF('Date Details'!E3=Itininary!L4,('Date Details'!E5),(0)))) In the above I am seaching along row 3 starting from c to Find a date match in Itininary L4 WHEN IT IS ACHIEVED RETURNS TEXT IN 'DATE DETAILS'! row 5. Trouble is row 3 goes thru 253 columms. Can this repetive formula me made shorter ,and in the process I learn More, or do i have to keep going 253 times? Thanks to all you helpers out there. I look forward to being able to answer a few questions myself Stew- Hide quoted text - - Show quoted text - |
very repetive formula needs looking at
Hi Pete
Thanks for correcting my stupid mistake -- Regards Roger Govier "Pete_UK" wrote in message ... Just change: $IV4,2 to: $IV5,3 Hope this helps. Pete On Sep 18, 4:01 pm, stew wrote: This is brilliant however when it finds value in L4 IT RETURNS THE VALUE IN ROW 4 OF THAT COLUMN AND NOT ROW 5 AS IT SHOULD ANY THOUGHTS BEST STEW, AND THANKS FOR THIS "Roger Govier" wrote: Hi Stew try =HLOOKUP(Itininary!L4,'Date Details'!$C3:$IV4,2,0) -- Regards Roger Govier "stew" wrote in message ... Hi All =IF('Date Details'!C3=Itininary!L4,('Date Details'!C5),IF('Date Details'!D3=Itininary!L4,('Date Details'!D5),IF('Date Details'!E3=Itininary!L4,('Date Details'!E5),(0)))) In the above I am seaching along row 3 starting from c to Find a date match in Itininary L4 WHEN IT IS ACHIEVED RETURNS TEXT IN 'DATE DETAILS'! row 5. Trouble is row 3 goes thru 253 columms. Can this repetive formula me made shorter ,and in the process I learn More, or do i have to keep going 253 times? Thanks to all you helpers out there. I look forward to being able to answer a few questions myself Stew- Hide quoted text - - Show quoted text - |
very repetive formula needs looking at
You're welcome, Roger - easily done.
Pete On Sep 18, 11:04*pm, "Roger Govier" <roger@technology4unospamdotcodotuk wrote: Hi Pete Thanks for correcting my stupid mistake -- Regards Roger Govier |
| All times are GMT +1. The time now is 07:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com