View Single Post
 
Old 04-11-2021, 02:54 AM
Mechaman Mechaman is offline Windows 10 Office 2019
Novice
 
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