5 ms·

I've often wanted some references in the formula to shift when I paste, while having others be fixed. This can be accomplished by putting the fixed references

by LukeShu 10y ago

I've often wanted some references in the formula to shift when I paste, while having others be fixed. This can be accomplished by putting the fixed references as string argument to the INDIRECT function (ex `indirect("F7")`).

gberger 10y ago
You can also use $F$7. Or if you do just F$7, only the column changes when pasting. Or $F7, only the row changes.
- DiabloD3 10y ago
  This right here is maybe the most important thing anyone can learn in Excel. The second most important thing is how to use vlookup() for cross-sheet lookups, using a column in the other sheet as a foreign key.
  - vehementi 10y ago
    > vlookup I guess you didn't watch the video?
    - DiabloD3 10y ago
      I did, and I understand why he mentions an alternate way of doing that. I still prefer vlookup.
      - abricot 10y ago
        If they made it optional to use column and e.x. made it possible to use column name references, i'd go back to love it too. But manually counting columns is annoying.
  - NamTaf 10y ago
    Stop using vlookup. Use index and match, either through named references as Joel does it or just manually even. It's more powerful, faster and less prone to failure because vlookup relies on an assumed order of sorting but most people won't know this and will wonder why it's cocking up.
    - DiabloD3 10y ago
      Fourth argument, set to false. Fixes the ordering issue.
    - yread 10y ago
      You really should use MATCH with last argument 0 (exact match) and use it only within IF(ISERROR: IF(ISERROR(MATCH(Key;Keys;0));"null";INDEX(Values, MATCH(Key; Keys))))
    - FabHK 10y ago
      VLOOKUP has a parameter for exact matches.
philsnow 10y ago
I forget the syntax but you can also write cell references that shouldn't change as $A$5 ; that fixes both row and column. Just put a $ in front of the dimension fixed.
FabHK 10y ago
Definitely don't want to use INDIRECT in large sheets, as it is volatile (i.e. always dirty). (Just to clarify: Excel maintains a DAG (directed acyclic graph) of dependencies, and upon changes marks cells dirty, then only recalculates what is required when F9 is hit. Cells containing volatile functions, being always dirty, always trigger a recalc of all their children.)

by LukeShu 10y ago

gberger 10y ago

You can also use $F$7. Or if you do just F$7, only the column changes when pasting. Or $F7, only the row changes.

DiabloD3 10y ago
This right here is maybe the most important thing anyone can learn in Excel. The second most important thing is how to use vlookup() for cross-sheet lookups, using a column in the other sheet as a foreign key.
- vehementi 10y ago
  > vlookup I guess you didn't watch the video?
  - DiabloD3 10y ago
    I did, and I understand why he mentions an alternate way of doing that. I still prefer vlookup.
    - abricot 10y ago
      If they made it optional to use column and e.x. made it possible to use column name references, i'd go back to love it too. But manually counting columns is annoying.
- NamTaf 10y ago
  Stop using vlookup. Use index and match, either through named references as Joel does it or just manually even. It's more powerful, faster and less prone to failure because vlookup relies on an assumed order of sorting but most people won't know this and will wonder why it's cocking up.
  - DiabloD3 10y ago
    Fourth argument, set to false. Fixes the ordering issue.
  - yread 10y ago
    You really should use MATCH with last argument 0 (exact match) and use it only within IF(ISERROR: IF(ISERROR(MATCH(Key;Keys;0));"null";INDEX(Values, MATCH(Key; Keys))))
  - FabHK 10y ago
    VLOOKUP has a parameter for exact matches.

philsnow 10y ago

I forget the syntax but you can also write cell references that shouldn't change as $A$5 ; that fixes both row and column. Just put a $ in front of the dimension fixed.

FabHK 10y ago

Definitely don't want to use INDIRECT in large sheets, as it is volatile (i.e. always dirty). (Just to clarify: Excel maintains a DAG (directed acyclic graph) of dependencies, and upon changes marks cells dirty, then only recalculates what is required when F9 is hit. Cells containing volatile functions, being always dirty, always trigger a recalc of all their children.)