Excel Help please.....

Shelster · 29 Jun 2012 at 14:54

At present, I am trying to fill a column with the data from a row on another sheet. The data in that sheet, does change and being inherintley lazy, I ponly want to change one set of data (the row also reflects on another sheet as a row)

When I place the value into the 1st cell it reads '='IDs and Naming'!B3' but if i try to do a drag fill it wil read '='IDs and Naming'!B4' and '='IDs and Naming'!B5' and so on. What I am wanting to do is have the column increment up and not the cell number.

Is there a way to achieve what I am after?

NXZT · 29 Jun 2012 at 15:13

difficult...referencing cell in excel will work best when both spreadsheets (the one containing the data and the one with the cell reference) have the same structure.
You can bring the spreadsheets into the same structure by marking the spreadsheet with all data and then using Copy/Paste Special and tick the transpose box.
Give it a try....

Shelster · 29 Jun 2012 at 15:21

Have thought of the transpose, but as I already reference the row setup on the main page as a row, would be duplicated stuff all over the place.

Did see soemthing on a search about INDEX, but dont really understand the syntax of how to achieve it.

Trying to explain another way....

What happens:

Sheet 6!B3
Sheet 6!B4
Sheet 6!B5

What I want:

Sheet 6!B3
Sheet 6!C3
Sheet 6!D3.......and so on

EDIT:- I have tried the INDEX, which does appear to work ok, YET, it starts at Column U and not A as I would like it.......

Joshct · 29 Jun 2012 at 15:40

Sorry just to check you want:

a
b
c
d
e

to become

a b c d e?

If so it's possible, i've done it recently, and i'll find it for you. If thats not what you want then i didn't understand sorry. (I.e convert a list of data from a coloumn of data to a row of it)

EDIT: Can't remember exactly how to do it, but if thats what you need look up how to 'Transpose' data.

Shelster · 29 Jun 2012 at 15:44

Josh,

Is the vice versa of that...want a row of data to become a column

123456 to be

1
2
3
4
5
6

but the column is on a different sheet to the row....

Joshct · 29 Jun 2012 at 15:46

It works both ways, personally i'd transpose the data in the same sheet, and then copy it to the new one. (About to leave work else i'd google the specifics for you)

james.miller · 29 Jun 2012 at 16:04

you can do it with offset but its a bit messy, something like:

=offset(IDs and Naming!$B$3,0,ROW()-X)

where x is the row number of the first cell you would be using this formula in . Ie, if you are starting on row 100 then x = 100.

Like i said, messy, but it does work

Linkex · 29 Jun 2012 at 16:14

Shelster said:
Have thought of the transpose, but as I already reference the row setup on the main page as a row, would be duplicated stuff all over the place.

Did see soemthing on a search about INDEX, but dont really understand the syntax of how to achieve it.

Trying to explain another way....

What happens:

Sheet 6!B3
Sheet 6!B4
Sheet 6!B5

What I want:

Sheet 6!B3
Sheet 6!C3
Sheet 6!D3.......and so on

EDIT:- I have tried the INDEX, which does appear to work ok, YET, it starts at Column U and not A as I would like it.......

Try this - in your index formula, replaced
ROW()
with
22-ROW()

Shelster · 2 Jul 2012 at 09:13

All,

Many thnaks for the replies and help.

Now I am back in work am giving it a go, but all seems to be not working.

The transposing works, but want to keep it referenced so I only have to update one list and not 3 on seperate sheets.

The INDEX trick worked for the first 2 cells, but then all I get after that is #VALUE!

OFFSET gave me the same results as the INDEX without the 22- added.....

I have reshuffled the data so that it starts at A1, with the first cell I am wanting to reference now being at A2 then the next being B2, C2, D2 and so on. Sheet now called 'Names'