Have the sheet name automatically change in a formula

I don't see how you can say you are new to this when you joined in 2007 and have 95 posts !!

Is there a column where you can record the sheet names (e.g. column D, starting with D2)? If so, then you can use this formula instead:

=INDIRECT("'"&D2$"'!C4")

then copy that down. Note that 01 is a text value, so you will need to set the format of the column that contains the sheet names to Text before you enter those names.

Hope this helps.

Pete

Originally Posted by CLSSY56

... So out of that of which you posted, what can I copy and paste into different similar formulas? ...

The formula is shown on the third line of my answer - it begins with an = sign. When you put this in a cell on row 2 and then copy it down, the D2 part will change to D3, D4, D5 etc. on successive rows, and so it will pick up the sheet name from those cells and return the value from C4 on that sheet. The C4 will not change when it is copied down, because it is treated as text.

I do not know why you get that error message as I can't see what you have been trying, so if you can't resolve it yourself it would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Glad to hear that you got it working.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, since you are relatively "new" to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

In all cases quoted above (Post #11) you have omitted the final quotes inside the closed bracket, so the expressions are not valid.

In the final example, you would have to use INDIRECT twice, once for each cell reference (as per the line above).

Your profile does not state which version of Excel you are using, but in earlier versions (up to XL2010, I think, or maybe even XL2013) conditional formatting did not allow you to directly reference other worksheets, so you won't be able to do it that way, anyway. You can put the (valid) expression in a cell on the same worksheet and reference that instead in your CF condition.

Hope this helps.

Pete

No, like this:

=INDIRECT("'"&A4&"'!$F$6")>INDIRECT("'"&A4&"'!$F$7")

I have removed the quote after the second apostrophe and put it just inside the close bracket.

Hope this helps.

Pete

That's right. You are building up a string of text which is made up of three parts, so you have:

"'"
&
A4
&
"'!$F$6"

and if A4 contains 10, then the composite string would be:

"'10'!$F$6"

However, this is just a piece of text to Excel, so to turn it into a valid cell reference you need to put it inside the INDIRECT( … ) function.

Hope this helps.

Pete

Originally Posted by CLSSY56

ok that kinda worked, when I copy it down "&A4&" doesn't change, it stays at A4.

Two things to check - Is your calculation mode set to Manual, rather than Automatic, OR is the cell formatted as Text instead of General?

Pete

On the Formulas tab at the top of the sheet, you should find the Calculation Options towards the right, in the Calculation group - just check on Automatic if it is set to Manual. Or, just press the F9 key to force a recalculation.

To check the formatting of the cell, right-click then choose Format Cells and on the Number tab you want the Category to be set as General. Then click in the formula bar (or press the F2 key) as if to edit the cell, then press < Enter > to effect the change.

I see that you have now set up a signature to say that you are using a Mac - I don't know if these directions will be exactly the same on a Mac compared with a PC.

Hope this helps.

Pete

Tell me again what formula you are using, or alternatively attach your workbook, as detailed in Post #5.

Pete

I opened the file and it seems to be working fine - this is what shows in cells A4:A6:

10 .... 1980 .... Mazda .... RX-7 ........... 50000
11 .... 1987 .... Jeep ...... Comanche .... 45000
12 .... 1992 .... Ford ...... Probe ........... 31000

(I've manually added the full-stops to try to keep the column data aligned).

Below that block you have a lot of #REF errors, because those sheets have not yet been added. You can overcome the errors by using this in A4:

=IFERROR(INDIRECT("'"&A4&"'!$C$4"),"")

and this will show blanks instead when it is copied down.

I'm not sure what problem you are having, then.

Pete

Put this formula in F4 of the Master sheet:

=IFERROR(INDIRECT("'"&A4&"'!$F$6")>INDIRECT("'"&A4&"'!$F$7"),"")

and copy down. It is based on the CF formula that you are trying to use and returns TRUE, FALSE or blank, as appropriate.

Then select cell E4 and click on Conditional Formatting | Manage rules, and change the Applies to box to =$B$4:$E$100

Then click on Edit Rule and change your condition to just:

=$F4

(Note there is no $ symbol in front of the 4).

That should work for you.

Hope this helps.

Pete

Glad to help - thanks for the rep.

Pete