VBA build custom function

[edwardpestian]

Okay, so I've got the first formula below working, but I need to expand on it. Should I write a custom function in VBA?How? Or can I somehow combine all of these functions into one?

So what I'm looking at is:

I need it to perform the same OFFSET function, except with a change to the range. I need it to calculate based off of

(A1)+100 IF H8<>"Day"
(A1)+101 IF H8<>"Swing"
(A1)+102 IF H8<>"Grave"

If the first function returns nothing "", then perform the second, and so forth.


=IF(OR(Date=0,$H$8<>"Day"),"",OFFSET(Data!$E$5,ROW ($A$1)+101,MATCH($G$8,Data!$F$3:$CT$3,0)))

ELSEIF

=IF(OR(Date=0,$H$8<>"Day"),"",OFFSET(Data!$E$5,ROW ($A$1)+102,MATCH($G$8,Data!$F$3:$CT$3,0)))

ELSEIF

=IF(OR(Date=0,$H$8<>"Day"),"",OFFSET(Data!$E$5,ROW ($A$1)+103,MATCH($G$8,Data!$F$3:$CT$3,0)))

I'm stuck on this one for days...

Thanks in advance for any help or feedback.

EP

 

[SkipVought]

Hi,

I think you're making this alot more complex than it need to be.

1. ROW($A$1) returns 1. So ROW($A$1)+101 is 102. So your 3 row offsets are 102, 103 & 104. Why obsure the obvious?

2. MATCH($G$8,Data!$F$3:$CT$3,0) will return the same index in each of your formulas, since $G$8 is an absolute reference.

So the first MATCH argument ought not to be absolute. I have no idea what it should be cuz I really can't figger out what you're doing and with what.



Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[VicRauch]

This question was already answered in thread thread68-1226951.

 

[edwardpestian]

All,

I got it working.

=IF($H$8="","",OFFSET(Data!$E$5,MATCH($H$8,{"Day", "Swing","Grave"},0)+101,MATCH($G$8,Data!$F$3:$CT$3 ,0)))

And then....a much more eloquent version was shared with me.

=IF($H$8="","",OFFSET(Data!$E$5,MATCH($H$8,{"Day", "Swing","Grave"},0)+101,MATCH($G$8,Data!$F$3:$CT$3 ,0)))

Thanks for the help.

EP