Index Match Problem

M

Mike B

I have worksheets numbered 1 - 26. I am using the following on a woksheet
named 'Orders' to get data from a sheet named '13'
{=INDEX('13'!$B$2:$R$11,MATCH($C$32,'13'!$A$2:$A$47,0),MATCH(1,IF('13'!$B$2:$R$2=$D$6,IF('13'!$B$4:$R$4=$D$8,1)),0))}
How would I alter this to return data from a sheet number I enter into a
cell in my Orders worksheet. I have tried various methods but I must be doing
something wrong.
Any help would be greatly appreciated,

Thanks

Mike
 
G

Gary''s Student

Here is the general way to make the sheet name (in your case a number) a
variable:

Say we have the formula:
=Sheet2!A1
but we want to use a cell reference rather than the hard-coded Sheet2.

Use instead:
=INDIRECT(B2 & "!A1") and put Sheet2 in cell B2
Now we can change the tab we are examining just by changing the value in B2
 
M

Mike B

Thanks for the reply, I'll give it a try and let you know how I get on,

regards
Mike B
 
M

Mike B

Hi,
I have tried to incorporate the look-up function into the formula but still
cant get it right. I must be witing it the wrong way

Mike
 
M

Mike B

Hi,
I have tried to incorporate the INDIRECT function in the formula but without
success, I must be writing it incorrectly.

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top