Better Way to Manage Database with Too Many Columns in One Worksheet

[Yodelayheewho]

Hi Everyone!
For a variety of reasons, I need to manage a small database for order entry into our main system database. I enter very complex orders that have 80+ steps from start to finish.
I have a Master worksheet that has about 96 columns.
My two major challenges:

1. How to make entering data into the Master faster and easier vs scrolling along 96 columns.
2. How to make editing Master data faster, easier and accurate for the same reasons

I tried to use the form function, but Excel limits you to 32 fields. I've thought about dividing the worksheet into multiple worksheets since the Master is comprised of the following sections (multiple columns that I color-coded):

1. Shop Order Section
2. Task Section
3. Proposal Section
4. PO Section
5. Sales Order Section
6. Customer Detail Section

I've attached a sample file. I've removed a lot of rows and columns and replaced confidential data with non-sense data. I left only three worksheets:

1. Master - holds all of the data. I cut this down to 63 columns for the sample
2. System Process - I use this as a step-by-step guide to enter an order in our system (so I don't have to scroll and scroll in the Master)
3. Search - I use this as a quick search tool to find key data points

Thank you in advance for your consideration.

 

[vletm]

Yodelayheewho
Could You use VBA with this?

 

[Yodelayheewho]

I am open to trying VBA. I'm always concerned that VBA will slow things down especially since the Master will continue to grow.

 

[vletm]

Yodelayheewho
I made the 1st sample.
Select any row from MASTER to EDIT
... from EDIT -- select Master to go back or Save to ... save selected row to Master-sheet.

 

[Yodelayheewho]

Hmmm...this brings me closer to where I want to go!
Technically, I just want the Master to 'house' the data and maybe even hide the worksheet. After all, it is quickly growing, and looking at it to find a row is a step I'm trying to avoid.

I love the EDIT worksheet and wonder if it could be used for EDITING AND SEARCHING. I attempted to do this with the SEARCH worksheet, but I now realize I need more capabilities to find answers fast while on conference calls.

As you noticed, on the SEARCH worksheet, I have four key fields to search:
Shop Order Number or...
Sales Order Number or...
Purchase Order Number or...
Proposal Number

I have multiple weekly meetings from each of our different business units. During these meetings I'm about orders. They will provide me one of four numbers listed above and it varies which number they will ask about based upon their role and involvement in the project.

Could we take your EDIT worksheet to the next level by adding a SEARCH feature? For example, if my colleague gives me Purchase Order Number 2031, I would like to type it in the search and all of the data fields in the same row populate the worksheet. Then if the next colleague asks me about Shop Order Number OSA-39928, I type it in the search box and all the fields populate the data in that row. The same would go for Sales Order Number and Proposal Number.

Is this doable? See the EDIT worksheet on the attached. I've added the search box as a visual example of what I'm thinking.
Thank you so much for your willingness to assist me!

 

[vletm]

Yodelayheewho
#1 What do You mean with term 'house'?
#2 EDIT and SEARCH ... hmm?
... 'searching' = filtering could do as well as below ... by enter needed information in cell and after that You could get those rows
... those 'green cells' could be anywhere ... below a sample
Then could go to EDIT-sheet to see that rows data ...
... and that EDIT sheet could have other layout too

I tried to search EDIT-sheet from Your file ... maybe I've to continue searching? Did You hide it?

 

[Yodelayheewho]

My apologies, it didn't save. Here's the file.

 

[vletm]

Yodelayheewho
Did You noticed my writings before that snapshot?
How Your 'edit-shearch' knows
- which number to search?
- if there could be more than one row to show ... then how?

 

[Yodelayheewho]

Hi vletm!
I read your comments again and played with the sample file you sent me some more. First, this is pretty cool what you've done!
Here's what I figured out, correct me if I'm wrong, please
1. Go to MASTER and click on ANY cell with data
2. The EDIT worksheet automatically pops up and all of the data from the same I clicked on populates the EDIT worksheet

Here are some challenges with this setup:
1. I can no longer edit the MASTER. While I did say I may end up hiding it, I still want to be able to work with it at times.
2. To search in this way isn't my ideal.

If you look at the first image I've attached, it would be faster to be able to enter the search criteria into a SEARCH box.
However, you made an excellent point about showing more than one row of results because of duplicate data.

Could it be solved by being able to 'scroll' through multiple results? See the second image below.

An unrelated question....why can't I see the column headers or row numbers? Why isn't the data starting at A1? Just curious

Thank you!!!

 

[vletm]

Yodelayheewho
You figured it.

some challenges with this setup:
#1 My version is a sample as well as I have tried to show some possible ideas.
There should do a lot of modification before it would be useful.
Of course, Master-sheet could be possible to edit too ... maybe with my next version.
My two major challenges:

1. How to make entering data into the Master faster and easier vs scrolling along 96 columns.
2. How to make editing Master data faster, easier and accurate for the same reasons

as well as Master - holds all of the data.

Your newer snapshot ... did it give an answer to my question?
How Your 'edit-search' knows - which number to search?
#2 that 'search' was the 1st quick sample... from Master-sheet could search with more informations

'Scrolling' could be possible to get.

why can't I see the column headers or row numbers?
Data is that You need to see
> You could focus with data. Less possibilities to do something mistakes.

Why isn't the data starting at A1?
> To have space for new further great ideas
Sheet has a lot of rows and columns to use.
Same way my sample of Edit-sheet starts as it starts - and You could add there some ideas too.

Just curious
... keep on asking.

Some samples ...
#1 Row selection for Edit from left side columns
#2 'Your Search' could activate with [ Search False/True ]-option
... use normal filtering to do Your search
#3 ALL and colors
... to scroll selected color section ...