Trying to build a dynamic formula and a little more.

[RodgerDJr]

Good Evening all,

I am trying to build this without using VBA.

I am working on a workbook that will contain lab results for each day on a seperate sheet for each day.

The data comes from a report and will be pasted onto the appropriate tab according to the day. The data is cumulative for the month we are working with so I will have all of the results from day 1 on day 2, by the end of the month we will have all the patient results for the month on the final tab.

Part I getting the patient data to show up on the Main Sheet
On the main tab I want to display certain columns from the results tab depending on the day the user enters in cell B1. So if the user enters in a 3 then all the data from day 3 will be on the main form. I thought I would use the INDIRECT formula it all was working so well, until I realized that the last part of the formula was hard coding in the cell I was looking for. =INDIRECT($B$1 & "!A2") When I copied this down and saw the same Patient over and over then I realized what I was doing and why I was getting the same patient over and over. I am looking for some way I could change A2 to A3 . . .A32 when I copied it down. So far no luck. Any ideas on how I could do this?

Part II getting the result date to show up.
Now for the really hard part of what I am doing. There is a column for the date the result came back. This is easy for day 1 as they are all the 1st of the month; however on day two there will be patients that were resulted on day 1. The list of patients on Day 2 will have the new patients for the day as well as the patient from yesterday. So I need to look up the patients and match them against what was pulled on the first. If the patient is there then they had a result on day 1 otherwise they were resulted today as they are not found on the previous sheet.

This might be OK for one or two days, but what happens on the 20th day and I need to look back now on 19 different sheets to see when this patient first appeared, how do I look though 19 sheets without VBA?

Would it be better to add all the data on one sheet and add a new column with the date the data was added? Then search for the patient sorting the data by date and finding the min of the date I added the data.

I am not opposed to using VBA if I have to I am just trying to find a way that they lab can support this themselves. I may not have a choice but use VBA. But I thought I would at least try to do this without it at first.

Thank you in advance for your help, guidance or suggestion.
Rodger

 

[RodgerDJr]

Good Evening all,

I am trying to build this without using VBA.

I am working on a workbook that will contain lab results for each day on a seperate sheet for each day.
View attachment 18876

The data comes from a report and will be pasted onto the appropriate tab according to the day. The data is cumulative for the month we are working with so I will have all of the results from day 1 on day 2, by the end of the month we will have all the patient results for the month on the final tab.

Part I getting the patient data to show up on the Main Sheet
On the main tab I want to display certain columns from the results tab depending on the day the user enters in cell B1. So if the user enters in a 3 then all the data from day 3 will be on the main form. I thought I would use the INDIRECT formula it all was working so well, until I realized that the last part of the formula was hard coding in the cell I was looking for. =INDIRECT($B$1 & "!A2") When I copied this down and saw the same Patient over and over then I realized what I was doing and why I was getting the same patient over and over. I am looking for some way I could change A2 to A3 . . .A32 when I copied it down. So far no luck. Any ideas on how I could do this?

Part II getting the result date to show up.
Now for the really hard part of what I am doing. There is a column for the date the result came back. This is easy for day 1 as they are all the 1st of the month; however on day two there will be patients that were resulted on day 1. The list of patients on Day 2 will have the new patients for the day as well as the patient from yesterday. So I need to look up the patients and match them against what was pulled on the first. If the patient is there then they had a result on day 1 otherwise they were resulted today as they are not found on the previous sheet.

This might be OK for one or two days, but what happens on the 20th day and I need to look back now on 19 different sheets to see when this patient first appeared, how do I look though 19 sheets without VBA?

Would it be better to add all the data on one sheet and add a new column with the date the data was added? Then search for the patient sorting the data by date and finding the min of the date I added the data.

I am not opposed to using VBA if I have to I am just trying to find a way that they lab can support this themselves. I may not have a choice but use VBA. But I thought I would at least try to do this without it at first.

Thank you in advance for your help, guidance or suggestion.
Rodger

UPDATE
After writing all this out I went back and the Advanced Filters it the way to go on this. I think I just needed to move past what they wanted me to try and do and figure out that was not the best solution. The advanced filter gives them what they want and it really easy to implement.

Still need help with the Results Date
I am going to place all the data on one tab and have the date added and the account number as a primary key of sorts. I need to figure out a formula to look at the data and then create a Results Date. I am going to create an UPLOAD tab for them to copy the data to then I will have a button on the sheet that will copy the data over to the Main Data tab and add in the current date for the upload. Then on the data tab I need a column for Result Date which is just the first date the user was added to the list.

Thanks again,
Rodger