Replace tab value inside formula with cell value

**Haywire** · 02-10-2010, 11:26 AM

Hi all,

It would be extremely helpful if anybody can help me with this problem, which again would be obvious but for the life of me can not figure it out. I also tried searching the Forum but an error with "run out of memory" occurs, sorry if this has been asked before.

I need to replace or substitute a tab reference value inside a formula to display the value of the cell in the validation drop down list. I have attached a file to explain the situation better.

Please help!
Thanks

**martindwilson** · 02-10-2010, 12:05 PM

in b5 =INDIRECT($B$3&"!B2") in c5 =INDIRECT($B$3&"!C2") and so on

**DonkeyOte** · 02-10-2010, 12:22 PM

If the cell references are to increment etc as outlined you want to use INDIRECT in conjunction with ADDRESS, eg:

B5: =INDIRECT(ADDRESS(2,COLUMNS($A5:B5),,,$B$3))
copied across to F5

**Haywire** · 02-10-2010, 01:45 PM

Originally Posted by martindwilson

in b5 =INDIRECT($B$3&"!B2") in c5 =INDIRECT($B$3&"!C2") and so on

Thanks, this works perfectly on that example which hasn't got a formula, I tried to insert =INDIRECT($B$3&"!B2") in the "Feb" values in the following example and cant get it to work.

How do I apply =INDIRECT($B$3&"!B2") in the following "Feb" values, =SUMIFS(Feb!$AB$7:$AB$277,Feb!$AF$7:$AF$277,Feb!$B5,Feb!$B$7:$B$277,Feb!B$2)+IF(Feb!$B5="7705-UG00",$AL$229*B$4,0)

What I'm actually asking is for the syntax to merge the two formulas.