Help needed with formula in Excel 2003 please!

[Attleborough]

I have a workbook with two sheets. On sheet A I have a cell (A1) that has a variable result gained from data in Sheet B.

I need to use the result in Sheet A Cell A1 to bring back the data from Sheet B next to where the result of A1 came from - with me so far? eg The result of A1 is from Sheet B cell C1 so I want to look up and bring back the data from Sheet B Cell C2.

I'm trying to use LOOKUP but I'm getting data from all over Sheet B and can't seem to work out what the formula should read,

Can anyone help prevent me from another sleepness night?

Thanks.

 

[MMC]

Can you not just copy and paste from B? Or have I just completely misunderstood

Post the sheet and I'll see if I can sort it for you...

And if I can't, there'll be some clever clogs on here who can! You could always post fake data if you don't want your figures public.

 

[Clive]

Offset?

=OFFSET(Sheet2!C1,1,0)

 

[NeilF]

In the cell where you want the result to be type "=" (without hitting Return) then go to the worksheet from where the value will come, and click on the relevant cell.

i.e. On Worksheet B, cell A2 type "=" then go to worksheet A and click on cell B6. The formula automatically completes, and the formula in B, A2 should then read "= worksheet A, B6".

You can use this same method to get data from another spreadsheet even.

Hope this is what you asked for!

Cheers,

Neil.

 

[Attleborough]

Thanks for all the replies but on reflection I feared I might not be able to make myself clear so I have now zipped and attached a workbook that gives some idea of what I'm trying to achieve.

In this example it's a simple database. On the frontpage sheet you select the perameters you want in Cells C6 & C8. As selected at the moment it gives you the managers name. What I then want it to do is pick up the managers telephone number.

OR if the perameters are changed to say the TU rep - it will then pick up the TU Rep telephone number.

So because the value in C8 can vary I need the formula to read what is in C8 and then look for it in The Database Sheet and then read and return the data one cell to the right to Cell C10 on the frontpage sheet.

 

[Taff]

In the cell where you want the result to be type "=" (without hitting Return) then go to the worksheet from where the value will come, and click on the relevant cell. ..You can use this same method to get data from another spreadsheet even. Hope this is what you asked for!

Fantastic. I've been on Excel courses and never knew I could do that.

This place continues to amaze me.

 

[Clive]

OR if the perameters are changed to say the TU rep - it will then pick up the TU Rep telephone number.

One way is to OFFSET the same MATCH
i.e =OFFSET(INDEX(Database!B2:M56,MATCH(C8,Database!A2:A56,),MATCH(C6,Database!B1:M1,)),0,1)

 

[Attleborough]

One way is to OFFSET the same MATCH
i.e =OFFSET(INDEX(Database!B2:M56,MATCH(C8,Database!A2:A56,),MATCH(C6,Database!B1:M1,)),0,1)

I think we have a winner!

Thanks for your help - Clive

As much as you think you (I) know, you sometimes need to stand away from it to see the answer clearly!

Thanks to all who offered suggestions!

 

[richie]

Fantastic. I've been on Excel courses and never knew I could do that.

This place continues to amaze me.

Taff, I used to teach Excel. I am amazed that you were not shown that, and suggest maybe you change training companies. Only on a half day intro to Excel for newbies would I only concentrate on one sheet calculations.

BTW I don't teach anymore so am not after any work.