Help in linking cells of one sheet to others based on response

Hello all,

Very new to this forum.

After much reading, I have an Excel spreadsheet that is working quite well for data collection - namely studying how many positive cultures patients with recurrent pneumonia have.

There are several sheets, but all start with the same variables in each of the first 5 columns. These 5 variables are (1) the total number of cultures (validated between 1-20), (2) first 3 letters of last name (may not be unique to an individual), (3) medical record number (unique to each individual), (4) the date of the culture in question (validated), (5) and the specific culture # in question.

"1 culture" will require 3 sheets consisting of a "demographic page" (basic information of the individual), the other two are sheets regarding specific questions about what happened when those respiratory cultures were obtained. Each of those 3 sheets is somewhat different after the 5 columns, but they just repeat. In other words, a pt with only one culture will have only 3 sheets; pt with 2 cultures, will require 5 sheets (one demographic, 2 for culture #1, 2 for culture #2, etc).

What I need is a way to link the variables of the first give columns to specific spreadsheets based on the total number of cultures of each patient, or column one. This is to avoid some replication, and be able to sort the data out based on other criteria.

For example, I have sheets for patients with 4 cultures, 6 cultures, etc (a couple of folks have 11 cultures) each of which have the exact same layout and first 5 columns. If a patient has 2 cultures, based on the value of the 1st column (a number 2), these will be immediately duplicated to a spreadsheet for "2 cultures". Patients with 3 cultures, the spreadsheet for "3 cultures", and so on.

Lastly, these would have to be dynamic - if a patient with a "3" in his first column (3 cultures, thus duplicating in the sheet for "3 cultures") now has another culture, I type in a "4" on his first column, add another row to accommodate the new culture, and his information is in the "4 cultures" sheet instead of the "3".

Honestly, I have no idea how to link this. I have attached a screenshot of the specific columns in question if it helps. I can make the values in column 1 as a drop-down list if it helps.

If you need a sample excel file, do let me know. All the information provided in the chart is bogus for HIPPA purposes.

Free coffee to the one who helps me out!

sample sheet.jpg

trial.xlsx

I have attached a test Excel file as well

Sorry it is really hard to understand your requirement, even looking into your same excel. You better simplify your data and require output sheet (in sample excel).

Originally Posted by shcon

Sorry it is really hard to understand your requirement, even looking into your same excel. You better simplify your data and require output sheet (in sample excel).

Thanks for your help. This is still data collection, and did not send any of the output data.

I'm just looking for a way to send certain information to a specific sheet (e.g. a row that starts with the #4 should have this information copied automatically to sheets "cx 4 AND ), and if it changes, to be sent to the adequate sheet.

I can set up filters after everything is in place and get specific information, I'm just trying to have some things separate.