Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Trending News

Promoted
IXL@XL
Lv 7
IXL@XL asked in Computers & InternetSoftware · 1 decade ago

Variable column reference in INDIRECT function (EXCEL)?

I am having trouble in an easy way of referencing a variable column number in the INDIRECT formula. I can understand how by entering INDIRECT("B"&row(4)) will return whatever is in cell B4 but how do you enter a variable column reference? You can't enter an variable alphabetical reference directly (eg "AA")

Say the result of a formula needs the data from column C ie column 3 but the next calculation requires the data from column E ie column 5. How do you reference the column using alphabetical strings ie "E" or do i need a different way of referencing.

I currently use INDIRECT(ADDRESS(1,COLUMN(numerical result of previous calculation say 5)) to give me a cell reference E1.

Is there another way of entering the column reference number as an alphabetical string in the form of "E" &number?

Thanks in advance.

8 Answers

Relevance
  • expletive_xom
    Lv 7
    1 decade ago
    Favorite Answer

    i think you are doing this the best way...it always comes down to numbers not letters

    i always flip my sheet to R1C1 when i work on the address() part of the formula, then i change it back once ive got the numbers straight

    if you want indirect("b"&1) style, then (more numbers) =char(65) is A

    =INDIRECT(CHAR( ROW(A66))&ROW(A5))

    would get you cell B5

    but this only works from A to Z, if you want AA, you have to use an if() with a mod() and an int()

    or if you just need to change a formula from E to C "on the fly" then you would use a cell reference

    =INDIRECT(H1&5)

    where H1 has the letter E and you can manually change it to C

    then ofcourse theres alway the offset()

  • byrd
    Lv 4
    4 years ago

    Excel Indirect Function

    Source(s): https://shrinks.im/a7Z8Z
  • ?
    Lv 4
    5 years ago

    Indirect Function Excel

    Source(s): https://shrink.im/a8moO
  • ?
    4 years ago

    1

    Source(s): Whois Number Check http://reversephonenumberlookup.enle.info/?M6tY
  • How do you think about the answers? You can sign in to vote the answer.
  • Shirleen
    6 years ago

    This Site Might Help You.

    RE:

    Variable column reference in INDIRECT function (EXCEL)?

    I am having trouble in an easy way of referencing a variable column number in the INDIRECT formula. I can understand how by entering INDIRECT("B"&row(4)) will return whatever is in cell B4 but how do you enter a variable column reference? You can't enter an variable alphabetical...

    Source(s): variable column reference indirect function excel: https://shortly.im/RFFnC
  • Anonymous
    5 years ago

    Reverse Phone Number Look Up Services

    Source(s): https://tinyurl.im/aHJnt
  • Anonymous
    5 years ago

    For the best answers, search on this site https://smarturl.im/aD4ww

    100 tabs? ouch you dont mention any patterns that you need. you will need the INDIRECT() function so in Sheet1 cell A1 you have 123 in B1 copy&paste this formula =INDIRECT("CreditFeed!H"&A1) that will get H123 and if you change A1, it will change the cell it looks for that basically answers your specific question you have over 100 sheets if A1 has a cell# like 123 if B1 has a sheet name put in CreditFeed and if C1 has a column put in H then in D1 you would concatenate like this =INDIRECT(B1&"!"&C1&A1) notice i manually concatenated the ! in the formula. now you can change A1 B1 and C1 independently, and you can copy the formula down so that A2 B2 and C2 can reference one of the other 100 sheets. hope that helped good luck.

  • Anonymous
    6 years ago

    variable column reference indirect function excel: https://tinyurl.im/e/variable-column-reference-in-...

Still have questions? Get your answers by asking now.