• 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.

Use concatenate to create a workbook reference? (non-vba)

3G

3G

Member
Hi there-

I am trying to create a data sheet that will pull (eventually) from the sheets I load to the workbook. There are many sheets, but, the data is ALWAYS in the same position on the source sheets (O10-O24). What I'm trying to do is set up the summary/helper sheet to pull the data back from these cells. However, I can't do it unless I type in all of the cell references directly. Wondering if I can use concatenate to set up the formulas? Is that possible? At first pass, it doesn't look it...here's what I've got:


=CONCATENATE("=","'","Project9'!","O","10")


it just shows up as text though...


Thoughts?


Thanks
 
Try: =INDIRECT(CONCATENATE("Project9!","O","10"))


Concatenate joins the text together and so it is displayed as Text

Indirect takes the text and tries to make a valid reference out of it

Also you don't need ' unless there is a space in the sheet name
 
Thanks HUI! THis also helps my question from the other day regarding ADDRESS & INDIRECT.


Awesome!
 
A slightly better layout would be


=INDIRECT(A1&"!"&A2)


A1: Project9

Sheet name


A2: O10

Cell/Range reference


This allows you to change the Sheet or Range without editing the code
 
You must log in or register to reply here.
Back
Top