•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
rows don't follow unique value
Our take
Are you struggling with maintaining the integrity of your Excel data when new entries disrupt your carefully structured dropdowns and comments? Many users encounter this issue when formulas update and shift rows, leaving manual entries misaligned. Fortunately, there’s a way to ensure that each row, including dropdown values, follows its unique ID even as your master sheet refreshes. Continue reading to discover practical solutions that will streamline your data management and keep your insights consistent and accurate.
Guys, I need help with an Excel issue.
I have a master sheet with an auto-updating formula that pulls unique rows by ID from 3 other sheets.
The next columns contain dropdowns (data validation) and comments that users manually update. Once they select a dropdown, the formula is replaced by a value (can’t avoid that).
The problem: when new data is added to the source sheets, the formula refreshes and inserts new rows in the middle, but the dropdowns/comments don’t move — they stay on the old rows and no longer match the correct ID.
Is there a way to make each row (including manual dropdown values) follow its unique ID when the formula updates?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Returning multiple matches per ID (auto-fill when dragged down?)Hi there! I have multiple assigned IDs for a single code so I need to return all matching values, not just one. I also want this to be kind of automated (?) when I drag down the upstream ID column, it can pull all corresponding values, even if they share or don't share the same code I tried using FILTER, but it only spills results in one place and doesn’t carry over properly when dragged down for other rows. I don’t want to adjust ranges every time manually since my data might get longer. Is there a way to make a formula that: works when dragged down, returns multiple matches per ID, and still updates properly even if the data goes beyond the current rows? Left Side = source workbook; Right Side = destinatioon workbook Thank you so much in advance! submitted by /u/jeankrstein [link] [comments]
- Insert row that once data from column A to W is filled in, it moves the row down? I saw something about adding a button to manually make it shift with developer insert. That would be ok, but it seems like its conflicting with the columns I have as drop down options.Using Excel desktop app version 2606 I'm self taught with excel, I'm trying to make a sheet for tracking jobs for work. I'm not even sure if this is possible as I've seen conflicting things online. Is it possible to lock my 7th row as a input field and when I complete all columns A to W it will shift the row down? I did make tables for some of the columns to have drop downs, maybe I'm over complicating things and stuff is conflicting. I was trying to have AI walk me through some options. one was inserting a table but its not letting me. In short the row of things I want tracked, A through W. Would greatly prefer if I could keep drop down options for some of them. Biggest thing I would love to have is the first row be empty until I fill all the fields of data out, then it shifts the row down. I have a column of a "setup date" that I will sort the list by, All I want is to be able to have a input field and not have to go to the bottom of the list to add stuff every time. https://preview.redd.it/anq0vxlfxoog1.png?width=2014&format=png&auto=webp&s=93af3fff9c5f5aa34f64b111f298c2cdcc778144 submitted by /u/Firealarmfriday88 [link] [comments]
- How do I create dynamic dropdowns that works 2 ways?I have an Order Form sheet set up with a fair bit of code in the background. Part of the Form has order lines with headers of Code, Category and Product Description. I have a separate Products list which I wanted to create the dynamic drop down lists using formulas. I soon found out that you cannot type a FILTER formula directly into the Excel Data Validation source box. After a bit of researching, I figured how to to create dynamic dropdowns by referencing a spill range. The way I've set it up is probably a bit messy (but it works). At the moment the user has to select the category of product they want first. Then the drop down list in Product Description will show only items in that category. When they select the item they want, the drop down list for Code will show the specific code that item relates to. It can work but I was hoping to be able to use partial searches in any of the 3 columns to generate suggestions. eg. Start typing partial item name which would then narrow down options in the drop down list for Products as you type each letter. Select the item and the category and Item Code self populates. Also enable the user to partially type item code or category for a similar result. Hope this makes sense. And if so, any ideas? submitted by /u/AdComplete9707 [link] [comments]
- How can I make a single selection drop down auto fill a ton of other cells?I've not used excel much so I'm in way over my head. I've watched a bunch of videos and can get one cell to fill from a drop down but it seems to only work if they're 'lined up' with each other? For example in the 1st picture below, the drop down I have I can get 5 and 10-year to autofill MSUP 1st and 2nd Coats respectively, but only if they're next to each other as shown. I may not be explaining this well, sorry in advance if I am, I pretty much just want to select one product / warranty and have it auto fill all the green. I have templates on another sheet posted below that I've been copying and pasting when I price out jobs but it's just not efficient at all. I suspect that I have to organise or format my 'templates' somehow in order to make the V or X lookup function reference it? Either way, I'm pretty lost, so any help is appreciated, thanks guys!!~\~ EDIT: ok I've managed to get the drop down list to populate the correct items for 1 product by 'tagging' but this is reliant on putting this formula in each of these highlighted cells. My problem now is these cells are 'locked' looking for data from the same area as this 1 product, but I need it to search the entire sheet. When I select the entire sheet for the 'return array' it gives a value error presumably because it's trying to pull more than 1 value and put it in a single cell from my 'tags'. I suppose I could make a sheet for each system template we have but that would be cumbersome to work on. Is there a better way to do this? Am I even making sense? haha =XLOOKUP($B$7,Sheet1!A20,Sheet1!B20) https://preview.redd.it/cfm0r533e2ng1.png?width=834&format=png&auto=webp&s=aabf27687150d1700dd5e99f406106e4f884f17b https://preview.redd.it/l2k8ory9e2ng1.png?width=908&format=png&auto=webp&s=7d0377af01f791ca49d9a0ff71866597d6ee03be https://preview.redd.it/dgm52mnbe2ng1.png?width=978&format=png&auto=webp&s=e5c636d22dc284402b5ebe60fb1da9b531ebc248 submitted by /u/MahoganyDesk [link] [comments]
Tagged with
#rows.com#AI formula generation techniques#Excel alternatives for data analysis#big data management in spreadsheets#generative AI for data analysis#conversational data analysis#Excel compatibility#google sheets#real-time data collaboration#financial modeling with spreadsheets#intelligent data visualization#row zero#Excel#master sheet#auto-updating formula#follow unique ID#unique rows#ID#dropdowns#data validation