Using Column Names In Formulas And Macros

D

David_9627

New Member
Joined
Aug 1, 2022
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello,

Simple question.... Can I use a column Name (Status), instead of it's alphanumeric reference (L:L) ?

I'm using Spiceworks as my Helpdesk Ticketing service for Users to log their helpdesk requests. Using the Reports function in Spiceworks I can export ticket details as XLXS files containing columns of information that I can then sort and reference to create a useful pie chart showing number of ticket opened, number of tickets closed. etc. to show to Management.

I export the data from Spiceworks and create a spreadsheet named Last_Month, which I then use to build the pie chart and other reported information.

Example here....

=COUNTIF(Last_Month!L:L, "Closed")

Column L in this case is a column named Status.

In this example it counts the number of tickets in a Closed status.

Management have asked me to capture some additional information for the monthly reports. Currently I have to go in and change each of the existing formulas to adjust for the shift in the column reference.
In this case column L is no longer my ticket status information, so I have to go in, find the correct column and update the formula - =COUNTIF(Last_Month!**NEW_COLUMN**:**NEW_COLUMN**, "Closed")

A time consuming pain in the @rse.

Is there a way for me to use COUNTIF and the column name "Status" rather than L:L (or whatever it changes to)?

Additionally, can this same principle be used to Sort a column by referencing the column name within a VBA Macro?

Any guidance or a solution would be greatly appreciated.

Regards,

David
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
David_9627 said:
Hello,

Simple question.... Can I use a column Name (Status), instead of it's alphanumeric reference (L:L) ?

I'm using Spiceworks as my Helpdesk Ticketing service for Users to log their helpdesk requests. Using the Reports function in Spiceworks I can export ticket details as XLXS files containing columns of information that I can then sort and reference to create a useful pie chart showing number of ticket opened, number of tickets closed. etc. to show to Management.

I export the data from Spiceworks and create a spreadsheet named Last_Month, which I then use to build the pie chart and other reported information.

Example here....

=COUNTIF(Last_Month!L:L, "Closed")

Column L in this case is a column named Status.

In this example it counts the number of tickets in a Closed status.

Management have asked me to capture some additional information for the monthly reports. Currently I have to go in and change each of the existing formulas to adjust for the shift in the column reference.
In this case column L is no longer my ticket status information, so I have to go in, find the correct column and update the formula - =COUNTIF(Last_Month!**NEW_COLUMN**:**NEW_COLUMN**, "Closed")

A time consuming pain in the @rse.

Is there a way for me to use COUNTIF and the column name "Status" rather than L:L (or whatever it changes to)?

Additionally, can this same principle be used to Sort a column by referencing the column name within a VBA Macro?

Any guidance or a solution would be greatly appreciated.

Regards,

David
Click to expand...
If its a structured table then you can use like
Excel Formula: 
=COUNTIF(TableName[Status],"Closed")
 
Upvote 0
Hi David and welcome to the board!

The short answer is yes to both questions.
Before (1)
David.xlsm
KLM
1header1Statusheader3
2OpenOpenOpen
3ClosedOpenOpen
4ClosedOpenOpen
5ClosedClosedOpen
6OpenClosedOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedClosedOpen
10ClosedClosedOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


After(1)
David.xlsm
KLMNOP
1header1header2header3header4Statusheader5
2OpenOpenOpenOpenOpenOpen
3ClosedClosedClosedClosedOpenOpen
4ClosedClosedClosedClosedOpenOpen
5ClosedClosedClosedClosedClosedOpen
6OpenOpenOpenOpenClosedOpen
7ClosedClosedClosedClosedOpenOpen
8ClosedClosedClosedClosedOpenOpen
9ClosedClosedClosedClosedClosedOpen
10ClosedClosedClosedClosedClosedOpen
11ClosedClosedClosedClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:P1="Status")*(K1:P11="closed"))


Before (2)
David.xlsm
KLM
1header1Statusheader3
2OpenOpenOpen
3ClosedOpenOpen
4ClosedOpenOpen
5ClosedClosedOpen
6OpenClosedOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedClosedOpen
10ClosedClosedOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


Code
VBA Code: 
Sub David_9627()
    Dim i As Long
    i = Range("1:1").Find("Status", Cells(1, 1)).Column

    With Cells(1, i).CurrentRegion
        .Sort key1:=Cells(1, i), Order1:=xlAscending, Header:=xlYes
    End With
End Sub

After (2)
David.xlsm
KLM
1header1Statusheader3
2ClosedClosedOpen
3OpenClosedOpen
4ClosedClosedOpen
5ClosedClosedOpen
6OpenOpenOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedOpenOpen
10ClosedOpenOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


Although as @SanjayGulatiMusafir suggested in post #2, you're probably better off going with tables instead.

Hope this helps.
 
Upvote 0
SanjayGulatiMusafir said:
If its a structured table then you can use like
Excel Formula: 
=COUNTIF(TableName[Status],"Closed")
Click to expand...
1659513755502.png
 
Upvote 0
You can see from the screenshot, I have separate tabs with the data, and use the formulas to capture the information and display it on the Main Page tab..

How should it work when I'm using different worksheets?
 
Upvote 0
kevin9999 said:
Hi David and welcome to the board!

The short answer is yes to both questions.
Before (1)
David.xlsm
KLM
1header1Statusheader3
2OpenOpenOpen
3ClosedOpenOpen
4ClosedOpenOpen
5ClosedClosedOpen
6OpenClosedOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedClosedOpen
10ClosedClosedOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


After(1)
David.xlsm
KLMNOP
1header1header2header3header4Statusheader5
2OpenOpenOpenOpenOpenOpen
3ClosedClosedClosedClosedOpenOpen
4ClosedClosedClosedClosedOpenOpen
5ClosedClosedClosedClosedClosedOpen
6OpenOpenOpenOpenClosedOpen
7ClosedClosedClosedClosedOpenOpen
8ClosedClosedClosedClosedOpenOpen
9ClosedClosedClosedClosedClosedOpen
10ClosedClosedClosedClosedClosedOpen
11ClosedClosedClosedClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:P1="Status")*(K1:P11="closed"))


Before (2)
David.xlsm
KLM
1header1Statusheader3
2OpenOpenOpen
3ClosedOpenOpen
4ClosedOpenOpen
5ClosedClosedOpen
6OpenClosedOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedClosedOpen
10ClosedClosedOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


Code
VBA Code: 
Sub David_9627()
    Dim i As Long
    i = Range("1:1").Find("Status", Cells(1, 1)).Column

    With Cells(1, i).CurrentRegion
        .Sort key1:=Cells(1, i), Order1:=xlAscending, Header:=xlYes
    End With
End Sub

After (2)
David.xlsm
KLM
1header1Statusheader3
2ClosedClosedOpen
3OpenClosedOpen
4ClosedClosedOpen
5ClosedClosedOpen
6OpenOpenOpen
7ClosedOpenOpen
8ClosedOpenOpen
9ClosedOpenOpen
10ClosedOpenOpen
11ClosedOpenClosed
12
134
Last_Month
Cell Formulas
RangeFormula
K13K13=SUMPRODUCT((K1:M1="Status")*(K1:M11="closed"))


Although as @SanjayGulatiMusafir suggested in post #2, you're probably better off going with tables instead.

Hope this helps.
Click to expand...
Same question for you Kevin,

You can see from the screenshot I added, I have separate tabs with the data, and use the formulas to capture the information and display it on the Main Page tab..

How should it work when I'm using different worksheets?
 
Upvote 0
David_9627 said:
Same question for you Kevin,

You can see from the screenshot I added, I have separate tabs with the data, and use the formulas to capture the information and display it on the Main Page tab..

How should it work when I'm using different worksheets?
Click to expand...
You simply put the sheet name followed by an exclamation mark before the range address, such as:

Excel Formula: 
=SUMPRODUCT((Last_Month!K1:M1="Status")*(Last_Month!K1:M11="closed"))
 
Upvote 0
kevin9999 said:
You simply put the sheet name followed by an exclamation mark before the range address, such as:

Excel Formula: 
=SUMPRODUCT((Last_Month!K1:M1="Status")*(Last_Month!K1:M11="closed"))
Click to expand...
Works great,

=SUMPRODUCT((Last_Month!A1:M1="Status")*(Last_Month!A1:M100="Open"))

Thanks Kevin, that's done the trick. Two-Thumbs-Up!
 
Upvote 0
kevin9999 said:
You simply put the sheet name followed by an exclamation mark before the range address, such as:

Excel Formula: 
=SUMPRODUCT((Last_Month!K1:M1="Status")*(Last_Month!K1:M11="closed"))
Click to expand...
Sorry David, forgot the single quotation marks...

=SUMPRODUCT(('Last_Month'!K1:M1="Status")*('Last_Month'!K1:M11="closed"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
99
JEC
JEC
ChrisMac1
  • Question Question
VBA - Compare specific data in one column on one sheet, with the same column on 4 other sheets, copy specific cells if match found.
Replies
1
Views
438
ChrisMac1
ChrisMac1
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
480
RoryA
RoryA
M
  • Question Question
Calculating Percentage between two columns
Replies
0
Views
1K
Mr Steve Cox
M
M
  • Solved
Dynamically Plot Data Points in Scatterplot
Replies
5
Views
681
felixstraube
F

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top