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$4 7,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

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
--
Gary''s Student - gsnu200842


"Mike B" wrote:

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$4 7,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

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

regards
Mike B

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200842


"Mike B" wrote:

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$4 7,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

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
"Mike B" wrote:

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

regards
Mike B

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200842


"Mike B" wrote:

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$4 7,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

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

Mike

"Mike B" wrote:

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

regards
Mike B

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200842


"Mike B" wrote:

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$4 7,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