Match and copy paste values from different tabs into master sheet.

[VBA learner ITG]

I hope everyone is having a good day

I was wondering if there was a formula or VBA code that you can recommend or point me in the right direction for looking at data from different tabs and populating them into the master tab.

I have summary document and in column B4 - B23 is the tab Name which has been created within VBA and within the Summary Document In Row 3, Column C through to N are months.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Tab Title (Column B)[/TD]
[TD]January[/TD]
[/TR]
[TR]
[TD]Audi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ford[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


On each of the tabs i have the corresponding data in Column D Row 25 - 36 relating to Row 3, Column C through to N.

[TABLE="width: 500"]
<tbody>[TR]
[TD]January[/TD]
[TD]£2500[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD]£300000[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD]£20000[/TD]
[/TR]
</tbody>[/TABLE]

I would like your guidance and expertise to validate by the Tab name on the Summary document and pull into the summary tab the corresponding data from each tab against the tab name.

 

[cerfani]

just put this formula in C4 (under january right of Audi)...

=INDIRECT($B4&"!D"&COLUMN(Y1))

this will always reference sheet in column B and it will reference starting at D25 and as you drag right D25 will then go to 26, 27, etc so like transposing

i dont think your sheet name have spaces but if it does then i believe you need single quotes for the sheet name...

=INDIRECT("'"&$B4&"'!D"&COLUMN(Y1))

 

[VBA learner ITG]

Thank you for your assistance.

Your formula works and its definitely something i wouldn't have thought of in a million years.

I do have a final question though on the matter.

Is there a way of instead of transposing the data it can be done by looking at the month and pasting the data so it correlates.

for example if we are working on Q1 / Q2 dates like below

[TABLE="width: 1244"]
<colgroup><col span="2"><col><col><col span="3"><col><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[/TR]
[TR]
[TD="align: right"]250000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]700[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[cerfani]

No prob, yeah you can do that... it looks like your formula needs to not rely on a sorted and sequential month value... so if you want it to actually check the month and return the appropriate row based on column, you perform a lookup with index-match... (assumes sheet names have no blanks)

=INDEX(INDIRECT($B4&"!D25:D36"),MATCH(C$3,INDIRECT($B4&"!C25:C36"),0))

try that

 

[VBA learner ITG]

Thank you for your further assistance.

The revised formula works like a charm. Thank you for making my life easier.