Name with relative reference (shift, offset, rows...)

[Dag]

I have the name "DO" with value "DO=A1" when active cell is A2.
So:

-     A     B     C     D
1    10    20    30    40
2   =DO   =DO   =DO   =DO

and it giving me the result as:

 -    A     B     C     D
2    10    20    30    40

Question 1:
What name (what value in it) I should create in the next case (offset, index, rows... never mind: any working solution) which will works in columns A, B, C and D (like following):

 -    A     B     C     D     E     F     G     H     I     J     K
1    10                20                30                40
2   =DO   =DO   =DO   =DO

and result is to be:

-     A     B     C     D
2    10    20    30    40

Between data, 2 empty columns are always.

So far, I have wild solution with hidden row which have values that are used by offset:

-     A     B     C     D
2    =DO   =DO   =DO   =DO
* * *
Hid.  1     4     7     10

but there are must be better solution without any additional data anywhere.

Question 2:
What kind of name I should create in the next case:

-     A
1    10
2
3
4    20
5
6
7    30
8
9
10   40

{another sheet}

-     A     B     C     D
2   =DO   =DO   =DO   =DO

Result:

-     A     B     C     D
2    10    20    30    40

Between datas, 2 empty rows are always.

Thanks in advance.

 

[BobUmlas]

Apology accepted!

 

[Dag]

Do you have to use a Defined Name, or can you use a formula in the cell?

I prefer name of course. When you have many x 100 cells full of data, u can easily check out that only with names (Ctrl + ~) and, something like this: Sales, Interest, Credits, Leasing, Quater (all of this were about quaters

Your last formula works fine with names and without. If formula works, it will work with name for sure (if u create it on the proper way).
Few screen shots:
http://orwell.ru/junk/pngFormula
http://orwell.ru/junk/pngName
http://orwell.ru/junk/pngRes

BTW, those 2 works fine:
Q1: Define DO as =OFFSET(Sheet1!$A$1,0,COLUMN(Sheet1!A1)*3-3)
Q2: Define DO as =OFFSET(Sheet1!$A$1,COLUMN(Sheet1!A1)*3-3,0)

but not ROW instead of Column (in the second one).

 

[Dag]

Apology accepted!

Glad to hear that

Both of you helped me a lot. It was tricky and I forgot basic math solving more complex situation Simple, I couldn't catch how to walking trough.

2 images more (with your formulas):
http://orwell.ru/junk/pngNames1
http://orwell.ru/junk/pngNames2

 

[mgirvin]

Yes, you are right about the formula working in a defined name, but as you say with the relative cell references you have to create it in the proper way.

In those pictures you posted, that IS a lot of names for that Income Statement!

 

[Dag]

Yes, you are right about the formula working in a defined name, but as you say with the relative cell references you have to create it in the proper way.

In those pictures you posted, that IS a lot of names for that Income Statement!

Eh Sir... and a lot of data!! That is not our internal Income statement but document linked with state regulated form with a lot of stupid regulations. And I am getting vertical data and horizontal data and quarters data...

BTW, not all of that names belong to the Income statement worksheet... there are several sheets in that workbook.