How to ID data cols in formula when data col location move around in file

**welchs101** · 07-11-2011, 02:10 PM

I have a file (see enclosed). In this file i am doing a countifs on specific cols (this is just an example file). What i want is to somehow use match (if possible) to identify the column to use in the countifs formula. The reason: I dont expect the "location" of the cols of data to stay the same.......i expect them to move around a bit.

**welchs101** · 07-11-2011, 02:38 PM

just wanted to provide a little clarification in case i confused anyone.

on the 2nd sheet is a formula containing a countifs function. in this formula i concentrate on two cols of data...........data is located on sheet1. formula only works if the data stays in the right cols..........but what i want to do is somehow use like match or something such that header1 and header4 can be anywhere in the file and formula will still work.

**welchs101** · 07-11-2011, 08:30 PM

since no one responded should i assume this is not possible and i should be gin looking for another alternative?

**welchs101** · 07-11-2011, 08:36 PM

I found this

MATCH("Header2",Data!1:1)

But this gives the col #. What i need is the col ID like A, B, or C so that i can use it in a formula like this

COUNTIFS(Data!A:A,"=" &"Junk1",Data!D:D,"=" & "Junk4")

I need to replace A and D with something in case the locations of A and D change.

doe sthis make sense?

**Azam Ali** · 07-12-2011, 12:34 AM

Hi welchs101

you can use the address function to pick up the column reference. Kindly see the attatched file.

**welchs101** · 07-12-2011, 06:29 AM

thanks i will take look at it today.

**welchs101** · 07-12-2011, 08:15 AM

Very neat! But it looks like that formula wont work if cols go beyond A to Z. I guess i could modify the formula to extract everything prior to the "$" and then it might work for cols past "Z".

Thanks for the info.......i learned something. Never knew abou tthe address function.

**Azam Ali** · 07-12-2011, 11:52 PM

Hi welchs101

column range is define in the match function i.e. "Data!$A$1:$K$1". You could change the range by replacing the K in the match funciton. e.g. if you replace K from Z than the formula will be effective upto Z column