Drag down values but skip a cell. [SOLVED]

[James Perry]

Hello friends of Chandoo.org & here's my question.


Cells A1=9, A2=99, A3=999.

Now i make cell J4=A1. So J4 returns the value 9. Perfect.

Now Row 5 is 99% hidden; and naturally so is cell J5.

So i would like cell J6 (instead of J5 which is hidden) to return the value of A2, which is 99, when i drag J4 down.

Is there a solution??


Many thanks4 ur valuable time & solution.

James

 

[SirJB7]

Hi, James Perry!


First of all, rows are hidden or displayed, 99% hidden sounds like as almost virgin or as military intelligence (Groucho's dixit)

Now if you're talking about size, well, despite of what someone could say, size matters (women's and just a few men's dixit)


When you drag or copy and paste a cell in any direction, Excel does this:

- if the reference for the direction dragged is fixed (has $ in the formula), it keeps it fixed, otherwise it's offset regarding the difference of rows or columns between source and target

- =A1 in J4, dragged to J6 will convert it to =A3 (0 columns offset, 2 rows offset), if dragged to J7 it would be =A4, to L4 then =C1.

- =$A1 in J4, dragged to J6 will convert it to =$A3 (no offset in column, 2 rows offset), if dragged to J7 it would be =$A4, to L4 then =$A1.

- =A$1 in J4, dragged to J6 will convert it to =A$1 (0 columns offset, no offset in row), if dragged to J7 it would be =A$1, to L4 then =C$1.

- =$A$1 in J4, dragged anywhere would remain =$A$1 (no offset in column, no offset in row)


Regards!

 

[Faseeh]

Hi SirJb7,


I think the OP just want to skip the row that is hidden.


Regards,

 

[SirJB7]

@Faseeh

Hi!

If so, it can't be done by dragging the original formula, and as we don't know if there might be further drags or copies we can't suggest any formula with OFFSET or INDIRECT or anything.

Regards!

PS: But my 1st paragraph is true!

 

[James Perry]

Hello SirJB7.


That was truly convincing! I am very impressed and very grateful 4 all that wonderful explanation.

A million thanks 4 all the trouble taken in explaining all that 2 me.

Long live CHANDOO.ORG


Best wishes & stay blessed.

James

 

[SirJB7]

Hi, James Perry!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: Be aware of avoiding almost virgins, intelligent military guys and those who say that size doesn't matter... they all lie!