Problem in If function

[Junaid]

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

 

[muddan madhu]

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:G10)&"!B5"),"")

 

[Pete_UK]

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

 

[Junaid]

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]

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

 

[Junaid]

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]

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

 

[Junaid]

Line 2 is in Row 38 and so on after same rows, 2nd sheet name is "Line
Capacity".