Forum Discussion
How to link data from a column on one tab to a column on another tab?
Devon500 I'm sorry, i was so focused on the 1st few columns of the attachment and looking for the non-existent macro I didn't notice the comments in columns G and H.
In general I would recommend just having another tab that keeps the data from each month and then 'this' tab would just perform a sum of the data from there. Attached is an example of what i mean.you could also just copy values from col I and paste (values only) onto col G each month.
I would not recommend the whole column but only the data on that column.
To make it universal, I added a Named Range called
GetDataFromColumn =OFFSET('My Data'!$A$1,0,0,COUNTA('My Data'!$A:$A),1)
Find attached an example.
='Sheet1'!A:A
would be faster and more efficient then forcing Excel to count and calculate the populated range and then only copy that range.
I would COMPLETELY agree with this method if the formula is complicated and especially if that range is used multiple times in the complicated formula. But in this request to just copy that column, I think it may actually be less efficient.
Anyone have time tests on this sort of thing?
By doing it ('Sheet1'!A:A), it will make reference to all 1,048,576 rows from column A.
Also if the cell is blank, it will return ZERO as value.
What he needs is the data.Juliano-Petrukio and @mtarler Is this what you would use - instead of a macro vba code- to transfer the data from one column to another?
I need to clear out one column, then add those amounts to another column.
How would your formula be altered so the figure will be added to the cells in the column vs just moving them
- Although I do a lot with macros/VBA, I actually recommend avoiding them when possible due to compatibility with others that have security settings that deny them or those using the web version of Excel.
So why are you transferring the data? is this some sort of data file that is continuously updated/overwritten by something else? Maybe you could give us a big picture so we aren't trying to guess. If it is a 1 time move then cut and paste, but I doubt that is the case. If the original file keeps getting appended then an external reference from 1 sheet to the other may be best. If the file get overwritten and you need to save the former data then a macro will probably be necessary.
As for adding the column to another column, will the 2 columns be the same length? will 1 always be longer than the other? Do you care if there are zeros filled down the 'empty' cells?
