Problem with writing a formula

[hunin]

Greetings,

I'm wondering how I'm going to write this formula to solve a problem that occurs every month that takes a lot of time.

If L2 contains "stage 1" and M2 "pre-sat" it will show the value from sheet "input c6" ,if L2 contains "stage 2" and M2 "pre sat" it will show input from "sheet d6", if L2 contains "stage 3" and M2 "pre-sat" it will show input from "Sheet e6", if L2 contains "stage 1" and M2 "post-sat" it will show input from "sheet c6" , if L2 contains "stage 2" and M2 "post-sat" it will show input from "sheet d6", if L2 contains "stage 3" and M2 "post sat" it will show input from "sheet e6".

So, I have a sheet that is named "Input" where I want to put numbers into and when I do so the formula shall recognize this change and automatically check the criterias above and retrieve the correct value.

Thank you for your help and feel free to raise any questions if something is unclear.

Best regards
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]Stage 1[/TD]
[TD]Pre-sat[/TD]
[TD]This cell will retrieve its information from another sheet by using the formula[/TD]
[/TR]
[TR]
[TD]Stage 2[/TD]
[TD]Post-Sat[/TD]
[TD]This cell will retrieve its information from another sheet by using the formula[/TD]
[/TR]
[TR]
[TD]Stage 3[/TD]
[TD]Pre-sat[/TD]
[TD]This cell will retrieve its information from another sheet by using the formula[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[offthelip]

this should give you the idea of how to do it, i haven't taken into account the pre sat and post sat i hadn't noticed that condition when i started. but this solves the changing sheets problem

=INDIRECT("'Input " &CHAR(66+(IF(M2="pre-sat",IF(LEFT(L2,5)="stage",MID(L2,7,1),"blank"),"blank")))&"6'!A1")

 

[hunin]

Hi Offthelip,

Thanks for your reply. I don't seem to getting it to work. Why have u made three ''' before input? And shouldn't it be Input! with the added exclamation mark at the end? It is really important that it fits for the condition otherwise it won't work. Can you please give it another shot?

Best regards

 

[hunin]

Please find the attached image if it makes things clearer

 

[offthelip]

Yes there is a ! at the end, it is the 5th character from the end of my equation.
The problem as I saw it is that you have sheet names which change letter in the middle of the string. going in sequence c , d ,e.
The way I approached this was to extract the number from the string: stage 1 , stage 2 , stage 3 which is in L2, then add this number to 66 and find which character that represents. Character 66 is letter B character 67 is letter C, etc
So this gives us the letter to insert into the middle of the string. the first bit of the string is 'Input the next bit is the letter we have determined and the last bit is 6'!A1. this should give us 'Input c6!'A1 etc as the final string

the three ''' which you ask about are NOT three single quotes is it a double quote to show that it is the start of a string and the first character of the string is a single quote. This could be why it doens't work for you.
To test is out, get rid of the indirect function around the whole thing and check that the string you get is the correct string to address the different worksheets.
It worked fine when I tested it but ofcourse I was interpreting your description of your workbook