Tips for converting a multi-tabbed workbook to more 'database' like?

[REvans81]

Hello everyone, sorry if this isn't the right place for this.

Current workbook consists of a sheet with a list of products and a sheet for each product. Each product sheet has 10 cells of product info in the exact same spot on each sheet (A:1 - A:10) and a list of sales data starting at row 15 (A15:H15). I want to condense this all down to 3 sheets:

- One with all the product info (Done)
- One with entries for all sales (I can convert this separately)
- One with a drop down box where I select the product, then the info and sales data populates. Then, I could add the new sales data to the end of the list and make any product info changes. Then, it would ideally automatically 'save' back to the other two sheets, updating it like a database.


Tl;Dr: Have tab with products and info and tab with sales. Want tab where I can select product from a list and update info and sales.


Hopefully that makes sense. I'm sure this is easily accomplished with some table magic, I just need pointed in the right direction. Thank you!

 

[Micron]

TBH, I think if you want a workbook to act like a relational database, you should create one. I guess that would be in Access (although it's not the only thing out there), but it has a significant learning curve if you're going to do that right. What you're describing is a perfect candidate for a db - for a workbook, not so much. Just saying that in case you don't get any ideas that meet your wishes.

 

[REvans81]

TBH, I think if you want a workbook to act like a relational database, you should create one. I guess that would be in Access (although it's not the only thing out there), but it has a significant learning curve if you're going to do that right. What you're describing is a perfect candidate for a db - for a workbook, not so much. Just saying that in case you don't get any ideas that meet your wishes.

Thank you, I agree. It's definitely not my idea to have our data in Excel but for the time being, that's where it's staying. It may not be worth the effort of trying to do this in Excel and I may just need to stick with manual entries until I convince others.

Thanks again

 

[Micron]

When/if you make that attempt, might help to know that Access RunTime is free for users (you need at least one license to develop something) and does not have the multiple concurrent users problem that Excel has. Hope you get what you're looking for (I wouldn't know what to suggest for Excel).