Hello all, hoping someone can help with a formulae dilema.
I have a table with target information set in rows, and a destination
group of cells that call for the information to be picked from the rows
and put in offset boxes, i.e when pasting the formulae first created
the formula is adding a 3 cell variance from the wanted destination
cells, because the destination cells are 3 cells apart from being
concurrent.

Can anyone suggest a formula that will solve the problem.

Thanks Richard.

Can you give a more specific description using cell references and examples
of the the data?


***********
Regards,
Ron

XL2002, WinXP-Pro


"richy" wrote:

Hello all, hoping someone can help with a formulae dilema.
I have a table with target information set in rows, and a destination
group of cells that call for the information to be picked from the rows
and put in offset boxes, i.e when pasting the formulae first created
the formula is adding a 3 cell variance from the wanted destination
cells, because the destination cells are 3 cells apart from being
concurrent.

Can anyone suggest a formula that will solve the problem.

Thanks Richard.

Yes thanks Ron.

Say my data is in A4, B4, C4, D4, E4 and I want the data from these
cells to transpose into:

a different sheet in the same file but the B2, B3, C2, D2, E2 cells, ie
the destination cells are not concurrently listed in rows or columns. I
can do the formula for a straight calculation, but cannot fatham how to
paste it to repeat in the next block of destination cells as the paste
function wants to pick the next series three cells away from the source
cell location

ie. A7, B7, C7, D7, E7 to destination worksheet B5, B6, C5, D5, E5.

When I want it to take A5, B5, C5, D5, E5 and place them in B5, B6, C5,
D5, E5.


Hope thats makes sense (it does in my head !)

Thanks for your input in advance.

Richard.

Let's see if I understand....

On Sheet1 you have valus in Cells A4:E4

On Sheet2, you want formulas that refer to Sheet1 in this way....
B2 refers to Sheet1!A4
B3 refers to Sheet1!B4
C2 refers to Sheet1!C4
D2 refers to Sheet1!D4
E2 refers to Sheet1!E4

and you want to be able to copy that formula scenario down and have the
following:
B5 refers to Sheet1!A7
B6 refers to Sheet1!B7
C5 refers to Sheet1!C7
D5 refers to Sheet1!D7
E5 refers to Sheet1!E7

Here's what I propose....

On Sheet2:
A2: 4
B2 refers to =OFFSET(Sheet1!$A$1,$A2-1,0)
B3 refers to =OFFSET(Sheet1!B$1,$A2-1,0)
C2 refers to =OFFSET(Sheet1!C$1,$A2-1,0)
D2 refers to =OFFSET(Sheet1!D$1,$A2-1,0)
E2 refers to =OFFSET(Sheet1!E$1,$A2-1,0)

Now..if you put a 7 in A5 on Sheet2, you can copy the set of formulas down
to B5 on Sheet2 and the formulas will all refer to the row 7 on Sheet1

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"richy" wrote:

Yes thanks Ron.

Say my data is in A4, B4, C4, D4, E4 and I want the data from these
cells to transpose into:

a different sheet in the same file but the B2, B3, C2, D2, E2 cells, ie
the destination cells are not concurrently listed in rows or columns. I
can do the formula for a straight calculation, but cannot fatham how to
paste it to repeat in the next block of destination cells as the paste
function wants to pick the next series three cells away from the source
cell location

ie. A7, B7, C7, D7, E7 to destination worksheet B5, B6, C5, D5, E5.

When I want it to take A5, B5, C5, D5, E5 and place them in B5, B6, C5,
D5, E5.


Hope thats makes sense (it does in my head !)

Thanks for your input in advance.

Richard.

Thanks for your help Ron.

I have input the formulas and i'm getting #REF!, any idea why ?

Also from your reply I don't understand the A2: 4 entry ? and your
suggestion to put a 7 in A5 on sheet 2.

I appreciate your help with this.

Cheers my friend.

Richard

Hi, Richard

The Sheet2 offset formulas I posted refer to Sheet1. Each one references a
cell in Row_1 and points a certain number of cells down from the first row.
The numbers to be entered in A2 and A5 tell those formulas how many rows to
"offset" by.

Example, using the B2 formula:
B2: =OFFSET(Sheet1!$A$1,$A2-1,0)
That formula initially points Sheet1!$A$1, then points at the cell that is 3
cells below that (A2-1 = 4-1 = 3), which is cell A4. Changing A2 from 4 to 5
(or 6 or whatever) changes the record on Sheet1 that the formulas refer to.

I used that technique because a simple copy paste won't increment references
1 row for every 3 rows you skip when copying.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"richy" wrote:

Thanks for your help Ron.

I have input the formulas and i'm getting #REF!, any idea why ?

Also from your reply I don't understand the A2: 4 entry ? and your
suggestion to put a 7 in A5 on sheet 2.

I appreciate your help with this.

Cheers my friend.

Richard

Thanks Ron, justing trying to get my head round that bit. How can A2-1
= 4 ? What would you input to get it to = 6 for example ?

Any ideas on the "I have input the formulas and i'm getting #REF!, any
idea why ? "

Cheers Richard

I can only guess that you don't have a sheet named Sheet1 OR you didn't enter
a value in cell A2. (A2 is a cell reference that tells the formula which row
on Sheet1 to point to.)

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"richy" wrote:

Thanks Ron, justing trying to get my head round that bit. How can A2-1
= 4 ? What would you input to get it to = 6 for example ?

Any ideas on the "I have input the formulas and i'm getting #REF!, any
idea why ? "

Cheers Richard

Ron

Thank you so much.

I've got my head around it now and it works great.

Really appreciate your help.

Thanks again.

Richard.

ps. A bit of a cheek I know, but should I get anyother formula
dilema's, would it be ok to contact you ? (as you seem to know what
your talking about !)


Richard.