Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-08-2021, 04:16 AM
Mechaman Mechaman is offline Keep formulae pointed to same cell Windows 10 Keep formulae pointed to same cell Office 2019
Novice
Keep formulae pointed to same cell
 
Join Date: Apr 2021
Posts: 2
Mechaman is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 04-08-2021, 07:24 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Keep formulae pointed to same cell Windows 7 64bit Keep formulae pointed to same cell Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 04-11-2021, 02:54 AM
Mechaman Mechaman is offline Keep formulae pointed to same cell Windows 10 Keep formulae pointed to same cell Office 2019
Novice
Keep formulae pointed to same cell
 
Join Date: Apr 2021
Posts: 2
Mechaman is on a distinguished road
Default 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!
Attached Images
File Type: jpg solution all.jpg (92.5 KB, 11 views)
Reply With Quote
Reply

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
Keep formulae pointed to same cell 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
Keep formulae pointed to same cell Use NUMPAGES in formulae Friedebarth Word 1 11-30-2011 02:22 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 04:43 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft