IF THEN QUESTION???

[Yugo]

Perhaps this has been discussed in the past.

I have 30 worksheets in an EXCEL file. The #1 worksheet is the main
informational sheet. I'm trying to obtain the formula that would allow me to
retrieve a specific data from any of the given worksheet.

Example: If J7 is 2, I would like for the system to go to worksheet #2 and
obtain the number from cell d39. If tomorrow I change J7 to 25, I would like
it to go to worksheet #25 and obtain the number from d39.

Your assistance is greatly appreciated
Thanks in advance,

Mike

 

[Don Guillett]

Have a look at the INDIRECT function to create this formula

 

[Ragdyer]

If your sheets have the standard XL labels:
Sheet1
Sheet2
.... etc.

Try this:

=INDIRECT("Sheet"&J7&"!D39")

 

[flummi]

What Don Guillett is suggesting is this:

Put your sheet number e.g. in A2 e.g. sheet3
Put your destination sheet cell reference in B2 e.g. A1
In C2 type this formula: =INDIRECT(A2&"!"&B2)

That will give you this

Sheet Cell Result
sheet3 a1 100

Hans

 

[Joseph N.]

=INDIRECT("Sheet"&J7&"!D39")

What does the exclamation point signify?

 

[Ragdyer]

It's strictly a separator between the name of the sheet and the cell
designation (name or address).

Enter 100 in A1 of Sheet1.

Enter an equal sign ( = ) in B1 of Sheet1, and then click in A1 of Sheet1
and hit <Enter>.
You see 100 in B1 and when you click in B1, in the formula bar you see,
=A1.

Now enter an equal sign in B1 of *Sheet2*.
Then navigate to Sheet1, and click in A1, and hit <Enter>.

You have the same 100 displayed in B1 of Sheet2, but what you see in the
formula bar is,
=Sheet1!A1