Is there a function to get colm range from a number?

[MottoK]

Hi,

Is there a simple function to return a column range from a number e.g.

If my match returns col 11 then I need a function to change/convert the 11 to N:N.

Is there such a function?

If VBA is the only way - don't waste your time as I need this as part of a larger formula.

Many thanks.

 

[SkipVought]

Hi,

INDIRECT function
[tt]
INDIRECT(CHAR(A1+66)&":"&CHAR(A1+66))
[/tt]
if your 11 is in A1

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[firefytr]

What is the purpose and what is the larger formula? Please explain - in detail - the large scope of your project so that we may give you the best solution(s) possible. Often times there may be a better way to accomplish your tasks.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[yogia]

Hi MottoK:

If A1 houses the numeric entry, then Oneway ...

=SUBSTITUTE(ADDRESS(1,A1+3,4),1,"")&":"&SUBSTITUTE(ADDRESS(1,A1+3,4),1,"")

would yield N:N

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[MottoK]

I don't know if what I want to do is possible but here goes:

I have a report sheet and a data sheet;

There are 10 columns on the data sheet the first is a date col and the rest are numbered 1 to 12.

On the report sheet there are 12 rows (one for every column of the data sheet).

What I want to do for each of the 9 rows on the report is SUM everything on a particular day - problem is I need the formula to be dynamic enough to return the corresponding row from the data sheet. For example if I was looking at row 11 on the report sheet I need to do a SUMIF and return where 11 matches the row header on the data sheet (i.e. col N:N).

Here is the formula to do this but it doesn't work because the SUM range argument returns a number not a column e.g. 11 instead of N:N.

=SUMIF(Proactive_Dispositions_Rpt_Data!B:B,Proactive_Dispositions!D$6,MATCH(Proactive_Dispositions!A8,Proactive_Dispositions_Rpt_Data!1:1,0))

The above works for a vlookup but I need to return everything not just one value.

I just need a way of getting the last argument to work!

Many thanks.

 

[MottoK]

Sorry (from the above I obviously meant there were 13 columns on the data sheet)!

 

[MottoK]

OK - I think I may have over stated the fact!

All I want to be able to do is do a SUMIF in the same way a VLOOKUP works but instead of returning one figure from the column I want to return evry figure (where the date col matches).

Can this be done?

Cheers

 

[Fenrirshowl]

MottoK

If I understand what you are after, you need the DSUM function. The help file should explain it clearly.

Fen

 

[SkipVought]

"... in the same way a VLOOKUP works but instead of returning one figure from the column I want to return evry figure"

You have ONE formula for each value. That's the way it works.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[Fenrirshowl]

MottoK

Found some more time...so to give you a more complete answer...

(I will assume your 13 columns are B to N, as you check the date against column B in your example)

Set up a criteria box, two cells, one above the other. (I will assume this is in Z1 and Z2.) The top one should be the title from the first column on Proactive_Dispositions_Rpt_Data sheet (your date column). I will assume it's entitled "Date" (most likely in cell A1 on Proactive_Dispositions_Rpt_Data.) Under it is your criteria, so if you want to sum all the entries in column M where the date in the first column is equal to today, you could enter "=TODAY()" or enter a date.

Your function to sum all the entries in column M say which has the assumed title of "Target" for all dates equal to the value in Z2 would be:
=DSUM(Proactive_Dispositions_Rpt_Data!B:N,"Target",Z1:Z2)

HTH

Fen
Fen

 

[SkipVought]

or...

If your data area is A1:M9999, the Column Range for any column number...
[tt]
=OFFSET(A1,0,ColNum-1,9999,1)
[/tt]
so the SUM in that column would be...
[tt]
=SUM(OFFSET(A1,0,ColNum-1,9999,1))
[/tt]

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[MottoK]

Hi,

Thanks for all the reply's - trouble is I can't get them to work.

To summarise: I have a SUMIF and the sum range argument is not working because all I have is the column number not the column letters.

I have tried the following to change the column number to letters and then bring it in to my SUMIF (but it doesn't work):

="PA_Disp_Data!"&SUBSTITUTE(ADDRESS(1,MATCH($A8,PA_Disp_Data!$A$1:$CY$1,0),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH($A8,PA_Disp_Data!$A$1:$CY$1,0),4),"1","")

I then direct the SUM range argument of my SUMIF to look at the cell which contains the above code - but it just returns 0 all the time.

ANy ideas?

 

[SkipVought]

Hey, how about posting an example of your DATA and am example of how you want to report.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[MottoK]

No worries here goes:

Excel Data:

Col: A B C D E etc

Col Name: Day 1 2 3 4 etc

12/09/2007 44 20 16 42
12/09/2007 12 7 47 11
13/09/2007 10 32 15 50
13/09/2007 22 33 14 19


Excel Report:

Columns:

Col Lookup Description 12/09/2007 13/09/2007 etc etc

1 "Descr 1" 56 32
2 "Descr 2" 27 65
3 "Descr 3" 63 29


The Col Lookup provides a number to do a macth on in the data sheet and is linked to a description shown here as "Description".

The trouble I am having is returning these figures i.e. 56, 32, 27, 65, 63 and 29.

Thanks.

 

[AnotherHiggins]

Check out faq68-5287 from Skip. Once your data is normalized, this will be a breeze.

Your example data will wind up looking like this:
[tt]
Row Column Value
12/9/2007 1 44
12/9/2007 2 20
12/9/2007 3 16
12/9/2007 4 42
12/9/2007 1 12
12/9/2007 2 7
12/9/2007 3 47
12/9/2007 4 11
13/9/2007 1 10
13/9/2007 2 32
13/9/2007 3 15
13/9/2007 4 50
13/9/2007 1 22
13/9/2007 2 33
13/9/2007 3 14
13/9/2007 4 19[/tt]

Now you can use a pivot table to organize things the way you want.

You'll wind up with this:

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

In the DATA sheet...
1. Name the column range (1, 2, 3...) ColRange

2. Name the date range DateRange

3. Name the range containg the data DataRange

In the REPORT, assuming that your example starts in A1...
[tt]
=SUMPRODUCT((DataRange)*(ColRange=$A3)*(DateRange=C$1))
[/tt]
copy thru all necessary cells.

Skip,

_{When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen!}

 

[MottoK]

Thanks Skip - I thought I'd done this but it keeps coming back with #Num! or #Name? depending on how I name these ranges.

Would it be possible to walk me through the naming of the ranges? This is where I think I'm going wrong, many thanks.

 

[MottoK]

Never mind I've got it - Skip you are the man thanks again.