Log In
Register
Home
February 11, 2022
Offline
I am trying to modify an existing Excel file. I am using MS 365 on a MacBook Pro running Ventura. I have been using dependent data validation in several columns, but I am trying to reduce some manual input each month. I have about 30 monthly deposit and withdrawal transactions that I am trying to automate, possibly with xlookup. I would like to enter the Description, and have the checkbook register row auto populate the Parent_Cat, Category, Transaction Type. I would like it to be flexible so I can still enter other transactions not in the xlookup data sheet. My current file has about 5300 existing rows, but I am sending a sample file that you can do anything in. Maybe what I would like is not possible, but I wanted to ask people that have more Excel Knowledge than me.
Thank you,
David Topp
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Even though you have added a data validation list for the cells, it is still possible to write a formula in those cells. The problem is that the formula in the cell is only there as long as you don't change the data in the cell, in other words don't type in anything or pick any value from the list.
If the intention is to add the row and not reusing any row for another data and using a range this is ok, even though nothing I would recommend. But if you use an Excel table I would not mix two different methods to get data, either go for looking up the data or use the data validation lists.
The reason I don't like to mix the methods on how the data is collected is because I like it to be consistent, makes it far easier to maintain and understand, especially when you detect some errors and need to troubleshoot.
In attached file I have added a simple XLOOKUP formula.
Br,
Anders
1 Guest(s)
