Modify formula containing consecutive "IF" statements to enable drag-down

**bubastisbastet** · 10-08-2008, 07:27 AM

Hi All,

Was hoping one of you gurus could help me adjust a formula such that I can drag it down to fill a series of cells within a column.

Let's say the formula in cell A1 is:

Please Login or Register  to view this content.

and the formula in cell A2 is:

Please Login or Register  to view this content.

and thus the formula in cell A3 is:

Please Login or Register  to view this content.

...and so on and so forth...

So as you can see, all I am wanting to do is add the numerical value of whatever Y6 is to its corresponding "D" partner, ie.

1+21=22; 1+22=23

2+22=24; 2+24=26

3+23=26; 3+26=29

4+24=28; 4+28=32

Many Thanks!!!!

**daddylonglegs** · 10-08-2008, 08:05 AM

Try this formula in A1 copied down

=IF(AND(Y$6>=1,Y$6<=4),OFFSET(D$21,(ROWS(A$1:A1)-1)*4+Y$6-1,0),"")

**Cheeky Charlie** · 10-08-2008, 08:06 AM

For this kind of thing, making a lookup table will work. there are hundreds of examples of vlookups in lookup tables on this forum.

You may, however, find using column() (which returns the number of the column in the reference.

You may also find some mileage in the offset() and indirect() functions

HTH

**bubastisbastet** · 10-09-2008, 08:44 AM

daddylonglegs,

thank-you for the suggestion....unfortunately it only kinda worked and did not give me exactly what i need...

i have attached a picture of exactly what i mean.

in essence: the user may input a numerical value (arbitrarily from 1-5) in cell F6 (shown as "3"). when this number is chosen, i then want to reference BOTH the multiples of that chosen number (1,2,3,4,5...) or (2,4,6,8...) or (3,6,9,12...) etc from D21:D40, AND the corresponding partnered entry in E21:E40 (just arbitrary objects shown here for simplicity), and show them as a separate entity in the spreadsheet (eg. columns JK, LM, NO, PQ and RS).

I should point out that the textual values in E21:E40 are actually numerical values in spreadsheet 'proper' (ie, i am not trying to use an INDEX function to return the actual word in the cell), so please ignore the actual words and for argument's sake assume they are unique numbers instead.

i figure if i can get the correct formula to enable the first part (ie, reference to D21:D40) i can then apply the same thing to E21:E40. i omitted this in the original question to keep it simple.

I have some 100+ cells in the column i need to reference, hence my preference for a formula that can be easily copied down. it may seem a little odd as to why i want to do this, but the real spreadsheet actually refers to a series of calculated numbers that are subsequently graphed....FYI

in the meantime, i will take on the suggestions as given by cheekycharlie to see if i can't approach it this way...i am a total newb with this stuff, so it takes me a frustrating amount of time to get things to do what i want!

Thanks again...much appreciated.

**daddylonglegs** · 10-09-2008, 10:25 AM

The principle is still the same.....

You could probably use INDEX (which is preferable to OFFSET usually because OFFSET is a volatile function which re-calculates every time you re-calculate anything, thius using more resources) but it might depend how you want to show cells after data is exhausted.

Try this formula in J12 copied across to K12 and down both columns

=OFFSET(D$20,ROWS(J$12:J12)*$F$6,0)

Zeroes will show in cells after your data is exhausted. You can format the cells to not dispaly zero, e.g. with custom format 0;0;

....assuming that zero might not be a valid value to display