Hi, I'm working on a project and I need some help with the address function. A few notes for reference.
Sheet I'm working in is called 'AvB'
The sheet I'm drawing data from is in the same workbook and is called 1) 'BS'
Background: The sheet called BS stands for balance sheet. On that sheet the months increase by 1 every time you move 1 column to the right. On 'AvB' I need to move 5 columns to the right to increase the month by 1. For example
Sheet name of table below: BS
For example this table below is very similar to the sheet I'm drawing numbers from. In this example I want the numbers residing in B2:J4 on the sheet BS and I want them to show up in the sheet AvB in their prospective named cells.
<tbody>
</tbody>
The problem is how AvB is constructed. Each month (Jan, Feb, etc) on this sheet requires 6 columns before the next month begins.
Sheet Name of Table Below: AvB
For example this table below is very similar to the sheet I'm pulling numbers into in terms of the column names and layout. My problem is that every time I get a new month (in this case let's assume it's Feb) I want to copy and paste columns B, C, D, E, F, & G to get the new Feb columns. I would like the column with Actual Feb'16 data to be populating from BS!C (Feb) however it's populating from BS!H (July). I'm pretty sure I can fix this by using the address function, but I just don't know how.
<tbody>
</tbody>
Many many thanks in advance
Sheet I'm working in is called 'AvB'
The sheet I'm drawing data from is in the same workbook and is called 1) 'BS'
Background: The sheet called BS stands for balance sheet. On that sheet the months increase by 1 every time you move 1 column to the right. On 'AvB' I need to move 5 columns to the right to increase the month by 1. For example
Sheet name of table below: BS
For example this table below is very similar to the sheet I'm drawing numbers from. In this example I want the numbers residing in B2:J4 on the sheet BS and I want them to show up in the sheet AvB in their prospective named cells.
Jan | Feb | Mar | Apr | May | June | July | Aug | Sept | |
Revenue | 1 | 3 | 3 | 4 | 5 | 3 | 4 | 5 | 7 |
Expense | 1 | 1 | 2 | 2 | 3 | 4 | 1 | 2 | 3 |
Profit | 0 | 2 | 1 | 2 | 2 | -1 | 3 | 3 | 4 |
<tbody>
</tbody>
The problem is how AvB is constructed. Each month (Jan, Feb, etc) on this sheet requires 6 columns before the next month begins.
Sheet Name of Table Below: AvB
For example this table below is very similar to the sheet I'm pulling numbers into in terms of the column names and layout. My problem is that every time I get a new month (in this case let's assume it's Feb) I want to copy and paste columns B, C, D, E, F, & G to get the new Feb columns. I would like the column with Actual Feb'16 data to be populating from BS!C (Feb) however it's populating from BS!H (July). I'm pretty sure I can fix this by using the address function, but I just don't know how.
<colgroup><col></colgroup><tbody> </tbody> |
<colgroup><col></colgroup><tbody> </tbody> |
<colgroup><col></colgroup><tbody> </tbody> |
<colgroup><col></colgroup><tbody> </tbody> |
<colgroup><col></colgroup><tbody> </tbody> | [Blank Cell] | Actual Feb'16 | Budget Feb'16 | Variance $ Feb'16 | Variance % Feb'16 | Notes | [Blank Cell] | |||||||||||
Revenue | =BS!B2 | =BS!C2 | ||||||||||||||||||||
Expense | =BS!B3 | =BS!C3 | ||||||||||||||||||||
Profit | =BS!B4 | =BS!C4 |
<tbody>
</tbody>
Many many thanks in advance