Excel 2007 help, Referencing sheets?

Question

I have several tabs which contain information from a standard questionaire, and a final tab where I put all the results together.
I need to write a formula that references the same cell in different sheets, so I can drag the formula and make it look like this:
'Sheet1'!A1
'Sheet2'!A1
'Sheet3'!A1

The problem is when I drag it, the sheet doesn't change, the cell changes. How do I get the sheet name to change?

Thanks!

Answer 1

You just need to input an equals sign at the beginning. For instance. If you want to reference cell A1 on Sheet1 on Sheet3, then you'd write:

=Sheet1!A1

You only need to add the apostraphes if the tab name has a space. So if Sheet1 was instead called Sheet 1, then you'd need to write:

='Sheet 1'!A1

Really all you need to do is type in the equals sign then select the cell you want to reference and hit enter. Excel should do the rest.

Answer 2

confident, expensive... that's frequently solved by skill of following... you have records in Sheet2 say from A2 to D10 (with the exception of the header row) Say A2, A3, A4 hav the drop down checklist. and you want corresponding pay1, pay2 and pay 3 in B, C and D columns. in simple terms kind the function in B2 as follows... =VLOOKUP(A2,Sheet2!$A$a million:$D$10,2) in C2 kind =VLOOKUP(A2,Sheet2!$A$a million:$D$10,3) in D2 kind =VLOOKUP(A2,Sheet2!$A$a million:$D$10,4) in simple terms replica the formulation downward for all of the columns you want. bear in thoughts it would teach #N/A if not something is chosen interior the Dropped Down checklist and the formulation is copied interior the cells. Bingo! your problem is solved.