Worksheet Consolidation

[n0112388]

Hi,

I am trying to consolidate monthly account information from several worksheets into a single summary worksheet. I defined names for the data in each worksheet (i.e. _BCC1, _BCC2, etc) and I created the following formula to work for the Jan column in my summary worksheet.

(=IFERROR(VLOOKUP(D17,_BCC1,2,0),0)+IFERROR(VLOOKUP(D17,_BCC2,2,0),0)+IFERROR(VLOOKUP(D17,_BCC3,2,0),0)+
IFERROR(VLOOKUP(D17,_BCC4,2,0),0)+IFERROR(VLOOKUP(D17,_BCC5,2,0),0)+IFERROR(VLOOKUP(D17,_BCC6,2,0),0)+IFERROR(VLOOKUP(D17,_BCC7,2,0),0)

Jan is column_index : 2 and now I want to drag across to replicate this formula for the other months but I don't want to manually change the column index reference to 3, 4, 5 and so on. I also want to note that the sheets do not contain the same number of accounts, so one account may not exist in a worksheet.

I think using some sort of sumif index/match combination could work but since I am just starting to learn about index/match I am looking for some help.

Thank you in advance!

 

[ChrisM]

For the column count in your vlookup you can use the Column() function, which will return the column # of the current cell. So in your example you want column #2. Say that your formula is in cell A1. Column() would return '1', you want '2', so the formula would be Column() + 1

=Vlookup(D17, _BCC1, Column()+1,0)

**Important note: If you insert/delete columns from your summary worksheet you will throw these formulas off

 

[n0112388]

For the column count in your vlookup you can use the Column() function, which will return the column # of the current cell. So in your example you want column #2. Say that your formula is in cell A1. Column() would return '1', you want '2', so the formula would be Column() + 1

=Vlookup(D17, _BCC1, Column()+1,0)

**Important note: If you insert/delete columns from your summary worksheet you will throw these formulas off

@ChrisM- Thank you for the reply. I tried the column() and it works but you are right I can throw it my formulas off by adding /deleting columns and that concerns me since I will be sharing this workbook with my colleagues. Do you think there is a better way to do this?

thanks!

 

[ChrisM]

I'm sure there is a better way out there, but one alternative is to use a hidden helper row, autofilled with numbers 1 thru xxx. then point your vlookup column # to the corresponding value in the hidden row.