Forum Discussion

Copper Contributor

Oct 27, 2022

Solved

Help checking and re-ordering columns

Hello all, I have data that comes to me in a long line of columns that are regularly switched around. Every week I need to move the columns to make the data conform to a spreadsheet that has a bu...

excel

Formulas and Functions

OliverScheurich
Oct 27, 2022
BDCanuck
=CELL("Spalte",INDIRECT(B2&"1"))-CELL("Spalte",INDIRECT(A2&"1"))
You can try this formula.

BDCanuck

Copper Contributor

Oct 27, 2022

Thank you! That worked! For anyone else curious, there was a little bit of German left in the formula that OliverScheurich suggested. The formula for English Excel is =CELL("Col",INDIRECT(B2&"1"))-CELL("Col",INDIRECT(A2&"1"))

OliverScheurich

Gold Contributor

Oct 27, 2022

BDCanuck

You are welcome! Thank you for the correction!

BDCanuck
Copper Contributor
Oct 27, 2022
Hello again OliverScheurich! I tried to use the formula with strings of text, (which are what I actually receive as Column Header names). It didn't work. Is there something about this formula that is limited to the alphabet? It seems to work only for strings of three letters or less, with "XFD" being the last string that works.
Sorry if I'm asking dumb questions! I'm pretty new to this!
- OliverScheurich
  Gold Contributor
  Oct 27, 2022
  BDCanuck
  There are 16384 columns in an Excel sheet. Column XFD is column number 16384 and that is the limitation for the formula.
  - BDCanuck
    Copper Contributor
    Oct 27, 2022
    Ahhh, I see. Is there a different way to do this other than with that formula?
    The headers that I am actually using are things like
    
    dvt_day_dept_fam_count
    dvr_day_dept_fam
    dvr_day_dept_fam_count
    dvt_day_dept_count
    dvx_day_family