Indirect("'"&...

[srizki]

I am using the following formula,

=INDIRECT("'"&A5&"'!"&"$A$1")

I want to adjust the formula, so instead of A1 it should have another indirect and match the column heading where the formula resides and get the number from the corresponding cell.

Thanks

 

[GreasySpot]

What is the other column?
Can you give an example or screenshot?

 

[srizki]

it is in row 4 and column could be L, M, N, or O. etc, that I will adjust, I just need to know how to add two indirect in one formula.

 

[C Moore]

Try CELL("address",A1)

 

[srizki]

ADVERTISEMENT

Thanks. but the formula has to match the heading.
But I will try the Address.

 

[GreasySpot]

I'm still having a hard time understanding what it is your trying to accomplish.

 

[srizki]

ADVERTISEMENT

I have sheet numbers in column A, expense description in column D. I want a formula, (INDIRECT most probably), that pick the sheet number from column A, expense from column D, (same row) and header of the column where the formula resides. (so sheet number and expense will change for each row, column heading will remain constant for each column). And pick the value from the sheet by matching column D expense to expense in column B and column heading in row 4.
So, I think there will be three INDIRECT and two match functions.

RC​	General & Administrations​	General/Oper​	G & A​	$ Actual​	Pipelines​	Terminals​	P&T​
015​	General & Administrations​	G & A​	015 G & A DIRECT		35%​	29%​	63%​
115​	General & Administrations​	G & A​	115 DIRECTOR'S CHARGES		34.5%​	28.8%​	63%​
117​	General & Administrations​	G & A​	117 ADMINISTRATION VACATION ACCRUAL
120​	General & Administrations​	G & A​	120 CORPORATE MGMT HOUSTON

<tbody> </tbody>

 

[srizki]

Something like,

=INDIRECT("'"&A32&"'!'015'!B5:B22"='Source Data'!D32)*(INDIRECT("'"&'Source Data'!A32&"'!'015'!C4:J4")='Source Data'!G4)

 

[GreasySpot]

I sincerely apologize but I'm still confused. Your 'indirect' statement doesn't match the data/chart you provided. It also doesn't match anything you mention in the text above it. And none of it matches anything else you provided in the first two posts.

I honestly don't mean to be an a$$hole but is there any way you can provide the actual file, as is and maybe with your expected results included.

 

[srizki]

Okay you see I have two tables, table one has 34.5% under Pipeline, and that is from the bottom part from RC 115. As you can clearly see that the table two has 34.5% for each of the charges under Pipeline.
I hope it is clear now, what INDIRECT formula you have tried, let me know so I can understand what you are thinking.
Let me know what part you did not understand.

Thanks

RC​	General & Administrations​	General/Oper​	G & A​	$ Actual​	Pipelines​	Terminals​	P&T​
015​	General & Administrations​	G & A​	015 G & A DIRECT		35%​	29%​	63%​
115​	General & Administrations​	G & A​	115 DIRECTOR'S CHARGES		34.5%​	28.8%​	63%​
117​	General & Administrations​	G & A​	117 ADMINISTRATION VACATION ACCRUAL
120​	General & Administrations​	G & A​	120 CORPORATE MGMT HOUSTON

<tbody> </tbody>

RC 115 DIRECTOR'S CHARGES​

ALLOCATION RATES INPUT BY RC​

CORPORATE RATE (COLUMN L) ALLOCATED TO SEGMENT (MASS FORMULA)​

34.5%​

28.8%​

2.0%​

2.4%​

4.2%​

28.1%​

​

100%​

2014 BUDGET UPDATE​

2014 UPDATE​

Responsibility Name

Expense

Pipelines​

Terminals​

Gas Storage​

BES​

BDL​

GM​

Corporate​

Total​

Pipelines​

Terminals​

Gas Storage​

BES​

BDL​

GM​

Corp.​

Total​

115 DIRECTOR'S CHARGES

PAYROLL REGULAR EXPENSE

100.0%​

100.0%​

34.5%​

28.8%​

2.0%​

2.4%​

4.2%​

28.1%​

-100%​

0.0%​

115 DIRECTOR'S CHARGES

PAYROLL CHARGED

100.0%​

100.0%​

34.5%​

28.8%​

2.0%​

2.4%​

4.2%​

28.1%​

-100%​

0.0%​

115 DIRECTOR'S CHARGES

TRAVEL EXPENSE

100.0%​

100.0%​

34.5%​

28.8%​

2.0%​

2.4%​

4.2%​

28.1%​

-100%​

0.0%​

115 DIRECTOR'S CHARGES

COMMUNICATION EXPENSE

100.0%​

100.0%​

34.5%​

28.8%​

2.0%​

2.4%​

4.2%​

28.1%​

-100%​

0.0%​

115 DIRECTOR'S CHARGES

OTHER CONTROLLABLE EXPENSE

100.0%​

100.0%​

34.5%​

28.8%​

2.0%​

2.4%​

4.2%​

28.1%​

-100%​

0.0%​

115 DIRECTOR'S CHARGES

PROFESSIONAL FEES EXPENSE

100.0%​

100.0%​

34.5%​

28.8%​

2.0%​

2.4%​

4.2%​

28.1%​

-100%​

0.0%​

115 DIRECTOR'S CHARGES

PAYROLL BENEFITS EXPENSE

100.0%​

100.0%​

34.5%​

28.8%​

2.0%​

2.4%​

4.2%​

28.1%​

-100%​

0.0%​

<tbody> </tbody>