Automatically show amounts from different sheets

[princessdy]

Hello! I hope someone can help me.

I work with a lot of numbers. Here is my dilemma:

I want to designate a reference A1 to any amount I work on coming from different worksheets. What I want is when I designate a reference as A1, the amount of the left of A1 should automatically show under A1, including the description for that amount. The corresponding reference (eg B1, B2) should also show under A1 schedule.

Here is the mini worksheet below. I only link them manually now. But sometimes I have a lot of adjustments I want to show in A1 but I miss some of them.
Right now, the columns are fixed -- meaning all amount will be coming from Column D of that worksheet and all description will be coming from Column B of that worksheet -- all going to A1 column D and B respectively.

Is there any way I can automate this?

Table template.xlsx
	B	C	D	E
2	[Sheet 1]
3	A1	Adjustments	$
4	Bank interest income		$ 300.00	B1
5	Bank charges		$ 200.00	B2
6	Total		$5,300.00
7
8	[Sheet 2]
9	B1	Interest income	$
10	Bank interest income		$ 300.00	A1
11	Loan interest income		$5,000.00
12	Total		$5,300.00
13
14	[Sheet 3]
15	B2	Expenses	$
16	Bank charges		$ 200.00
17	Service fee		$ 150.00	B2
18	Total		$5,300.00
Sheet2

Cell Formulas
Range	Formula
B4,D4	B4	=B10
B5,D5	B5	=B16
E4	E4	=B9
E5	E5	=B15
D6,D18,D12	D6	=SUM(D4:D5)
E10	E10	=B3
E17	E17	=B15

 

[SanjayGMusafir]

If the head you have shown under A1, B1 etc is the situation, you can populate some formula to pull a sum total of such heads from another sheets.

Normally you need such summary for taxation or analysis purpose. A Total of such heads can be pulled from another sheets using SUMIFS function.

Underneath is just one example from my personal sheet for you to understand.

All Records.xlsb
	L	M	N
25	Bank Charges		6,992
SGM

 

[princessdy]

If the head you have shown under A1, B1 etc is the situation, you can populate some formula to pull a sum total of such heads from another sheets.

Normally you need such summary for taxation or analysis purpose. A Total of such heads can be pulled from another sheets using SUMIFS function.

Underneath is just one example from my personal sheet for you to understand.

All Records.xlsb
	L	M	N
25	Bank Charges		6,992
SGM

Hello! Thanks for replying. I am familiar with SUMIFS function. But I have not tried the idea with my dilemma. I will try to understand your formula. But do you mind if I get a copy of the file?

I only use SUMIFS if I try to show the total of all A1 for example. But in this case, I want to show all A1 figures and descriptions first from all the separate worksheets. Then compute the total in A1 itself. In my example above, not sure why all Totals are wrong. Here is the corrected mini-sheet below.

Table template.xlsx
	B	C	D	E
2	[Sheet 1]
3	A1	Adjustments	$
4	Bank interest income		$ 300.00	B1
5	Bank charges		$ 200.00	B2
6	Total		$ 500.00
7
8	[Sheet 2]
9	B1	Interest income	$
10	Bank interest income		$ 300.00	A1
11	Loan interest income		$5,000.00
12	Total		$5,300.00
13
14	[Sheet 3]
15	B2	Expenses	$
16	Bank charges		$ 200.00
17	Service fee		$ 150.00	B2
18	Total		$ 350.00
Sheet2

Cell Formulas
Range	Formula
B4,D4	B4	=B10
B5,D5	B5	=B16
E4	E4	=B9
E5	E5	=B15
D6,D18,D12	D6	=SUM(D4:D5)
E10	E10	=B3
E17	E17	=B15

 

[SanjayGMusafir]

I want to show all A1 figures and descriptions first from all the separate worksheets

This is somewhat not that clear.

What I can understand is - That you want details of -
For example - Bank Interest Income in A1 to show and then it should give a Total of Bank Interest Income?

 

[princessdy]

Sorry I think my table was not showing properly what I want. Here you go. I want all amount with designated "A1" reference from anywhere in the Excel file gets summarised in the tab "A1" (ie in this case tab Sheet 1)

Table template.xlsx
	B	C	D	E
2	[Sheet 1]
3	A1	Adjustments	$
4	Bank interest income		$ 300.00	B1
5	Bank charges		$ 200.00	B2
6	Total		$ 500.00
7
8	[Sheet 2]
9	B1	Interest income	$
10	Bank interest income		$ 300.00	A1
11	Loan interest income		$5,000.00
12	Total		$5,300.00
13
14	[Sheet 3]
15	B2	Expenses	$
16	Bank charges		$ 200.00	A1
17	Service fee		$ 150.00
18	Total		$ 350.00
Sheet2

Cell Formulas
Range	Formula
B4,D4	B4	=B10
B5,D5	B5	=B16
E4	E4	=B9
E5	E5	=B15
D6,D18,D12	D6	=SUM(D4:D5)
E10	E10	=B3
E16	E16	=B3

 

[SanjayGMusafir]

I want all amount with designated "A1" reference from anywhere in the Excel file gets summarised in the tab "A1"

"Anywhere" Practically this is not feasible - You have to give Excel some particular range, location to pull data from.

For that you need to have a standardised format, data structure.

With Excel 365 in your hand, at best we can tell excel is to look at a location, if not found then the alternative location to pull data from.

 

[princessdy]

"Anywhere" Practically this is not feasible - You have to give Excel some particular range, location to pull data from.

For that you need to have a standardised format, data structure.

With Excel 365 in your hand, at best we can tell excel is to look at a location, if not found then the alternative location to pull data from.

Didn't receive any notification for your reply hence the late reply. I think I understand your point. But given my example above, all A1 will only be coming from Column E of all the other sheets and it should return Column B and D in Sheet 1. How should I be able to do that?