How to make a cell value dynamic when the referred value changes?

[bncoxuk]

Hi, good weekend!


I have a very short question. In cell A1 of Sheet2, the value is static text: Sheet1!B:B, which means that it refers to column B of Sheet1. As this is a static text, it will not change even when new columns are added before column A of Sheet1. How do I use a formula to make cell A1 of Sheet2 automatically update when new columns are added in Sheet1? I know named range can make this happen, but I prefer to use a formula to accomplish this.


Thanks!

 

[kevatarvind]

try below in sheet2

Excel Workbook
	A	B
1	Test	Sheet1!B1
Sheet2

 

[Jonmo1]

I'm guessing that you already have a formula using INDIRECT that references A1 to get the Sheet1!B:B reference.
Can you post that formula?
There may be a better way..

 

[bncoxuk]

I'm guessing that you already have a formula using INDIRECT that references A1 to get the Sheet1!B:B reference.
Can you post that formula?
There may be a better way..

Yes, that's exactly right I have a formula that already uses INDIRECT:

=MATCH(DATEVALUE("30-Jun-2012"),INDIRECT(A1),0)

What I hope to achieve is that I make a template with many calculation functions such as above. Then someone can simply specify in cell A1 of Sheet2 which columns they want to use from Sheet1 (e.g. Sheet1!B:B)

 

[Jonmo1]

Would it be simpler for your users if they were to type in a Header Value instead of Column ?

Say Sheet1 has headers like, Name Address Number etc..
And the user could just type (or select from a dropdown menu) the Header name they want ?

That would be much easier for the formula to handle dynamically.

 

[bncoxuk]

Would it be simpler for your users if they were to type in a Header Value instead of Column ?

Say Sheet1 has headers like, Name Address Number etc..
And the user could just type (or select from a dropdown menu) the Header name they want ?

That would be much easier for the formula to handle dynamically.

Hi Jonmo1, yes. I totally agree that your idea is a better one. So maybe I make it as:

=MATCH(DATEVALUE("30-Jun-2012"),OFFSET(Sheet1!$A$1,0,MATCH(A1,Sheet1!$1:$1,0)-1,1000,1),0)

So a user just specify the head name in cell A1 and the result will be shown directly.

 

[Jonmo1]

That's almost exactly what I was going to suggest..
But I would use index instead of offset

=MATCH(DATEVALUE("30-Jun-2012"),INDEX(Sheet1!$A$1:$Z$1000,0,MATCH(Sheet2!$A$1,Sheet1!$A$1:$Z$1,0)),0)

 

[bncoxuk]

That's almost exactly what I was going to suggest..
But I would use index instead of offset

=MATCH(DATEVALUE("30-Jun-2012"),INDEX(Sheet1!$A$1:$Z$1000,0,MATCH(Sheet2!$A$1,Sheet1!$A$1:$Z$1,0)),0)

Thanks, Jonmo1. I learned!

 

[Jonmo1]

Thanks, Jonmo1. I learned!

Good deal, that's what we're all about here.
Glad to be a part of it.

 

[Andrew Poulsom]

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here along with the explanation: http://www.mrexcel.com/forum/showthread.php?t=99490). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Cross-post here:

How to make a cell value dynamic when the referred value changes?