Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Need some comments on my Utility_Move class module. | Excel Worksheet Functions | |||
Date & Time | New Users to Excel | |||
copying existing function | Excel Worksheet Functions | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |