Excel - Cell Reference

[VincentCrimmins]

Hi,

I have cell in a worksheet ("Sheet1") that references a cell in another, Sheet1!C3=Sheet2!D4

If I drag the formula down one, I know it will reference Sheet2!D5

Is there a way to drag it down to and that it will reference Sheet2!E4?

I have to do this for a lot of data and it the column in Sheet1 will reference numerous other sheets, so it can't really be done manually.

Any help greatly appreciated!

Thanks,

 

[GlennUK]

How many rows do you need to drag it down over?

If you know the shape beforehand, simple select the area ( for example Sheet1!A1 to Sheet1!A99 ) and type in =TRANSPOSE(Sheet2!D4:CX4)
and do Ctrl-Shift-Enter to enter as an array formula.

Note that the number of cells selected beforehand equals the number of cells in the source reference Sheet2!D4:CX4.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[VincentCrimmins]

Hi Glenn

Thanks for that.

Its actually each cell is pulling data from a different sheet, so that won't work (though I hadn't come across that formula, so thanks for that!).

I suppose what I'm asking really is, can you read in the formula in a cell, in this case 'Sheet2!D4', and say column +1, instead of row +1, when you drag the formula down one.

In other words, if in cells B1, B2 and B3 I have: SheetX!D30, SheetY!Q45, SheetZ!B12.

In cells C1, C2 and C3 I want SheetX!D31, SheetY!Q46, SheetZ!B13.

Does that help?

Thanks,

 

[kjv1611]

you can do it in VBA.

But are you sure Transpose won't work for what you need? At least in part? Have you tried? Do you know what Transpose does?

--

"If to err is human, then I must be some kind of human!" -Me

 

[VincentCrimmins]

I thought you could do it in VBA alright, but I was wondering if there was an Excel formula that could handle it?

I think I understand how transpose works, but I dont see how it solves my problem here? The TRANSPOSE function will only tranpose the values, not the formula.

In other words, if I have cells B1 containing '=SheetX!D30', how can I get cells C1 to contain '=SheetX!D31' via a formula, not manually.

Thanks,

 

[kjv1611]

Yeah, the transpose thing doesn't seem to affect the formulas in a way that would help. Well, it does look like you'll have to:
1. Do it manually
2. Do it in VBA
or
3. Beg someone else to do it. [WINK]

--

"If to err is human, then I must be some kind of human!" -Me

 

[GlennUK]

I suppose what I'm asking really is, can you read in the formula in a cell, in this case 'Sheet2!D4', and say column +1, instead of row +1, when you drag the formula down one.

In other words, if in cells B1, B2 and B3 I have: SheetX!D30, SheetY!Q45, SheetZ!B12.In cells C1, C2 and C3 I want SheetX!D31, SheetY!Q46, SheetZ!B13.

I think I understand how transpose works, but I dont see how it solves my problem here?

The TRANSPOSE function will only tranpose the values, not the formula.In other words, if I have cells B1 containing '=SheetX!D30', how can I get cells C1 to contain '=SheetX!D31' via a formula, not manually.

No, I don't think you see what I'm getting at ... I'm saying that you should enter a TRANSPOSE formula instead of dragging formulas down.

Your example in B1,B2, and B3 ( where you want to drag across ) would consist of 3 TRANSPOSE formulas. The first would be done by selecting B1:C1 ( or however many cells you want to transpose ), and entering
=TRANSPOSE(SheetX!D3031)
using Ctrl-Shift-Enter instead of Enter. ( the range used in the formula would cover the same number of cells that are selected before entering the formula, of course ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.