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