• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Looking up a tab

P

phillygirl777

Member
I have to create a mechanism for looking up a value on a particular tab in the file with multiple tabs. I have a summary sheet for multiple departments that lists each department separately. Total expenses are on a particular cell of identically formatted sheets for each department. I know I can link it just by manually typing in the formula that looks like this:

=Sales!$C$25, or ='Internal Audit'!$C$25

However, I was wondering whether I can program it that it reads the name of the department in the adjacent cell.

Your input is greatly appreciated.
 
Hi Phillygirll

I don't quite understand your question. If

=Sales!$C$25

is acceptable to get the value why isn't

=Sales!$B$25

an acceptable method to get the name in the adjacent cell? Are you talking about using a lookup function based on the name of the sheet to return the value or are you just wanting the name of the worksheet the value is contained in?

Take care

Smallman
 
Hi ,

I think the INDIRECT function is the one you are looking for ; suppose A1 contains the text "Sales" , and A2 contains the text "Internal Audit" , then the following formulae will retrieve the amounts from the cells C25 on those two tabs :

=INDIRECT("'"&A1&"'!"&"C25")

=INDIRECT("'"&A2&"'!"&"C25")

Narayan
 
Narayan,
Thanks a lot, it worked perfectly fine.

Smallman,
The reason is that there are new departments being created, and I am just trying to automate the summary schedule for the future use.

Have a great day everyone!

Regards,
PhillyGirl777
 
You must log in or register to reply here.
Back
Top