Keep formulae pointed to same cell

Mechaman · #3 04-11-2021, 02:54 AM

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
Show Printable Version Email this Page
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

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