Address Function to increase reference cell by 1

J

jaspncr

New Member
Joined
Jun 6, 2011
Messages
5
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.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]Aug[/TD]
[TD]Sept[/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Profit[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]-1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


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.
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Actual[/TD]
[/TR]
[TR]
[TD]Jan'16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Budget[/TD]
[/TR]
[TR]
[TD]Jan'16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Variance $[/TD]
[/TR]
[TR]
[TD]Jan'16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 87"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Variance %[/TD]
[/TR]
[TR]
[TD]Jan'16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Notes[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][Blank Cell][/TD]
[TD]Actual
Feb'16[/TD]
[TD]Budget
Feb'16[/TD]
[TD]Variance $
Feb'16[/TD]
[TD]Variance %
Feb'16[/TD]
[TD]Notes[/TD]
[TD][Blank Cell][/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD]=BS!B2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=BS!C2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Expense[/TD]
[TD]=BS!B3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=BS!C3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Profit[/TD]
[TD]=BS!B4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=BS!C4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Many many thanks in advance
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Hi, how about something like this to retrieve the values based on the text in the header (I'm assuming the Month and Year are on a row by themselves):


Excel 2013/2016
ABCDEFGHIJKLM
1ActualBudgetVariance $Variance %NotesActualBudgetVariance $Variance %Notes[Blank Cell]
2Jan'16Jan'16Jan'16Jan'16Feb'16Feb'16Feb'16Feb'16
3
4Revenue13
5Expense11
6Profit02
Sheet2
Cell Formulas
RangeFormula
B4=INDEX(BS!$B:$M,MATCH($A4,BS!$A:$A,0),MATCH(LEFT(B$2,3),BS!$B$1:$M$1,0))



Excel 2013/2016
ABCDEFGHIJKLM
1JanFebMarAprMayJuneJulyAugSepOctNovDec
2Revenue133453457
3Expense112234123
4Profit02122-1334
BS
 
Upvote 0
Hi FormR,

I tried the formula you listed but got a #N/A. I'm sure it's user error. I've posted two links to screenshots.

Screenshot 1 - AvB Sheet - Sheet where I'm using the formula to pull from the other sheet
https://photos.app.goo.gl/Ls8ufbVCnLQWvBKn1

Screenshot 2 - BS Sheet - Sheet where I'm pulling the data from
https://photos.app.goo.gl/OjnlTOoRHTHmhJql2

Not sure where I'm going wrong but I think these two images should provide all the information to identify my error?

Many Thanks in Advance
Jeff


FormR said:
Hi, how about something like this to retrieve the values based on the text in the header (I'm assuming the Month and Year are on a row by themselves):

Excel 2013/2016
ABCDEFGHIJKLM
ActualBudgetVariance $Variance %NotesActualBudgetVariance $Variance %Notes[Blank Cell]
Jan'16Jan'16Jan'16Jan'16Feb'16Feb'16Feb'16Feb'16
Revenue
Expense
Profit

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B4[/TH]
[TD="align: left"]=INDEX(BS!$B:$M,MATCH($A4,BS!$A:$A,0),MATCH(LEFT(B$2,3),BS!$B$1:$M$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2013/2016
ABCDEFGHIJKLM

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Jan[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Feb[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Mar[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Apr[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]May[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]June[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]July[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Aug[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Sep[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Oct[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Nov[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Dec[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Revenue[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Expense[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Profit[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]-1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
BS​
Click to expand...
 
Upvote 0
jaspncr said:
Not sure where I'm going wrong but I think these two images should provide all the information to identify my error?
Click to expand...

Hi Jeff - looks like the month labels in the BS sheet match the labels in the AVB sheet, i.e. they also include the year (which is different to the sample data in the 1st post).

Try removing the LEFT() function, i.e. using the layout post#2

=INDEX(BS!$B:$M,MATCH($A4,BS!$A:$A,0),MATCH(B$2,BS!$B$1:$M$1,0))
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
112
Peter_SSs
Peter_SSs
HansM
  • Question Question
Looking for a specific number in a cell that can have multiple numbers separated by a comma (Using Mac Excel)
Replies
5
Views
312
Jeffrey Mahoney
Jeffrey Mahoney
S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
229
Fluff
Fluff
J
  • Question Question
adding cells based on criteria across several columns
Replies
5
Views
999
Cubist
Cubist
F
  • Solved
Look up monthly budget/actual based on input month
Replies
2
Views
201
fishmonger
F

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top