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.
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.
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.
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.
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.
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.
<=>?
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!
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.
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)
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?
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?
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...
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. :-)
Meanwhile ...
... we should all continue to invent solutions because you never know when they might come in handy!
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.
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.
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
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). :-)