VLOOKUP/Indirect Formula

[Katich]

Hello,

I'm trying to do a lookup that will reference different tabs in spreadsheet. Currently i only have 1 tab in the spreadsheet but it will have multiple. On the Variance v2 tab, i want cell B7 to lookup at the "3+9" which will be the same name of the other tab. I want it to go to 1Q23 column in tab 3+9 and find the cells in the formula i currently have and perform that calculation. Next month when i have a 4+8 tab, i want it to look at that. I'll have to create a drop down in B2 i know that. Any help will be appreciated

Thank you!

Book3
	A	B	C	D	E	F
1
2		3+9
3	Income Statement	1Q23	2Q23	3Q23	4Q23	FY23
4
5	Revenue:
6
7	Building A	0.4	0.4	0.5	0.7	2.0
8
9	Total Revenue	0.4	0.4	0.5	0.7	2.0
10
11	Operating Expense:
12
13	Building A	0.4	0.4	0.3	0.4	1.6
14
15	Total Operating Expense	0.4	0.4	0.3	0.4	1.6
16
17	Adjusted EBITDA	0.0	(0.0)	0.2	0.2	0.4
Variance v2

Cell Formulas
Range	Formula
B7:F7	B7	=('3+9'!S31+'3+9'!S50)/1000000
B9:F9,B15:F15	B9	=SUM(B7:B8)
B13:F13	B13	=('3+9'!S46+'3+9'!S54)/1000000
B17:F17	B17	=B9-B15

Book3
	S	T	U	V	W
1	1Q23	2Q23	3Q23	4Q23	FY23
2
3
4	$ 3,120	$ 4,680	$ 4,680	$ 4,680	$ 17,161
5	$ -	$ 5,521	$ 16,564	$ 16,564	$ 38,649
6	$ -	$ -	$ -	$ -	$ -
7	$ -	$ -	$ -	$ -	$ -
8	$ -	$ -	$ 6,547	$ 9,820	$ 16,367
9	$ -	$ -	$ 1,396	$ 4,189	$ 5,586
10	$ -	$ -	$ 14,411	$ 43,232	$ 57,642
11	$ -	$ -	$ 9,291	$ 27,874	$ 37,165
12	$ 14,250	$ 14,250	$ 14,250	$ 14,250	$ 57,000
13	$ -	$ -	$ -	$ 55,868	$ 55,868
14	$ -	$ -	$ -	$ -	$ -
15	$ 7,750	$ 7,750	$ 7,750	$ 7,750	$ 31,000
16
17	$ 25,120	$ 32,201	$ 74,889	$ 184,226	$ 316,436
18
19	$ -	$ 8,333	$ 12,500	$ 12,500	$ 33,333
20
21	$ 25,120	$ 40,535	$ 87,389	$ 196,726	$ 349,770
22
23	$ 8,714	$ 12,312	$ 28,655	$ 68,536	$ 118,217
24	$ 2,430	$ 2,532	$ 4,010	$ 8,784	$ 17,756
25	$ 883	$ 2,150	$ 9,453	$ 21,948	$ 34,434
26	$ 83	$ 125	$ 125	$ 125	$ 458
27	$ 500	$ -	$ -	$ -	$ 500
28
29	$ 12,611	$ 17,119	$ 42,242	$ 99,393	$ 171,366
30
31	$ 37,731	$ 57,654	$ 129,631	$ 296,119	$ 521,135
32
33	$ 71,000	$ 104,615	$ 104,615	$ 104,615	$ 384,844
34	$ -	$ 12,187	$ 12,187	$ 12,187	$ 36,561
35	$ 303,430	$ 122,040	$ -	$ 122,040	$ 547,510
36	$ -	$ -	$ -	$ -	$ -
37	$ 21,300	$ 31,950	$ 31,950	$ 31,950	$ 117,150
38	$ -	$ -	$ -	$ -	$ -
39	$ 1,488	$ 1,488	$ 1,488	$ 1,488	$ 5,953
40	$ 2,344	$ 2,057	$ 2,011	$ 2,004	$ 8,416
41	$ -	$ -	$ -	$ -	$ -
42	$ -	$ -	$ -	$ -	$ -
43	$ -	$ 1,594	$ 1,044	$ 1,594	$ 4,232
44	$ -	$ 1,216	$ 2,622	$ 5,902	$ 9,739
45
46	$ 399,562	$ 277,147	$ 155,916	$ 281,780	$ 1,114,406
47
48	########	$ (219,494)	$ (26,285)	$ 14,339	$ (593,270)
49
50	$ 367,500	$ 367,500	$ 367,500	$ 367,500	$ 1,470,000
51
52	$ 5,669	$ 148,006	$ 341,215	$ 381,839	$ 876,730
53
54	$ -	$ 163,016	$ 163,016	$ 163,016	$ 489,048
55
56	$ 5,669	$ (15,010)	$ 178,199	$ 218,823	$ 387,682
3+9

Cell Formulas
Range	Formula
S4:S15,S56,S54,S52,S50,S48,S33:S44,S31,S29,S23:S27,S19	S4	=SUM(G4:I4)
T4:T15,T56,T54,T52,T50,T48,T33:T44,T31,T29,T23:T27,T19	T4	=SUM(J4:L4)
U4:U15,U56,U54,U52,U50,U48,U33:U44,U31,U29,U23:U27,U19	U4	=SUM(M4:O4)
V4:V15,V56,V54,V52,V50,V48,V33:V44,V31,V29,V23:V27,V19	V4	=SUM(P4:R4)
W4:W15,W56,W54,W52,W50,W48,W33:W44,W31,W29,W23:W27,W19	W4	=SUM(S4:V4)
S17:W17	S17	=+SUM(S4:S15)
S21:W21	S21	=SUM(S17:S19)
S46:W46	S46	=SUM(S33:S44)

 

[Arturthemaster]

What you want to lookup?

 

[awoohaw]

is it a requirement that you have separate tabs for 4+8 and 3+9?

This looks like you'll have a very big worksheet with a lot of INDIRECT functions which will slow your processing down.

Have you considered adding one column in your data sheet for the sheet category 3+9 or 4+8, etc. and the values in the column be 3+9 or 4+8? It would make your lookup much easier.

 

[Arturthemaster]

I think you have to create a data base with the sums and the periods to excel look up, but you have to create with only one number like
3+9= 12
4+8= 12

 

[awoohaw]

another way to approach it is with power query or power pivot. But, then you may need to take this discussion to the Power Forum.

 

[Katich]

What you want to lookup?

On the Variance v2 tab, the revenue amount is pulling cells S31 and S50 from tab 3+9 and the expenses are pulling S46 and S54 from tab 3+9 for Q123. Then just shift columns over for each quarter.

 

[Katich]

is it a requirement that you have separate tabs for 4+8 and 3+9?

This looks like you'll have a very big worksheet with a lot of INDIRECT functions which will slow your processing down.

Have you considered adding one column in your data sheet for the sheet category 3+9 or 4+8, etc. and the values in the column be 3+9 or 4+8? It would make your lookup much easier.

it's not a requirement but makes it easier (now) since there is alot of data on each tab. Each 3+9 or 4+8 tab will have data for Jan-Dec and each month those numbers will change.

 

[awoohaw]

Actually @Katich ,... it makes it much more difficult and will kill your processing time when the file gets big.
The rule of thumb I use:
if it is data.... make it as flat a data set as possible.... build reports from that.

if you had 365 you could use VSTACK and stack worksheet ranges, provided you had a column on each worksheet that identified the worksheet source
So: COLUMNA in 9+3 would have header of SOURCE, and all items in Column A2:A## would be '9+3, and similar for '8+4 on that tab.

 

[Katich]

Actually @Katich ,... it makes it much more difficult and will kill your processing time when the file gets big.
The rule of thumb I use:
if it is data.... make it as flat a data set as possible.... build reports from that.

if you had 365 you could use VSTACK and stack worksheet ranges, provided you had a column on each worksheet that identified the worksheet source
So: COLUMNA in 9+3 would have header of SOURCE, and all items in Column A2:A## would be '9+3, and similar for '8+4 on that tab.

ok, that makes sense on your rule of thumb.

Well, i do have 365 but not everyone in my company sometimes myself use it so would VSTACK work if someone else opened the file that didn't have 365? I've never used it nor really worked in 365 as i just haven't gotten used to working in there.

 

[awoohaw]

it would work if they opened it in Excel WEB version, but unsure of 2019 and earlier.

but, the first solution i suggest will work on any, but you need to do work to consolidate the worksheets.