Excel question - how can I easily link to a row of data with gaps between the cells?

Excel question - how can I easily link to a row...

Didn't find your answer?

I feel like there has to be an easy way to do this.....

I have two worksheets. I simply want to link one row of continuous data to a row of data in the other worksheet where there are gaps between the columns.

e.g. 

Worksheet 1

A B C D E F G H I J 

Worksheet 2

A C E G I

Excel doesn't seem to take the hint if I start manually linking the cells and try to autofill the rest of the series, even though there's a very obvious pattern.

Linking each cell manually is very time consuming and susceptible to errors.

There is a good reason why the two worksheets are different by the way. 

Your help would be much appreciated!!!

Replies (25)

Please login or register to join the discussion.

avatar
By stevie
14th Nov 2014 16:06

Linking?

What do you mean by linking?

 - hyperlink, formula or what?

Thanks (0)
Replying to DJKL:
avatar
By draze1458
14th Nov 2014 16:16

By "linking" I mean putting

By "linking" I mean putting an equals sign in one cell and then selecting the cell I want it to pick the data up from.

Thanks (0)
avatar
By paulwakefield1
14th Nov 2014 16:38

How about using the INDIRECT function?

Something along the lines of: =INDIRECT("Sheet1!r1c"&COLUMN()*2,FALSE)

Where Sheet1 is the name of your source sheet, r1 is the row number in your source sheet.

The column number may need a bit of playing with to give the right answer.

 

Thanks (0)
Replying to SteveHa:
avatar
By draze1458
14th Nov 2014 17:05

Paul - THANKS.

This looks like it could work except I can't seem to specify which COLUMN to start the series from....

Any ideas?

 

Thanks (0)
avatar
By paulwakefield1
14th Nov 2014 16:45

Or

create a normal series (=a1, =b1, =c1, etc) and delete every other column. Select using the control key might be easiest although it is a pain if you make a mistake when well into the process!

Alternatively there are macros and add ins which could provide a solution.

Thanks (0)
Portia profile image
By Portia Nina Levin
14th Nov 2014 16:47

Merge cells?

.

Thanks (0)
avatar
By paulwakefield1
14th Nov 2014 17:22

Which is your first source column

and which column is your first formula in?

Thanks (0)
Replying to Wanderer:
avatar
By draze1458
14th Nov 2014 17:23

Column 5...

Thanks (0)
avatar
By paulwakefield1
14th Nov 2014 17:42

How about this?

=INDIRECT("Sheet1!r1c"&(COLUMN(Sheet1!E1)-COLUMN(Sheet1!$D$1))*2-1,FALSE)

 

or

 

=INDEX(Sheet1!$e$1:$aa$1,1,(COLUMN(Sheet1!E1)-COLUMN(Sheet1!$D$1))*2-1)

 

Where Sheet 1!E1 is your first source formula and Sheet1!e1:aa1 is the complete source range

 

Apologies for any syntax errors as I'm doing this on the hoof and, with a bit more thought, it can probably be simplified but I'm sure you get the idea.

Thanks (0)
avatar
By paulwakefield1
17th Nov 2014 11:28

Having had the chance to look at

this properly, you have probably already realised that the INDIRECT example immediately above doesn't give the correct starting point (the INDEX function is fine).

Here is an INDIRECT function which is simpler, more flexible and works assuming the first source formula is in Column E:

=INDIRECT("Sheet1!r1c"&(COLUMN(A1))*2+3,FALSE)

Beware though of adding columns or rows to the worksheets involved. Ideally the hard coded numbers should be formula driven.

 

Thanks (0)
avatar
By paulwakefield1
17th Nov 2014 11:50

Neater still

=OFFSET(Sheet1!$E$1,0,(COLUMN(A1)-1)*2)

Thanks (0)
avatar
By Richard Benner
17th Nov 2014 13:18

HLOOKUP

How about using HLOOKUP with a little error capture;

 

=IFERROR(HLOOKUP(A1,Worksheet2!$A$1:$A$26,1,0),"")

 

"A1" is the cell you're looking for and "Worksheet2!$A$1:$A$26" is the range you're looking for it in. This will return a blank if the cell you're after isn't in the destination range.

Thanks (0)
avatar
By PracticePartner
17th Nov 2014 13:33

<=>?

If you only want to show the 5 columns from the other sheet then just put an = in each cell of the first row then copy down to the rest of the rows.

It is satisfying to solve things like this through formulae though!

Thanks (0)
avatar
By Richard Benner
17th Nov 2014 13:38

I think the issue is that there is a row with certain values (let's say there's 6, A-F). He then wants to match this master sheet and show any cells on the other sheets where they match;

Sheet 1: A   B   C   D   E   F

Sheet 2: A        C   D        F

Sheet 3:      B        D   E   F

Sheet 4: A        C        E

There's a number of ways to do this, finding the one that is the 'easiest' is going to be up for debate though.

Thanks (0)
avatar
By paulwakefield1
17th Nov 2014 13:52

Maybe I have misunderstood the question

I had assumed that in sheet 1 we have data in, say, A1, B1, C1, D1, E1 etc

and in sheet 2 in, say, cells A1, B1, C1 we want the effective formula =Sheet1!A1, =Sheet1!C1, =Sheet1!E1, etc.

Perhaps the OP could clarify.

(Edited for clarity)

Thanks (0)
avatar
By Richard Benner
17th Nov 2014 13:58

Hadn't considered he was entering the formulas in Sheet 2 to pull data from Sheet 1. It would be good to see a sample of the data, are there headers etc, OP?

Thanks (0)
Portia profile image
By Portia Nina Levin
17th Nov 2014 14:04

Surely

The obvious thing to do is simply merge cells A1 and B1, C1 and D1, E1 and F1, etc (inserting new columns as necessary) on sheet one and the whole thing just works out of the box?

Thanks (0)
avatar
By Richard Benner
17th Nov 2014 14:06

That's pretty time consuming when you get to a large range and it's assuming that there is always a gap of one missing value (A,C,E,G), whereas there may well be a bigger gap (A,E,I,M) etc. There are ways to do this with pretty simple formulas (vlookups are the most commonly known) that would be robust to these differences, all depends if we can get clarification from OP I guess.

We can get all fancy with formulas but the question was the easiest way of doing it which is the interesting part here.

Edit: Now I know my ABC's...

Thanks (0)
avatar
By paulwakefield1
17th Nov 2014 15:03

We'll just need to wait

We all seem to be reading the question differently. I see it as a formula question linking cells where a Lookup solution will not work and where the deficiencies of Autofill are exposed. Richard sees it as a matching question where a Lookup or Index/Match formula would work. Others see it differently again!

We need the OP. :-)

Thanks (0)
avatar
By PracticePartner
17th Nov 2014 18:02

Meanwhile ...

... we should all continue to invent solutions because you never know when they might come in handy!

Thanks (0)
pic
By jndavs
18th Nov 2014 08:32

For those who like macros

I know that the following pseudo code will not work as it stands, but something along the lines of

For Each a in Sheet1.Region1
  For Each b in Sheet2.Region2

  If a.value = b.value
  Copy Sheet1.Column a to Sheet2 Column b
  EndIf

  Next b
Next a

ought to do the trick.

Thanks (0)
avatar
By Richard Benner
18th Nov 2014 08:35

If we're playing that game... using the R1C1 formula style in VBA would solve it by referencing an offset column. A macro does appear to be overkill in this scenario though.

I'd be genuinely interested to see what this formula is being used for an some sample data, not sure if we're going to get it in this instance though.

Thanks (0)
avatar
By edhy
22nd Nov 2014 06:14

Select, Copy and Use Paste Link

Select the cells in Source sheet. To Select non-continuous cells / ranges use ctrl (control) key.

After selecting one cell / range, hold down ctri key and click / drag on other cells / ranges.

Then go to target sheet, select the cell where you want formula to start. Go to Paste Special dialog box and click Paste Link button. Done.

Regards

Zubair Edhy

 

Thanks (0)
Replying to lionofludesch:
pic
By jndavs
24th Nov 2014 11:16

You need a 'Copy' in there

Won’t this overwrite the structure of the target sheet?

Thanks (0)
avatar
By paulwakefield1
24th Nov 2014 11:37

Paste link

works well as long as there are not many cells to select. Maybe it's just me but, apart from the time aspect, I seem unable to select lots of individual cells without making a mistake and having to start again (although tidying up afterwards is sometimes a possibility). :-)

Thanks (0)