search and display entries that share identical values along with connected details
search and display entries that share identical values along with connected details
Hey all,
I'm working on a spreadsheet for creating internal coupons.
The file is available here:
https://drive.google.com/file/d/1Q8qpeDQ...sp=sharing
In brief, I have columns with movement details and another column listing coupon numbers or names.
In one tab I want to display all entries matching the same coupon number/name.
Currently I'm using the "ingave" tab and trying to match rows in the same way.
I used match and index to locate matching rows, but I'm unsure about the best approach to gather the data and format the formulas correctly.
Any guidance would be greatly appreciated.
Best regards,
Ben
I understand the reminder about the saying. It’s a good point to revisit it. I hope you can take some time with Access to simplify things. Once you do, the spreadsheet will become much easier to manage. This is especially true if others are using it or trying to... cutting and pasting becomes problematic as you know.
= = = =
Terminology question:
Coupon: is that a document of some sort more like a shipping invoice? Versus "coupon" as used for giving a product discount on something?
Just looking at context of your spreadsheet seems to indicate that. Not sure about some of the column headers so feel free to correct and explain further as necessary.
Is this a new spreadsheet just being created?
The difficulties you are having are all too common with the use of a spreadsheet when a database is much more applicable and more appropriate.
And the word database tends to be intimidating to many people which is why so many people try to use a spreadsheet.
And all the more likely to become increasingly cumbersome as the spreadsheet grows with respect to rows and columns. Or other workflow changes occur.
If you are using Excel (.xlsx) then Access (database) would be suitable choice.
For the most part do not let "database" become a barrier to your efforts and requirements.
Once past a few very basic concepts the database will be easier to use and maintain. And be scalable as well.
One concept is "normalization" which can be very overwhelming. But for most applications, normalization is very straight forward.
Here are three links:
https://docs.microsoft.com/en-us/office/...escription
https://www.mygreatlearning.com/blog/ms-...-tutorial/
https://sirinc2.org/a16cat/2020 Presentations/Nov 2020 Introduction to Access.pdf
You can easily find other similar links on the topic.
Use the example in the first link (or other links that you find) as necessary to "normalize" the data that you need to collect, match, compare, etc..
Once that is done then use the Access wizards to create your tables and database.
May take a few tries but it should all fall into place very quickly.
After that then you will be able to build reports, queries, and forms that will further your requirements.
Note: For the most part existing spreadsheet data can either be imported into a database or linked to a database for access and further data manipulation. Point being that existing information in a spreadsheet does not necessary have to be redone or lost/given up in some manner.
Just my thoughts on the matter.
Hey Ralston18,
You're absolutely correct. Having a database would be helpful, but I'm short on time to dive into the details of how access works. I'm just aiming to make the invoice creation easier in the current Excel setup. Copy-pasting seems tricky for some users, especially when it comes to keeping the layout right.
Focusing on filling in an invoice number would be better in the long run. If I have more time later, I might explore access options, but right now it's simpler to just build a formula or set of formulas in the existing range.
Best regards,
Ben
There is a straightforward answer now. I used the real row outcome instead of "j" and "n". Then I compiled where to check using the indirect formula (since I already know where to look, no complicated formula required). The updated file is available here: https://drive.google.com/file/d/11IUEa5i...sp=sharing
I have been reminded of the saying "When you are up to your neck in alligators it is hard to remember that the original purpose was to drain the swamp".
Hope you found the solution, but try to take some time with Access to simplify things.
Eventually, the spreadsheet will become too complicated and more difficult to manage, especially if others use it or attempt... Cutting and pasting becomes problematic as you know all too well.
= = = =
An invoice is just a document that can be created with a single click. No special skills needed.
It seems you are sufficiently skilled in Excel that Access won’t be too challenging.
Focus on setting up the basic tables first.
= = = =
Still, if someone knows Access well, ask for assistance.
Creating a simple and functional database to handle invoice data would likely take only a few hours of work.
Make a copy of the spreadsheet and use it as the source in Access—either directly or by importing data into an Access table.
How to do that:
https://www.lifewire.com/convert-excel-s...se-1019967
(And there are many other similar resources available.)
Start with the basics, use the wizards, begin manually and then move to macros, menus, etc., to automate routine tasks.
If you encounter difficulties, just revert as needed and adjust. It may require a few extra attempts, but everything will become much smoother soon.