+ Reply to Thread
Results 1 to 19 of 19

Refer to columns by cell reference

  1. #1
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    O365
    Posts
    180

    Refer to columns by cell reference

    Hi,

    I've always wondered if it were at all possible to refer to columns by putting the value in a cell. For example, if I had "D" or "4" in cell A1, could I use this in formulas then? Like count(a1:a1) - but I'd like it to count column D if that makes sense?

    I've thought about this purely because I might have a sheet with several formulas and I need to change the column which means going through and changing them manually. If I could use a cell reference, I could copy the sheet and then just change one thing.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Refer to columns by cell reference

    You would use INDIRECT for that. For example:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Refer to columns by cell reference

    Or:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Refer to columns by cell reference

    You mean like this?

    =COUNT(INDIRECT($C$1&":"&$C$1))

    However, the use of INDIRECT in whole columns is likely to be slow.

    Alternatively, you can restrict the start/end row in a similar way:

    =COUNT(INDIRECT($C$1&$C$2&":"&$C$1&$C$3))
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    O365
    Posts
    180

    Re: Refer to columns by cell reference

    Indirect - great, thanks!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Refer to columns by cell reference

    Too late .....!!

  7. #7
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    O365
    Posts
    180

    Re: Refer to columns by cell reference

    How would that work if I just wanted to refer to an entire column? My formula was

    =countif('Raw Data - filter'!C:C,"Something")

    but it doesn't work with

    =countif('Raw Data - filter'!(indirect(b2&":"&b2),"Something")

    when in B2 I have 3 for column 3.

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Refer to columns by cell reference

    The syntax for INDIRECT is footery:

    =countif(INDIRECT("'Raw Data - filter'!"&$B$1&":"&$B$1),"Something")

    where the column you want it to refer to is in B1.

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Refer to columns by cell reference

    What's footery?

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Refer to columns by cell reference

    Try:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Refer to columns by cell reference

    I thought footery was in general use on these islands. But it may be restricted to the celtic fringe, where it's Irish/Scots for "a bit complicated". I found the endless " that INDIRECT often entails hard to get right when I was starting off with Excel.

  12. #12
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    O365
    Posts
    180

    Re: Refer to columns by cell reference

    What am I doing wrong then here? This works:

    =COUNTA(INDIRECT("'Raw Data - filter'!"&$B$2&":"&$B$2))

    but this doesn't:

    =COUNTIF(INDIRECT("'Raw Data - filter'!"&$B$2&":"&$B$2),H3)

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Refer to columns by cell reference

    It looks OK to me...

    Check the spelling of the second sheet name....
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    O365
    Posts
    180

    Re: Refer to columns by cell reference

    Same sheet - I'm just getting #ref

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Refer to columns by cell reference

    Please post it.



    Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  16. #16
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    O365
    Posts
    180

    Re: Refer to columns by cell reference

    Here you go.
    Attached Files Attached Files

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Refer to columns by cell reference

    You have two criteria: gender and answer. you need COUNTIFS

    =COUNTIFS(INDIRECT("'Raw Data - filter'!"&$B$3&":"&$B$3),H$3,INDIRECT("'Raw Data - filter'!"&$B$4&":"&$B$4),$G4)

    in H4, copied across and down.


    see sheet
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    10-14-2011
    Location
    London, England
    MS-Off Ver
    O365
    Posts
    180

    Re: Refer to columns by cell reference

    Thank you Glenn! Absolutely perfect.

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Refer to columns by cell reference

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 17
    Last Post: 01-01-2018, 07:29 AM
  2. Refer Folder/Filename in VBA code through Named cell reference
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2013, 09:38 AM
  3. [SOLVED] Drag Formula to every other cell but refer to consecutive columns in another sheet
    By mariejulia in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-02-2012, 06:44 AM
  4. Need to refer to value of cell reference
    By rf23338 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2011, 12:08 PM
  5. [SOLVED] Excel: can I refer to cell reference in page setup header
    By nikos in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-21-2006, 02:25 PM
  6. refer to drop down item as a sheet reference in a formula
    By milkman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-18-2006, 03:15 PM
  7. Replies: 1
    Last Post: 02-23-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1