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

Dynamic column reference formula.

V

vaidmohammed

Member
Hello,

I am having an excel, attached. In this the :
First page is the Dashboard, which gives the statistics of results.
Second page contins results for Access functionality.
Third page contins results for Orders functionality.

The cycle 1 results are defined under column head "ActRes_C1" and the cycle 2 results are defined under column head "ActRes_C2"

However, in the 2nd and 3rd sheet the results are recorded in different columns. Thus making difficult to give a generic reference on Dashboard to get the accurate result.

In the attached excel sheet, in Dashboard sheet, a formula is getting the details as per my requirement but I need to give the column references manually.

I need that instead of giving manual references of columns (in our case $F$1:$F$15000 in Dashboard example), a formula should search for the defined column header (which will be intact through out the work book) and then should pick the count of given condition.

Here we can make the folowing 2 condition that :
1. Column headers should be intact and uniform through out the workbook. i.e. ActRes_C1, ActRes_C2.
2. 1st row of all the worksheets should be the column header.
 

Attachments

  • Sample.xlsx
    10.3 KB · Views: 12
Hi Mohammed ,

There may be easier ways to do this , but this works :

=COUNTIF(INDIRECT("'"&$B3&"'!"&ADDRESS(1,MATCH(Col_1,INDIRECT("'"&$B3&"'!"&"$1:$1"),0))&":"&ADDRESS(10000,MATCH(Col_1,INDIRECT("'"&$B3&"'!"&"$1:$1"),0))),C$2)

Put this in C3 ; copy across and down.

Another formula :

=COUNTIF(OFFSET(INDIRECT("'"&$B3&"'!"&"$A$1"),,MATCH(Col_1,INDIRECT("'"&$B3&"'!"&"$1:$1"),0)-1,10000,1),C$2)

Col_1 refers to =Orders!$I$1

You can also use the header string ActRes_C1. For example :

=COUNTIF(OFFSET(INDIRECT("'"&$B3&"'!"&"$A$1"),,MATCH("ActRes_C1",INDIRECT("'"&$B3&"'!"&"$1:$1"),0)-1,10000,1),C$2)

Narayan
 
Dear Narayan,

Thankyou so very much for your efforts. But still I am facing problem using the given formula. I am attaching my actual file with this post. Please see that excel is giving #N/A error in all cells in my "Functionality Matrix" worksheet, which is my dashboard.
 
NARAYANK991 said:
Hi Mohammed ,

There may be easier ways to do this , but this works :

=COUNTIF(INDIRECT("'"&$B3&"'!"&ADDRESS(1,MATCH(Col_1,INDIRECT("'"&$B3&"'!"&"$1:$1"),0))&":"&ADDRESS(10000,MATCH(Col_1,INDIRECT("'"&$B3&"'!"&"$1:$1"),0))),C$2)

Put this in C3 ; copy across and down.

Another formula :

=COUNTIF(OFFSET(INDIRECT("'"&$B3&"'!"&"$A$1"),,MATCH(Col_1,INDIRECT("'"&$B3&"'!"&"$1:$1"),0)-1,10000,1),C$2)

Col_1 refers to =Orders!$I$1

You can also use the header string ActRes_C1. For example :

=COUNTIF(OFFSET(INDIRECT("'"&$B3&"'!"&"$A$1"),,MATCH("ActRes_C1",INDIRECT("'"&$B3&"'!"&"$1:$1"),0)-1,10000,1),C$2)

Narayan
Click to expand...


Dear Narayan,

Thankyou so very much for your efforts. But still I am facing problem using the given formula. I am attaching my actual file with this post. Please see that excel is giving #N/A error in all cells in my "Functionality Matrix" worksheet, which is my dashboard.​
 
Hi Mohammed ,

Can you attach your file ? The formulae I posted worked in the sample file you uploaded in your initial post.

Narayan
 
Hi Mohammed ,

I don't think I can do anything ; the first requirement for using the formulae posted in response to your initial sample file was that all fields would be in the same positions in all sheets ; in your working file , I find that the position varies from sheet to sheet ; I cannot do anything.

You will have to use a formula for each sheet based on the particular cell references.

Narayan
 
You must log in or register to reply here.
Back
Top