Appending to existing function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D.Jessup - 25 Nov 2006 15:59 GMT

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.

Signature

Just an ordinary Joe

Reply to this Message

Niek Otten - 25 Nov 2006 16:11 GMT

=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?

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Greetings,
|
[quoted text clipped - 15 lines]
|
| Thanks for any help.

Reply to this Message

D.Jessup - 25 Nov 2006 16:21 GMT

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.

Signature

Just an ordinary Joe

> =IF(I1<J1,100,VLOOKUP(I1,'Male Tables'!$AJ$4:$AT$140,AS1))
>
[quoted text clipped - 21 lines]
> |
> | Thanks for any help.

Reply to this Message

Max - 26 Nov 2006 09:15 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> 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.

Reply to this Message

Max - 26 Nov 2006 10:43 GMT

Sorry, kindly dismiss the earlier response ..
I must have been intoxicated (red-faced)
There wasn't any need to use INDIRECT at all

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message