Using Cells and Worksheets Functions in Formulas

Good afternoon,

I am wondering if it is possible to use the worksheets and cells functions within a formula as a cell location reference instead of a value. Please see below for what I currently have and ultimately what I would like to end up with to avoid any columns, worksheet name changes, or worksheets being added.

Variable y are the number of columns in the original document. This forumla is going to be 2 columns to the right of the last column in the original document.

Range(Cells(2, y + 2), Cells(x, y + 2)).Formula = "=INDEX('Cat. Table'!$C$2:$C$" & z & ",MATCH(N2,'Cat. Table'!$A$2:$A$" & z & ",0))"

Is it possible to have the following as the N2 location may eventually change if new columns are added.

Range(Cells(2, y + 2), Cells(x, y + 2)).Formula = "=INDEX('Worksheets(2)!$C$2:$C$" & z & ",MATCH(Cells(2,y+2),'Worksheets(2)'!$A$2:$A$" & z & ",0))"

When I enter Cells(2,y+2) it returns the value in the previous cell; ultimately, I would like it to return the cell location, in this case N2.

Thanks.

Untested, but try changing:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Regards, TMS

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Try, untested:

Please Login or Register  to view this content.

Regards, TMS

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.