Thanks for feeding back, Khalil.
Pete
On Aug 30, 6:26 pm, "Khalil Handal" <khhan...@stthom.edu> wrote:
> Hi Pete,
> It cannot be more clear. Thanks a lot. I understood it and it works fine.
>
> "Pete_UK" <pashu...@auditel.net> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > Instead of putting 30, 36, 42 etc in the formulae explicitly, you can
> > calculate what it should be from the row that it is on. Here's an
> > example which works in a cell on row 21:
>
> > =(ROW()-20)*6+24
>
> > or, better still:
>
> > =ROW(A1)*6+24
>
> > (better because it is independent of the row you put it on)
>
> > This will return 30. If you copy it down it will return 36, 42 etc on
> > successive rows. So, just replace your first 30 with ROW(A1)*6+24 in
> > the formula in E21. Your formula might also become a bit easier to
> > read if you define a named range for Marks!$F$14:$DG$65 (eg call it
> > table), then your formula becomes:
>
> > =VLOOKUP($A$3,table,30,FALSE)
>
> > or with my suggestions above:
>
> > =VLOOKUP($A$3,table,ROW(A1)*6+24,FALSE)
>
> > Note that if you move this formula onto a different row (eg by
> > inserting rows above it), then it should still work.
>
> > Hope this helps.
>
> > Pete
>
> > On Aug 30, 2:31 pm, "Khalil Handal" <khhan...@stthom.edu> wrote:
> >> Hi,
> >> I have a lot of vlookup formulas that looks like this one:
> >> Cell E21=VLOOKUP($A$3,Marks!$F$14:$DG$65,30,FALSE)
> >> Cell E22=VLOOKUP($A$3,Marks!$F$14:$DG$65,36,FALSE)
>
> >> The difference of 6 is for the next formula.
> >> When I do some changes in the sheet Marks the column index is changed and
> >> I
> >> have to do it manualy.
> >> Is there a way that this can be done automatically? (ie. add 6 each time)
> >> this makes it more easy to work. I copy the cell E21 down for the other
> >> formulas and the number are changed 30, 36, 42 ...- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
