Dynamic update of column in a formula?

[jmw1515]

Hi everyone, thanks again to all those who make this board the most helpful excel resource on the web.

I have a question that is causing major headaches.

My goal is to dynamically update a formula with a range based on lookup values, ie. A1:A10

Here is how I have set this up so far:

Lets call the sheet I want to do this Sheet 1 with the Sheet pulling data as Sheet 2.

I have a dropdown list of 4 activities, each of which has specific data underneath in Sheet 2. I have the Column Identifier (ie. A,B,C, etc) underneath the Activities on Sheet 2 for ease of using a lookup function to use the formula in Sheet 1.

Let me walk through an example to see if I can be crystal clear.

Sheet 1
Dropdown List found in A1:
Activity 1 - Walk
Activity 2 - Run
Activity 3 - Swim
Activity 4 - Crawl

Cell B1: Shows the corresponding Column letter from the activity

Formula need help with:
=COUNTIF('Sheet 2'!&B1&3&":"&B1&10, A1)
I am almost certain I'm doing this improperly because I can't dynamically link the formula Column to the cell that changes.

(The count is really all I'm looking for based on the dynamic update of the formula, driven by whatever is in Cell B1 in Sheet 1.

I don't know how to better explain this....Maybe there is an easy way? :x


Sheet 2
Activities mapped in cells:

A B C D
Row 1: Walk Run Swim Crawl
Row 2: A B C D
Row 3: Walk Run Swim Crawl
Row 4: Walk 0 Walk 0
Row 5: 0 0 Swim Crawl



I will post more if I am unclear, although I think this is pretty simple...I'm just stuck!

 

[jmw1515]

Bump...If anyone has ANY recommendations, please share...

 

[jmw1515]

Bump again...I've been searching about Concatenation but unable to find something I can leverage

 

[Domenic]

Try...

=COUNTIF(INDIRECT("'Sheet 2'!"&B1&"3:"&B1&"10"),A1)

Hope this helps!