Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem in If function
I have one file with 11 sheets. All sheets have same column & rows headers.
1st sheet is summary and the other 10 sheets 10 machines. every machine has its own saparate characteristics. in one column of the summary sheet i entered all the machine names and in Cell A3 is validated with these names. My file is just like below A B C D E F ......... M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions Jan Feb Mar April Mar ...... Dec 5 Total time 6 Shut down 7 Capacity 8 Load time In B5 i entered the formula =if(A3=Machine 1, Sheet 2! B5, if(A3=Machine 2, Sheet 3! B5, if(A3=Machine 3, Sheet 4! B5, .....) and then i drag it up to december and to the last required row but it works up to machine 8 after that its not work. and its work fine when i select the machine from drop down list in A3 the data appear from that sheet. please tell me if there is any other formula. thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem in If function
Try this
You have created drop down with machine names for cell A3. Assumed source for the drop down list in Cell F1:F10 as Machine 1,Machine 2,Machine 3,Machine 4.................... e.g., Col F Col G Machine 1 2 Machine 2 3 Machine 3 4 .. .. .. .. 2,3,4....... is the sheet number as you said if you choose Machine 1 you need Sheet2 B5 as your answer, if u choose Machine 2, you need sheet 3 B5 as your answer......... for that reason I have used col G. know your formula =IF(A3<"",INDIRECT("sheet"&LOOKUP(A3,F1:F10,G1:G1 0)&"!B5"),"") On Jan 24, 4:59*pm, Junaid wrote: I have one file with 11 sheets. All sheets have same column & rows headers. 1st sheet is summary and the other 10 sheets 10 machines. every machine has its own saparate characteristics. in one column of the summary sheet i entered all the machine names and in Cell A3 is validated with these names. My file is just like below * * * * A * * * * * * * * B * * * * C * * * * *D * * * * E * * * * * F ........ * * * * M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions * * * Jan * * *Feb * * Mar * * April * * *Mar ...... * * * *Dec 5 Total time 6 Shut down 7 Capacity 8 Load time In B5 i entered the formula =if(A3=Machine 1, Sheet 2! B5, if(A3=Machine 2, Sheet 3! B5, if(A3=Machine 3, Sheet 4! B5, .....) and then i drag it up to december and to the last required row but it works up to machine 8 after that its not work. and its work fine when i select the machine from drop down list in A3 the data appear from that sheet. please tell me if there is any other formula. thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem in If function
You could try something like this in B5 of your summary sheet:
=IF($A$3="","",INDIRECT("'Sheet "&(TRIM(RIGHT($A$3,2))+1)&"'!"&ADDRESS (ROW(B5),COLUMN(B5)))) Then copy this across and down as required. It relies on there being a space in your sheet names ("Sheet 2", Sheet 3" etc) and in your machine names ("Machine 1", "Machine 2" etc) as you have shown in your example. If your sheets are named the same as your machine names, then this could be simplified to: =IF($A$3="","",INDIRECT("'"&$A$3&"'!"&ADDRESS(ROW( B5),COLUMN(B5)))) Note the apostrophes which surround the sheet name. Hope this helps. Pete On Jan 24, 11:59*am, Junaid wrote: I have one file with 11 sheets. All sheets have same column & rows headers. 1st sheet is summary and the other 10 sheets 10 machines. every machine has its own saparate characteristics. in one column of the summary sheet i entered all the machine names and in Cell A3 is validated with these names. My file is just like below * * * * A * * * * * * * * B * * * * C * * * * *D * * * * E * * * * * F ........ * * * * M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions * * * Jan * * *Feb * * Mar * * April * * *Mar ...... * * * *Dec 5 Total time 6 Shut down 7 Capacity 8 Load time In B5 i entered the formula =if(A3=Machine 1, Sheet 2! B5, if(A3=Machine 2, Sheet 3! B5, if(A3=Machine 3, Sheet 4! B5, .....) and then i drag it up to december and to the last required row but it works up to machine 8 after that its not work. and its work fine when i select the machine from drop down list in A3 the data appear from that sheet. please tell me if there is any other formula. thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem in If function
Dear Pete Thanks for your help and it works exactly, now i need some more
clarification if my all machines details are on sheet 2 down ward so than what will be the formula should i need to give the name to the range of machine details. "Pete_UK" wrote: You could try something like this in B5 of your summary sheet: =IF($A$3="","",INDIRECT("'Sheet "&(TRIM(RIGHT($A$3,2))+1)&"'!"&ADDRESS (ROW(B5),COLUMN(B5)))) Then copy this across and down as required. It relies on there being a space in your sheet names ("Sheet 2", Sheet 3" etc) and in your machine names ("Machine 1", "Machine 2" etc) as you have shown in your example. If your sheets are named the same as your machine names, then this could be simplified to: =IF($A$3="","",INDIRECT("'"&$A$3&"'!"&ADDRESS(ROW( B5),COLUMN(B5)))) Note the apostrophes which surround the sheet name. Hope this helps. Pete On Jan 24, 11:59 am, Junaid wrote: I have one file with 11 sheets. All sheets have same column & rows headers. 1st sheet is summary and the other 10 sheets 10 machines. every machine has its own saparate characteristics. in one column of the summary sheet i entered all the machine names and in Cell A3 is validated with these names. My file is just like below A B C D E F ........ M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions Jan Feb Mar April Mar ...... Dec 5 Total time 6 Shut down 7 Capacity 8 Load time In B5 i entered the formula =if(A3=Machine 1, Sheet 2! B5, if(A3=Machine 2, Sheet 3! B5, if(A3=Machine 3, Sheet 4! B5, .....) and then i drag it up to december and to the last required row but it works up to machine 8 after that its not work. and its work fine when i select the machine from drop down list in A3 the data appear from that sheet. please tell me if there is any other formula. thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem in If function
Glad to hear it worked for you, but I don't understand your follow-up
question. Can you please re-phrase it, and perhaps give some examples like before? Pete On Jan 25, 7:05*am, Junaid wrote: Dear Pete Thanks for your help and it works exactly, now i need some more clarification if my all machines details are on *sheet 2 down ward so than what will be the formula should i need to give the name to the range of machine details. "Pete_UK" wrote: You could try something like this in B5 of your summary sheet: =IF($A$3="","",INDIRECT("'Sheet "&(TRIM(RIGHT($A$3,2))+1)&"'!"&ADDRESS (ROW(B5),COLUMN(B5)))) Then copy this across and down as required. It relies on there being a space in your sheet names ("Sheet 2", Sheet 3" etc) and in your machine names ("Machine 1", "Machine 2" etc) as you have shown in your example. If your sheets are named the same as your machine names, then this could be simplified to: =IF($A$3="","",INDIRECT("'"&$A$3&"'!"&ADDRESS(ROW( B5),COLUMN(B5)))) Note the apostrophes which surround the sheet name. Hope this helps. Pete On Jan 24, 11:59 am, Junaid wrote: I have one file with 11 sheets. All sheets have same column & rows headers. 1st sheet is summary and the other 10 sheets 10 machines. every machine has its own saparate characteristics. in one column of the summary sheet i entered all the machine names and in Cell A3 is validated with these names. My file is just like below * * * * A * * * * * * * * B * * * * C * * * * *D * * * * E * * * * * F ........ * * * * M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions * * * Jan * * *Feb * * Mar * * April * * *Mar ...... * * * *Dec 5 Total time 6 Shut down 7 Capacity 8 Load time In B5 i entered the formula =if(A3=Machine 1, Sheet 2! B5, if(A3=Machine 2, Sheet 3! B5, if(A3=Machine 3, Sheet 4! B5, .....) and then i drag it up to december and to the last required row but it works up to machine 8 after that its not work. and its work fine when i select the machine from drop down list in A3 the data appear from that sheet. please tell me if there is any other formula. thanks- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem in If function
take the same example that i gave before but this time all machines are on
one sheet, not like before on the separate sheets. 1st sheet is summary and on 2nd sheet details of all 10 machines. A B C D E F ......... M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions Jan Feb Mar April Mar ...... Dec 5 Total time 6 Shut down 7 Capacity 8 Load time Let say this is summary sheet, similarly for 10 machines on 2nd sheet. i think you have understand what i mean. "Pete_UK" wrote: Glad to hear it worked for you, but I don't understand your follow-up question. Can you please re-phrase it, and perhaps give some examples like before? Pete On Jan 25, 7:05 am, Junaid wrote: Dear Pete Thanks for your help and it works exactly, now i need some more clarification if my all machines details are on sheet 2 down ward so than what will be the formula should i need to give the name to the range of machine details. "Pete_UK" wrote: You could try something like this in B5 of your summary sheet: =IF($A$3="","",INDIRECT("'Sheet "&(TRIM(RIGHT($A$3,2))+1)&"'!"&ADDRESS (ROW(B5),COLUMN(B5)))) Then copy this across and down as required. It relies on there being a space in your sheet names ("Sheet 2", Sheet 3" etc) and in your machine names ("Machine 1", "Machine 2" etc) as you have shown in your example. If your sheets are named the same as your machine names, then this could be simplified to: =IF($A$3="","",INDIRECT("'"&$A$3&"'!"&ADDRESS(ROW( B5),COLUMN(B5)))) Note the apostrophes which surround the sheet name. Hope this helps. Pete On Jan 24, 11:59 am, Junaid wrote: I have one file with 11 sheets. All sheets have same column & rows headers. 1st sheet is summary and the other 10 sheets 10 machines. every machine has its own saparate characteristics. in one column of the summary sheet i entered all the machine names and in Cell A3 is validated with these names. My file is just like below A B C D E F ........ M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions Jan Feb Mar April Mar ...... Dec 5 Total time 6 Shut down 7 Capacity 8 Load time In B5 i entered the formula =if(A3=Machine 1, Sheet 2! B5, if(A3=Machine 2, Sheet 3! B5, if(A3=Machine 3, Sheet 4! B5, .....) and then i drag it up to december and to the last required row but it works up to machine 8 after that its not work. and its work fine when i select the machine from drop down list in A3 the data appear from that sheet. please tell me if there is any other formula. thanks- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem in If function
So on this composite sheet you have Machine 1 in cell A3 - which cells
are the other machine names in? Are they always the same number of rows apart? Are you just wanting to bring the data from 4 rows for Total time, Shut down, Capacity and Load time? What's the name for the second (composite) sheet? Pete On Jan 26, 12:15*pm, Junaid wrote: take the same example that i gave before but this time all machines are on one sheet, not like before on the separate sheets. 1st sheet is summary and on 2nd sheet details of all 10 machines. * * *A * * * * * * * * * *B * * * * C * * * * *D * * * * E * * * * * F * * ........ * * * * M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions * * * Jan * * *Feb * * Mar * * April * * *Mar * ...... * * * * Dec 5 Total time * 6 Shut down 7 Capacity 8 Load time Let say this is summary sheet, similarly for 10 machines on 2nd sheet. i think you have understand what i mean. "Pete_UK" wrote: Glad to hear it worked for you, but I don't understand your follow-up question. Can you please re-phrase it, and perhaps give some examples like before? Pete On Jan 25, 7:05 am, Junaid wrote: Dear Pete Thanks for your help and it works exactly, now i need some more clarification if my all machines details are on *sheet 2 down ward so than what will be the formula should i need to give the name to the range of machine details. "Pete_UK" wrote: You could try something like this in B5 of your summary sheet: =IF($A$3="","",INDIRECT("'Sheet "&(TRIM(RIGHT($A$3,2))+1)&"'!"&ADDRESS (ROW(B5),COLUMN(B5)))) Then copy this across and down as required. It relies on there being a space in your sheet names ("Sheet 2", Sheet 3" etc) and in your machine names ("Machine 1", "Machine 2" etc) as you have shown in your example. If your sheets are named the same as your machine names, then this could be simplified to: =IF($A$3="","",INDIRECT("'"&$A$3&"'!"&ADDRESS(ROW( B5),COLUMN(B5)))) Note the apostrophes which surround the sheet name. Hope this helps. Pete On Jan 24, 11:59 am, Junaid wrote: I have one file with 11 sheets. All sheets have same column & rows headers. 1st sheet is summary and the other 10 sheets 10 machines. every machine has its own saparate characteristics. in one column of the summary sheet i entered all the machine names and in Cell A3 is validated with these names. My file is just like below * * * * A * * * * * * * * B * * * * C * * * * *D * * * * E * * * * * F ........ * * * * M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions * * * Jan * * *Feb * * Mar * * April * * *Mar ...... * * * *Dec 5 Total time 6 Shut down 7 Capacity 8 Load time In B5 i entered the formula =if(A3=Machine 1, Sheet 2! B5, if(A3=Machine 2, Sheet 3! B5, if(A3=Machine 3, Sheet 4! B5, .....) and then i drag it up to december and to the last required row but it works up to machine 8 after that its not work. and its work fine when i select the machine from drop down list in A3 the data appear from that sheet. please tell me if there is any other formula. thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem in If function
Line 2 is in Row 38 and so on after same rows, 2nd sheet name is "Line
Capacity". "Pete_UK" wrote: So on this composite sheet you have Machine 1 in cell A3 - which cells are the other machine names in? Are they always the same number of rows apart? Are you just wanting to bring the data from 4 rows for Total time, Shut down, Capacity and Load time? What's the name for the second (composite) sheet? Pete On Jan 26, 12:15 pm, Junaid wrote: take the same example that i gave before but this time all machines are on one sheet, not like before on the separate sheets. 1st sheet is summary and on 2nd sheet details of all 10 machines. A B C D E F ........ M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions Jan Feb Mar April Mar ...... Dec 5 Total time 6 Shut down 7 Capacity 8 Load time Let say this is summary sheet, similarly for 10 machines on 2nd sheet. i think you have understand what i mean. "Pete_UK" wrote: Glad to hear it worked for you, but I don't understand your follow-up question. Can you please re-phrase it, and perhaps give some examples like before? Pete On Jan 25, 7:05 am, Junaid wrote: Dear Pete Thanks for your help and it works exactly, now i need some more clarification if my all machines details are on sheet 2 down ward so than what will be the formula should i need to give the name to the range of machine details. "Pete_UK" wrote: You could try something like this in B5 of your summary sheet: =IF($A$3="","",INDIRECT("'Sheet "&(TRIM(RIGHT($A$3,2))+1)&"'!"&ADDRESS (ROW(B5),COLUMN(B5)))) Then copy this across and down as required. It relies on there being a space in your sheet names ("Sheet 2", Sheet 3" etc) and in your machine names ("Machine 1", "Machine 2" etc) as you have shown in your example. If your sheets are named the same as your machine names, then this could be simplified to: =IF($A$3="","",INDIRECT("'"&$A$3&"'!"&ADDRESS(ROW( B5),COLUMN(B5)))) Note the apostrophes which surround the sheet name. Hope this helps. Pete On Jan 24, 11:59 am, Junaid wrote: I have one file with 11 sheets. All sheets have same column & rows headers. 1st sheet is summary and the other 10 sheets 10 machines. every machine has its own saparate characteristics. in one column of the summary sheet i entered all the machine names and in Cell A3 is validated with these names. My file is just like below A B C D E F ........ M 1 2 3 Machine name (this is drop down validated cell with machine names) 4 Descriptions Jan Feb Mar April Mar ...... Dec 5 Total time 6 Shut down 7 Capacity 8 Load time In B5 i entered the formula =if(A3=Machine 1, Sheet 2! B5, if(A3=Machine 2, Sheet 3! B5, if(A3=Machine 3, Sheet 4! B5, .....) and then i drag it up to december and to the last required row but it works up to machine 8 after that its not work. and its work fine when i select the machine from drop down list in A3 the data appear from that sheet. please tell me if there is any other formula. thanks- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MAX / MIN function problem | Excel Discussion (Misc queries) | |||
Mid Function Problem | Excel Discussion (Misc queries) | |||
Problem with IF function | Excel Discussion (Misc queries) | |||
Problem with the VALUE function | Excel Discussion (Misc queries) | |||
IF function problem | Excel Worksheet Functions |