Dynamic sheet name  [SOLVED]

Put your best Excel related tips and tricks here.
Forum rules
This is the place to put all of your Excel Tips & Tricks.
ElenaNAV
Posts: 79
Joined: Tue Oct 20, 2009 7:08 am

Dynamic sheet name

Postby ElenaNAV » Tue Feb 09, 2016 5:23 am

Hello,

Do you know if what I need for a report is possible?

O a totaling sheet I have:
B11 = NL("Rows","Production Order","Sales Agent Code")
H11 = IF($B11=AC!B3,AC!U14,0)
On a distinct sheet (that is now named AC) I have a formula:
B3 = NL("Sheets","Production Order","Sales Agent Code")

So, when running the report, I will have a sheet per Sales Agent Code, with its code as the name.
When I want to call various information on a totaling sheet, I only get details from the AC sheet...
Is there a way to make my AC sheet name somehow dynamic?

Hope it makes sense what I need...
Thanks!

Elena

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 441
Joined: Wed May 26, 2010 3:40 pm

Re: Dynamic sheet name  [SOLVED]

Postby HPDeskJet » Tue Feb 09, 2016 8:44 am

Hi Elena -

It sounds feasible.

I assume you would want to use Excel's INDIRECT function --> http://stackoverflow.com/questions/2580 ... ith-quotes

Since (on the TOTALING sheet) you have an NL(Rows) that is returning the same values as your NL(Sheets) function, you can reference that NL(Rows) to build your indirect references.
-HP
_______________________
Jet 2015 Certified Trainer

User avatar
HPDeskJet
Jet Reports Certified Professional
Posts: 441
Joined: Wed May 26, 2010 3:40 pm

Re: Dynamic sheet name

Postby HPDeskJet » Tue Feb 09, 2016 8:52 am

Please note that, when using an INDIRECT to reference a dynamic sheet name, you most likely will see #REF errors when in Design mode:
error.png
error.png (8.47 KiB) Viewed 1068 times
since the sheet names will not actually exist until you run the report
-HP
_______________________
Jet 2015 Certified Trainer

ElenaNAV
Posts: 79
Joined: Tue Oct 20, 2009 7:08 am

Re: Dynamic sheet name

Postby ElenaNAV » Wed Feb 10, 2016 1:39 am

Works great!

Thank you!
Never user this Excel function so far so I had no idea about it.


Return to “Excel Tips & Tricks”

Who is online

Users browsing this forum: No registered users and 1 guest