RodgerDJr
New Member
- Joined
- Dec 26, 2019
- Messages
- 2
- Office Version
- 365
- 2019
- 2016
- 2013
- 2010
- 2007
- Platform
- Windows
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
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