Pulling Data from 1 sheet or sheets to a master sheet(Profile sheet)

I hope someone can help before I pull my hair out. There is not enough coffee to keep me up for the amount of time I have been trying to solve this formula(s). I work for a nonprofit program that helps the homeless get off the street. I am the program coordinator and one of my main duties is to audit client files while they are in the program to make sure needed docs are in the file, money is being spent where it is allowed and so on. I can do anywhere from 1 to 20 audits on a given file depends how long they are in one my 9 programs. I have no true data program to keep track of the audits that I do but on paper for each one. That is a lot of trees I have killed over the years. The problem is that I have to up to 30 different audits I do for 1 one client sometimes. I would show you that mad house but it has PII on it so big NO NO. I have been working on something to help be keep better track and much much cleaner by far than 1 workbook for this information and 1 workbook for this stuff and another for everything else on top of the paper trail.

So where I am having trouble is on the workbook is 10 tabs

Profile Sheet (This page will be printed when I have done the final audit and the client file is closed) All data for the client will be on this "form".

Client Test (Once I get the correct formulas it will just be clients) This is where data is pulled from the Program sheets or Master Audit sheet) Some I will type in myself

Master Audit (This sheet I would like for it to show all types, for all clients, from all programs at one time in a list. (Problem on this sheet is that I have the same client with different types of audits " on the program sheet but it only pulls the first audit type" none of the other data pulls because it only shows the first one under the "Lookup"

Then there is a tab for each of the 6 program I do audits for(3 of these program have 2 profiles under them RRH or HP)

Data(Drop down list)

Hi and welcome to the forum

As someone that has worked with data for many years, the first thing I'd say is I feel you're looking at this from a slightly odd angle. Rather than put the data into the individual sheets and pull it into one 'master' sheet and the profile sheet, it would make more sense to just enter the data on the master sheet and either filter it to get an individual cluent/programme view or, if you really need to individual programme sheets, farm the data out to those sheets from the master. It would simplify this project a great deal.

Nobody likes adding data to one big long list on a spreadsheet, things can easily go wrong if you accidentally press the up/down arrow and move to a new row so you're updating the wrong record, but Excel has capacity to deal with that sort of thing. For example you could have a userform that you use for data entry/amendment rather than working on the sheet itself. This often makes the whole process easier to handle and less prone to human error.

With all the data in one place and the use of some helper columns or slightly more complex formulas than you're using now you could easily populate your profile sheet this way.

Another option would be to have Excel send the data to a database rather than store it in this workbook. That way you can have Excel query the database and pull data for only a selected client. This would make things even easier still as populating the programme sheets and the profile sheet would be simple lookups due to the workbook only containing data for one client at a time.

Most of the above is dependent on your ability to run VBA on your system (some organisations don't allow it) but I thought I'd throw some ideas out there.


BSB

OMG!!! You are a heaven sent Glenn. I knew there was something wrong just did not see it. Fresh eyes are always better. I know there are a lot of dates on the Master Audit( some are just "Blank" dates unless there is a date under the program profile. That is one of my big problems pulling the different dates a audit and recert. were done for a client. The SSVF program is the best example I can say.

I will work on it later today to see if I can get it to pull the data. I will let y'all know what happens.

Thanks again for your help

Majord89

So go at it (aka backwards) in away. I have never done that, so not sure how to do that. I have always pull data never "push the data to another page like you are talking. Where would I start? I need something that flows easy.

Y'all help give me the correct formula so it will pull the data that I need to pull. I drop the Master Audit page and just pulling data from each program and client list. Thank you so so much.
Just a simple formula made my work day so much easier.