Appending to existing function
Greetings,
I currently have a function I need to append to for a whole column: =VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1) =VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2) =VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3) " " " " " " I need to append it to look like this: =IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1)) =IF(I2<J2,100,VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2)) =IF(I3<J3,100,VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3)) Is there an easy method to appended the "=IF(I1<J1,100(existing data)" to the existing function without changing current references? I have 6 columns with over 200 rows that I need to apply similar appends too. Thanks for any help. -- Just an ordinary Joe |
Appending to existing function
=IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!$AJ$4:$AT$140,AS1))
Just copy down as far as you need. All references will be adjusted, with the exception those with $ signs. BTW, I can't imagine the "AS1" is correct. Shouldn't it just be 10; the relative column number in the search table? -- Kind regards, Niek Otten Microsoft MVP - Excel "D.Jessup" wrote in message ... | Greetings, | | I currently have a function I need to append to for a whole column: | =VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1) | =VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2) | =VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3) | " " " " " " | | I need to append it to look like this: | =IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1)) | =IF(I2<J2,100,VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2)) | =IF(I3<J3,100,VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3)) | | Is there an easy method to appended the "=IF(I1<J1,100(existing data)" to | the existing function without changing current references? | | I have 6 columns with over 200 rows that I need to apply similar appends too. | | Thanks for any help. | -- | Just an ordinary Joe |
Appending to existing function
Thank you very much!!! I was dreading the though of having to do a manual
change. And to answer your question about the AS1 reference. This is correct, it is yet another function that is used elsewhere on the worksheet. -- Just an ordinary Joe "Niek Otten" wrote: =IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!$AJ$4:$AT$140,AS1)) Just copy down as far as you need. All references will be adjusted, with the exception those with $ signs. BTW, I can't imagine the "AS1" is correct. Shouldn't it just be 10; the relative column number in the search table? -- Kind regards, Niek Otten Microsoft MVP - Excel "D.Jessup" wrote in message ... | Greetings, | | I currently have a function I need to append to for a whole column: | =VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1) | =VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2) | =VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3) | " " " " " " | | I need to append it to look like this: | =IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!AJ4:AT140,AS1)) | =IF(I2<J2,100,VLOOKUP(I2,'Male Tables'!AJ4:AT140,AS2)) | =IF(I3<J3,100,VLOOKUP(I3,'Male Tables'!AJ4:AT140,AS3)) | | Is there an easy method to appended the "=IF(I1<J1,100(existing data)" to | the existing function without changing current references? | | I have 6 columns with over 200 rows that I need to apply similar appends too. | | Thanks for any help. | -- | Just an ordinary Joe |
Appending to existing function
Since you need the column index part, ie: AS1
to increment to: AS2, AS3 etc as you copy down from the top cell think we could use: INDIRECT("AS"&ROW(A1)) in the top cell's formula The ROW(A1) will act as the incrementer as you copy down Try this slight tweak to Niek's suggestion: =IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!$AJ$4:$AT$140,INDIRECT("AS"&ROW(A1)))) Copy down as required -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "D.Jessup" wrote: Thank you very much!!! I was dreading the though of having to do a manual change. And to answer your question about the AS1 reference. This is correct, it is yet another function that is used elsewhere on the worksheet. -- Just an ordinary Joe |
Appending to existing function
Sorry, kindly dismiss the earlier response ..
I must have been intoxicated (red-faced) There wasn't any need to use INDIRECT at all -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
All times are GMT +1. The time now is 07:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com