Modified transpose function OR macro?

**froffel** · 10-14-2009, 10:27 AM

Hello & thanks for checking this out...

I am trying to perform the following, but am unsure if a macro is needed, or if there is a way to do it by possibly modifying the transpose function. Here are the steps required:

1 - copy a selected range of cells from a single row in one worksheet (i.e. D3:AE3)
2 - paste the data from those cells into a selected range of cells from a single column (i.e. A1:A14) in a separate worksheet BUT skip the cells from D3:AE3 that were blank instead of having them create blank cells in the column of the target worksheet
3 - Need to maintain a reference back to the original row of cells so that if their values change (i.e. D3 from source), the corresponding values in the new column (i.e. A1 in target) are auto updated

Would be happy to provide further clarification if necessary. Thanks very much in advance for any assistance you can provide.

Cheers,
Fredder

**StephenR** · 10-14-2009, 10:37 AM

So if D3 is blank and then has a value put it in it, the target cells should be shifted down one? Perhaps attach a sample workbook to clarify?

**froffel** · 10-14-2009, 10:44 AM

I understand what you are saying, but no, that would not be necessary. As long as the new cells have a reference back to the originals that is sufficient. (i.e. see sample spreadsheet attached: if value in C1 from worksheet 'source' changes, that change should automatically be reflected in A2 from worksheet 'target'.)

Thanks,
Fredder

**StephenR** · 10-14-2009, 11:08 AM

If they are always alternately filled/blank, then this in A1 and down:

=OFFSET(source!$A$1,0,2*(ROW()-1),1,1)

If the pattern is irregular, then I think you'd need code.

**froffel** · 10-14-2009, 11:14 AM

they are always alternately filled/blank, so this does the trick!
the only thing is that my cell positions are different from the example, but appears i can modify the cell references from the function quite easily.
thanks heaps!!
fredder