Linking spreadsheet where rows are not aligned, but are when portions are hidden

[Brad]

I am working on linked spreadsheets where I need to copy a formula down a
column. The problem is that the rows in the source spreadsheet are five or
six rows apart for each row in the destination spreadsheet, and yes, it does
vary. It is about 15 rows of five row spacing and then a row of six row
spacing. In other words:

DestinationA1=SourceA1
DestinationA2=SourceA6
DestinationA3=SourceA11
....
DestinationA16=SourceA77

The fifteen rows is also not constant; sometimes it is 16 times. It is sort
of like leap year where about once a century a different adjustment is
needed for the partial day in the year.

This goes on for 10,000 rows or so, and is taking way too long to link by
hand. In order to help in the manual linking, I wrote a macro that hid the
unneeded rows, but I can't sort them without destroying other calculations
involved in the visible rows. Is there a way to copy an equations such that
is each time it is copied down in destination spreadsheet is move down on
VISIBLE row (actually five or six rows)? I have thought about adding dummy
rows to the destination sheet, coping down, and then deleting the unneeded
rows, but that is about as much work as hiding them which took quite awhile
last night with the help of a macro. At least with hiding, I can always
unhide when I find a mistake. Deleting rows is more of a hassle when done
wrong.

Brad

 

[Brad]

BTW, I am using Excel 2002 with XP Pro. I have access to Excel 2007, but
would need the links to work in 2002 when finished.

 

[AdamV]

A bit long winded, but bear with me:

In the source spreadsheet, add a column where each row contains the
formula =ROW().
So now each row has the row number on

Filter it for the non blanks (you sound like you already know how to do
this).
Copy the row numbers, go to your destination spreadsheet and paste
special > values to get a list of the row numbers.

Now you have two options.
First one, which is easier to amend if you need to simply by pasting in
a new list of row numbers, but involves more calculation time:

Instead of direct formulas (=[Sourceworkbook]Sheet1!A27), use the OFFSET
function and refer to your pasted list of numbers.
So, if your list is in B1:B30 (I'm only avoiding A to make the example
easier to follow and less confusing between A1 and A1) you could put
this in C1:
=OFFSET([Sourceworkbook]Sheet1!A1,B1,0)
This will return the value of the cell which is below the source A1 by
as many rows as specified in your pasted B1 (and zero columns to the
right). You probably need to adjust this if your source has a header
row, as your first item needs to be from row 2 (1 below A1, not 2 below
it. Put a -1 in to adjust for this, or more if needed:
=OFFSET([Sourceworkbook]Sheet1!A1,B1-1,0)
Once you have an expectec result returned, copy down column C

Second method, which ends up with a fixed set of references and might
therefore be easier to adapt later. Probably quicker to do but needs
slightly more work to re-do if the list changes later:
Again, assume your pasted list is in B1:B30.
In C1 use a formula to build up your reference as text inside quotes:
="[Sourceworkbook]Sheet1!A"&B1
Now copy that down as far as need in column C
Copy the cells from C and use paste special values into column D (or
over the top of C if you want to).
Hmmm... problem is they now show the formulas not the results. You
*could* press F2, Enter, F2, Enter all the way down, but from what you
have said there are lots of them.
Trick: select the cells in column C which currently show formulas. Use
the Text to Columns wizard (from the data menu in 2002, I think). accept
all the defaults, and you should end up pasting back in a list of the
same formulas, but this time it will "recognise" them and deliver
results, not text.

Hope this helps
Adam

 

[Brad]

Adam,

Thank you for the help. I ended up last night manually linking the two
spreadsheets one cell at a time for about 2,000 cells. What a PITA! Now
that I see your answer, I am interested in learning some new (to me) ways to
do this.

I did not know how to filter before, but just read the help section on it in
Excel 2007. I think I see how to do it and got your process to work on a
sample file, but my filter criteria is a bit of a problem. I have traffic
data in 59.2 second increments thanks to a traffic counter malfunction. The
counter's software would normally summarize it in five, 15, or 60 minute
increments for me. It spit it out in even one minute increments to excel,
but I had to adjust the time stamps to remove the extra 2 hours and 20
minutes in the week that the counter spit out to the Excel file. (It was
also in the PDF and on the screen. I know the data was off since I was
driving with it in the trunk two hours away when the counter said it quit,
and I checked counter clock with my mobile phone at the start and end of the
count. It was accurate.) The source file has an original time stamp on the
minute and an adjusted time stamp that varies. I want to filter in five
minute increments, so the cells where the time in minutes is equally
divisible by is shown and the others hidden, but the time might be 4:25:22
instead of 4:25:00. Any idea how to set up the filter to even get it to
filter in even five minute increments as well as on the five minute with odd
seconds?

Thank you.
Brad

A bit long winded, but bear with me:

In the source spreadsheet, add a column where each row contains the
formula =ROW().
So now each row has the row number on

Filter it for the non blanks (you sound like you already know how to do
this).
Copy the row numbers, go to your destination spreadsheet and paste special

values to get a list of the row numbers.

Now you have two options.
First one, which is easier to amend if you need to simply by pasting in a
new list of row numbers, but involves more calculation time:

Instead of direct formulas (=[Sourceworkbook]Sheet1!A27), use the OFFSET
function and refer to your pasted list of numbers.
So, if your list is in B1:B30 (I'm only avoiding A to make the example
easier to follow and less confusing between A1 and A1) you could put this
in C1:
=OFFSET([Sourceworkbook]Sheet1!A1,B1,0)
This will return the value of the cell which is below the source A1 by as
many rows as specified in your pasted B1 (and zero columns to the right).
You probably need to adjust this if your source has a header row, as your
first item needs to be from row 2 (1 below A1, not 2 below it. Put a -1 in
to adjust for this, or more if needed:
=OFFSET([Sourceworkbook]Sheet1!A1,B1-1,0)
Once you have an expectec result returned, copy down column C

Second method, which ends up with a fixed set of references and might
therefore be easier to adapt later. Probably quicker to do but needs
slightly more work to re-do if the list changes later:
Again, assume your pasted list is in B1:B30.
In C1 use a formula to build up your reference as text inside quotes:
="[Sourceworkbook]Sheet1!A"&B1
Now copy that down as far as need in column C
Copy the cells from C and use paste special values into column D (or over
the top of C if you want to).
Hmmm... problem is they now show the formulas not the results. You *could*
press F2, Enter, F2, Enter all the way down, but from what you have said
there are lots of them.
Trick: select the cells in column C which currently show formulas. Use the
Text to Columns wizard (from the data menu in 2002, I think). accept all
the defaults, and you should end up pasting back in a list of the same
formulas, but this time it will "recognise" them and deliver results, not
text.

Hope this helps
Adam

BTW, I am using Excel 2002 with XP Pro. I have access to Excel 2007, but
would need the links to work in 2002 when finished.