Create columns without Absolute formula, copying problem

[Ebes1099]

I'm running into an interesting problem.

I am writing a macro to copy data from one workbook to another. All of the information to be copied is compiled into one main worksheet in my first workbook. There are columns that have an Offset formula which reference another cell on the worksheet for the "column" offset value. I can't have these as absolute formulas because the columns are dynamically created (there could be 1 column or 5 or 10 or whatever). Down the column some of the cells have this offset formula and some have formulas that reference cells above in the column, so I can't make those absolute or they'll get messed up when I copy into the new workbook.

Right now, the Offset formulas are getting messed up because the column offset value which is referencing a cell on the worksheet gets shifted when I copy into the new workbook. Sometimes so far left that it now no longer is a valid cell and I get a #REF error.

I know if I make them absolute references, they will copy correctly, but then when I am dynamically creating the number of columns in the first workbook it won't work when I fillRight on the formulas. And I can't just blanket make all formulas absolute after I create the number of columns I need because some of the formulas can't be absolute references.

Does anyone have a good solution for this?

 

[SkipVought]

...and the formula is?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

Here's an example of one of the formulas in the column. We'll say this is Column V. I have maybe 20 rows of formulas, some have an Offset formula which references another sheet. And some have just a regular formula like V12/V15.

=OFFSET(KeyInfo!$BO$8,'Doc Tbls'!C$32,0)

Cell C32 on the Doc Tbls tab is just a number to reference how many columns to shift over. D32, E32, F32, etc are the same thing, incremental by 1.

So I can't make that absolute because when I copy that formula to the right, I need the column to shift so the offset grabs the right value.

The sheet I'm copying to will not have these cells in the same row/column so I need the formulas like V12/V15 to be relative so they copy correctly, but the Offset formulas to be absolute otherwise they give me a #ref error.

 

[SkipVought]

Without understanding your workbook/worksheet structure, it's difficult to understand what your intent is.

The offset() function is designed to return a range of cells. The way you are using it is as it you were using the index() function, that returns one value ( you could substitute index for offset and it would do the same thing!

I think that you have a design deficiency. Suppose you explain the structure of KeyInfo & Doc Tbls. Doing direct references like you're doing, is not a particularly sound technique to get data from one sheet to another.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

My bad. I erred referencing the INDEX() function.

If your offset formula in the same row is to reference a corresponding row in the column following, then you could use something like this...
[tt]
OFFSET(KeyInfo!$BO$8,'Doc Tbls'!C$32+row()-[some constant],0)
[/tt]
Here's the effect: as you copy this formula down, the value retuned from C$32 is constant, yes, but you're adding a varaible based on ROW()-somthing, where the something equals the initial row number value. so the initial difference equals 0, then nex tor equzls 1, then 2 etc. Will that accomplish the desired result?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

Well, I didn't originally create this workbook, I'm just being asked to do something with it. So I'm not exactly sure why they are using Offset formulas to only return a single cell.

The KeyInfo tab is a table with a row for each item and the attributes of that item go across.

The DocTbls tab is grabbing some of that information, but now instead of rows for each item, it is using columns. There could be any number of items, so to start there is 1 column in DocTbl. When I run the macro, I count how many rows are in KeyInfo and then paste the formulas from my single column in DocTbl 'x' number of rows to the right. This is where the Offset formula comes into play. Since I'm copying to the right, but the reference to KeyInfo needs to go down a row, just copying some other formula over won't work. The Offset formula needs that 2nd argument to increase by 1 each column. That's where the reference to another cell on the DocTbl worksheet is used. There's values in row 32 that count up by 1. When the OFFSET(KeyInfo!$BO$8,'Doc Tbls'!C$32,0) gets copied over, the C$32 becomes D$32, etc. That specifies how many rows to go down on the KeyInfo tab.

I'm not copying this formula down at all (so I'm not sure why row 32 is absolute). These formulas are created once, and saved as a template. Each user copies this workbook and the Macro copies the formula to the right for the correct number of items.

 

[SkipVought]

Row 32 is absolute because that's how the formula is designed.

If you have a formula that is designed to do one thing, then it must be changed appropriately in order to do some other thing.

But what you're saying does not make any sense to me, such as "'x' number of rows to the right". ROWS? To the right??? Do you really mean COLUMNS to the right?

So I'm really confused now!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Bottom line: if you cannot perform a manual process on a sheet, then you cannot accomplish that in a macro, without changing something appropriately, or doing something entirely different.

So it seems that you cannot do the former. Therefore you must do the latter. But I don't understand how your doc table are used. You have not explained your environment well enough.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ebes1099]

I'm going to attack this a different way. Since I'm copying to a different book and I was going to break links anyway, I am just going to copy those formulas as values. I'll start a new thread with a question I have about copying non-contiguous cells and pasting with the same spacing.

Thanks for trying to help me with this crazy problem.