INDIRECT Formula pulling from different tabs

exx52w

New Member
Joined
May 8, 2018
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi,
I recently replaced someone at work and I'm trying to improve his spreadsheet but have no clue on how he derived his very complicated formula. This is ultra complicated to me.

=IF(F44<$D$10,0,IF(AND(F44>=$W$18,M44="Behavioural Term"),VLOOKUP(H44,INDIRECT(K44&"!$B$128:$CK$249"),MATCH('1 | OBU FTP Calibration'!F44,INDIRECT(K44&"!$B$128:$XFD$128"),1),1),VLOOKUP(H44,INDIRECT(K44&"!$B$3:$CK$124"),MATCH('1 | OBU FTP Calibration'!F44,INDIRECT(K44&"!$B$3:$XFD$3"),1),1)))

[TABLE="width: 500"]
<tbody>[TR]
[TD]01/07/2012 (D10)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Start Date[/TD]
[TD]Months[/TD]
[TD]Currency[/TD]
[TD]Treatment[/TD]
[TD]Test[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]10/12/2015 (F44)[/TD]
[TD]43 (H44)[/TD]
[TD]AUD (K44)[/TD]
[TD]Behavioural (M44)[/TD]
[TD]0% (W18)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

*I have 2 treatments, contractual, behavioural
*Different currencies are located in different tabs so tab 1 would have USD, tab 2 EUR, tab 3 JPY etc
*I would like to remove "Test" from the formula

Aim:
Currencies have two types of rates denoted in % for behavioural and contractual. Each rate is denoted % and categorised in months from 0 to 120 months (incremental of 1). Example: using the table above, AUD would have a rate of 1.02% for a 43 month denoted as 42.5 & 43.5

Please help - I need to come up with a formula that allows me to pull the correct rates based on the treatment & months & currency.

Thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Example: using the table above, AUD would have a rate of 1.02% for a 43 month denoted as 42.5 & 43.5

Thanks

Sorry what I meant there is that my currency tabs don’t have the actual 43 value but 42.5-43.5
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top