Consolidate Data Automatically

[PRADEEPSWALSE]

Hi,

I need help in consolidating data in excel.

In my case, Consolidated data is not updating if I delete or modify input sheet.

Case 1: If I newly add information in cell ‘B5’ in ‘Input sheet’ then in ‘Consolidated Data’ sheet, cell ‘D7’ is not updating automatically. Cell ‘D7’ shall show value as ‘4000’ because cell ‘B3’ and ‘B5’ are same.

Case 2: If I modify cell ‘B4’ same as ‘B2’ in ‘Input sheet’ then in ‘Consolidated Data’ sheet, cell ‘D5’ is not updating automatically.

Request to please suggest solution for this problem.

I have not found option to attach my sample excel file. So I have attached snap shots of both sheets.

Regards,
Pradeep S. Walse

 

[MADEASH]

Do you have your Calculation Options set to AUTOMATIC ?

 

[GraH]

Hi Pradeep,

Why not use an alternative like SUMIF(S), pivot table or Power Query? If it is 1 input sheet then I'd use a table to store the data and a pivot to "consolidate".

It data can be on multiple sheets, I'd use PQ to combine all sheets in a single table. Many great and easy to follow tutorials are available on the web.

Tip, to share data/worksheets, you can use the XL2BB add-on (if possible). Updating your profile with your Office version and OS also helps us in providing better help. e.g. PQ is only for windows users and for Office 2010 or later.

 

[PRADEEPSWALSE]

Do you have your Calculation Options set to AUTOMATIC ?

Yes.

 

[PRADEEPSWALSE]

hI

Hi Pradeep,

Why not use an alternative like SUMIF(S), pivot table or Power Query? If it is 1 input sheet then I'd use a table to store the data and a pivot to "consolidate".
View attachment 14575

It data can be on multiple sheets, I'd use PQ to combine all sheets in a single table. Many great and easy to follow tutorials are available on the web.

Tip, to share data/worksheets, you can use the XL2BB add-on (if possible). Updating your profile with your Office version and OS also helps us in providing better help. e.g. PQ is only for windows users and for Office 2010 or later.

HI

Hi Pradeep,

Why not use an alternative like SUMIF(S), pivot table or Power Query? If it is 1 input sheet then I'd use a table to store the data and a pivot to "consolidate".
View attachment 14575

It data can be on multiple sheets, I'd use PQ to combine all sheets in a single table. Many great and easy to follow tutorials are available on the web.

Tip, to share data/worksheets, you can use the XL2BB add-on (if possible). Updating your profile with your Office version and OS also helps us in providing better help. e.g. PQ is only for windows users and for Office 2010 or later.

Hi,

Thanks for suggestion.
I have used PIVOT table. It resolve my problem.
I have summary (Pivot) on another sheet and input in first sheet. If I change input data on first sheet, Pivot table is not updating automatically.
I searched it on google and tried with these code but it is not working in my case. After adding this code, I saved this file with .xlsm extension.

Attaching sample file for ready reference by using XL2BB (Doing first time).

Test_123_MACRO.xlsm
	C
17
Sheet1

Can you please help me for this issue?

Regards,
Pradeep