# Indirect("'"&...

#### srizki

##### Well-known Member
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
What is the other column?
Can you give an example or screenshot?

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.

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

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

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>

Something like,

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

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.

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>

Replies
3
Views
785
Replies
7
Views
662
Replies
3
Views
253
Replies
4
Views
782
Replies
3
Views
1K

1,196,152
Messages
6,013,752
Members
441,781
Latest member
Gian Carlos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back