Excel as a database: Pulling data from lists into another worksheet

[Kalimas]

Hi! I'm new to this forum and while I've been searching for a while now I still haven't found anyone with the same issue as me.

I just started working for a small architect firm, that's a little bit old school :)

What I'm doing is creating a database on excel for previous projects. So if anyone wants to find a reference for a previous project (or even find similar ones) there is an easy and simple way to find the data.

Example: Which school projects have we worked on it the last 5 years.

So under one column I have the building function: school, health, government, amenity, residential, Retail etc
and under another the project type: renovation, new construction, mechanical repairs, assessment, Accessibility etc.
and about 11 other columns detailing different aspects of the project. (Consultants, construction date, Project #, year, Contractor, links etc)

Currently I've turned this database into lists, which works great in that I can search for specific types of projects and then choose different parameters to narrow it down if there are still too many corresponding projects.

The biggest flaw is how wide it ends up being. While freeze panes are helpful I still end up scrolling for quite a while to read all of the information.
I would like to have a separate worksheet where I can put the lists and have it pull the corresponding data in an easier to read format.

Example: I would like all the school projects under $500,000.00 in the last 6 years.

I've looked at pivot tables and sorted through some v-lookup but nothing seems to fit my needs.
Can anyone point me in the right direction?
Thank you

[FDibbins]

If the data you are pulling is so wide, maybe you could try using index/match to "transpose" it so that it shows up in a column, rather than a row? It should be relatively (i think) to set it up so that you enter a building function (this could even be on a drop-down), then a project type (another drop-down?) and the rest of the info gets pulled in automatically.

to help you with this, i would need to see a sample workbook, showing what layout/data you have and what you're expected outcome is.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[FDibbins]

deleted - duplicate post

[Kalimas]

I've attached the spreadsheet I'm working with. I had to eliminated about 7 columns that contained more personal information but it has most of it there

Please let me know if you have any other suggestions

[FDibbins]

thanks. what would be the most common "things/headings" you would want to search on? would you be able to enter a unique category (of combination of categories), that would enable extraction of a specific row of info?

(i am assuming that you want to enter 1 or 2 pieces of info, and it will pull out the rest of the info for a specific row?)

[FDibbins]

see if the attached is something that you could work with? I have based it off the File No. and the way I have constructed it, you can actually compare multiple projects at the same time

[Kalimas]

More often then not we would search by year, (usually last 10 years as a custom search) function and project type.
You are assuming correctly! That is exactly what I would like, hopefully so it fits on the one worksheet with no scrolling necessary.
Thank you so much for responding back!

[PietBom]

I used Advanced Filter and some VBA

[Kalimas]

Hi Piet,
That is very close to what I want and I would love to know how you did it.
I was hoping to have all the information readable without scrolling. Is there a way to do that?
Thank you!

[FDibbins]

did you take a look at the file i uploaded?

[Kalimas]

Yes I did! I'm sorry I didn't realize you'd posted that, I thought it was the repeated comment.
That is pretty close to what I want, but does it only search by file #?
Thanks

[FDibbins]

yes I know it is only based on file No at the moment. that was just to show you my thoughts on how it could look. I am adjusting it to search based on function and project type, not sure yet how to include a "date range" though

[PietBom]

Hi Piet,
That is very close to what I want and I would love to know how you did it.
I was hoping to have all the information readable without scrolling. Is there a way to do that?
Thank you!

I used Advanced Filter connected to the button: [Run Filter1] which is executing a VBA subroutine.
You can see this subroutine in the VBA-editor (press Alt-F11 to open and Alt-Q to close)
To understand this code you should understand something about programming.
I hope you do.
The possibility to read the filtered list depends on the length of the list and the zooming factor and the size of your monitor.
I would prefer a bid of scrolling.

[Kalimas]

FDibbins,
That is pretty much perfect then! If you could include year that would be amazing, but if not then what you have already done but with function and type is above what I was hoping for.

Piet,
Sadly my programming knowledge is pretty nil at this point. Looks like I have something new to learn Thanks for your help!

[Kalimas]

FDibbins,
I've been trying to recreate your formula but am running into the issue of it saying #NA, can you please explain the steps you used to do this so I can try to solve the issue?
Thank you