#1
|
|||
|
|||
Keep formulae pointed to same cell
Problem: When dragging data around, with either shift drag or control drag, the formula's pointing to those dragged cells change as well. But I want them to point to a fixed cell, no matter if I move what it contains to an other cell.
This problem is both in exel 2013 and 2019. Example: Start data and formula's: The same effect applies with dollar signs in front of the cell info (colom and row) The innitial output: The unwanted magic happens when shift dragging B3 down to B4 and and C3 down to C4. The result of that action is: and the formulas are changed by that action, though I dont want this, to: A3 is now referring to data in the 4th row, A4 is now referring to data in the 3rd row. How to keep the formula colum unchanged, as it was in the first or second image? I have searched around, but could not yet find an answer. I have also tried data protection, but that is also not the solution since OR nothing can be done with a data cell (which is not usefull), OR the problem still exists: dragging my data cells lead to the automatic change of my calculating cells (Colom A) Who can help me out? How can I keep my formula's unchanged while still being able to change the data around? |
#2
|
||||
|
||||
No image attached, no example visible
Shot in the dark : Using the INDIRECT function will prevent any changes Like =SUM(INDIRECT("A1:A10")) for example |
#3
|
|||
|
|||
solution
Missing images: Strange! The forum doesn’t let me upload 8 files totaling less than 200 kb! Drama. I added them into one image, hope that works.
Pecoflyer: Your solution helped me, thanks! Output data after dragging: John is moved one spot down, but A3 is still saying YES, while it should (for my purpose) now say NO since it should judge B3. My preferred output data: ( compared to the actual output data above, the A3 and the A4 formulas are changed back again to what they were initially. Solution: The indirect() function in combination with concatenate() and row(). The indirect function works great for my small example dataset. But when the dataset is larger, it takes a lot of time to manually edit all the cells. E.g. =IF(INDIRECT("B2")="John"; "YES";"NO") =IF(INDIRECT("B3")="John"; "YES";"NO") =IF(INDIRECT("B4")="John"; "YES";"NO") =IF(INDIRECT("B5")="John"; "YES";"NO") Etc. With normal formula’s I would select a number (2-3) of formulas and drag the selection down to populate the empty cells (using the drag handle (square) in the bottom right corner of the selection. But here the text doesn’t change accordingly. The solution to that problem is adding the following: =INDIRECT(CONCATENATE("B"; ROW())) Row automatically gives me the right (current) row number, B is fixed, concatenate makes it B2,B3,B4 etc. And then the indirect to get the value from that cell which was described in text so exel will not mutate that reference. Nice! For clarity I did this first step of getting the name from the data in a separate colum. I expanded the table a little as well for testing. The solution looks like this: I hope this can help others as well! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What's wrong with formulae? | kingston123 | Excel | 5 | 06-29-2020 10:55 AM |
help with formulae | Zoemoe | Excel | 3 | 07-02-2017 05:40 PM |
Animated pointed arrow | ChaosNMayhem | PowerPoint | 0 | 08-29-2015 03:49 PM |
How to hide a slide's content, then reveal bullet pointed lines one at a time? | 150mph | PowerPoint | 1 | 04-12-2014 02:20 AM |
Use NUMPAGES in formulae | Friedebarth | Word | 1 | 11-30-2011 02:22 PM |