Hi All,
Sorry if that isn't very clear. What I have is a spreadsheet where I have a MAIN LOG, which I use to keep track of client appointment and report timescales. That all works fine, so don't worry too much about that. What I need help with is this. When 'Y' is entered in Column R (indicating a blood test has been requested), I need a way for Excel to automatically copy information from that row to my BLOODS tab. For example, if I enter Y in R7 of the MAIN LOG, I need parts of Row 7 to copy over parts of this row to Row 4 Columns B-H on the BLOODS tab. After that, additional info on the bloods progress can be manually entered in columns I onwards.
I have found an INDEX formula which populates this. However, let's say I've already had Rod Flanders (Row 7) copied over to Row 4 of BLOODS, but then someone adds a Y to MAIN LOG Row 5 (Lisa Simpson's row). What happens is the BLOODS tab adds Lisa's data to Row 4, and moves Rod Flanders down to Row 5. This is a problem, because if I have added manual data to Rod's row already in Columns I, J, K etc, this data will stay where it is, even though Rod's other details will be on a different row now, and it looks like that data related to Lisa Simpson.
I suppose what I need is for each time MAIN LOG Column R has a Y put in it, it sends a permanent copy to the next free row of the BLOODS tab, which doesn't get re-ordered when another MAIN LOG client has a test requested. ie. if Rod Flanders gets copied to BLOODS Row 4, it stays there, even if Bart Simpson and Lisa Simpson subsequently have bloods requested. They should be copied to Rows 5 and 6 as they had the Column R 'Y' added after Rod Flanders.
Hope that makes sense? Please see screenshots below of my 2 different tabs
Sorry if that isn't very clear. What I have is a spreadsheet where I have a MAIN LOG, which I use to keep track of client appointment and report timescales. That all works fine, so don't worry too much about that. What I need help with is this. When 'Y' is entered in Column R (indicating a blood test has been requested), I need a way for Excel to automatically copy information from that row to my BLOODS tab. For example, if I enter Y in R7 of the MAIN LOG, I need parts of Row 7 to copy over parts of this row to Row 4 Columns B-H on the BLOODS tab. After that, additional info on the bloods progress can be manually entered in columns I onwards.
I have found an INDEX formula which populates this. However, let's say I've already had Rod Flanders (Row 7) copied over to Row 4 of BLOODS, but then someone adds a Y to MAIN LOG Row 5 (Lisa Simpson's row). What happens is the BLOODS tab adds Lisa's data to Row 4, and moves Rod Flanders down to Row 5. This is a problem, because if I have added manual data to Rod's row already in Columns I, J, K etc, this data will stay where it is, even though Rod's other details will be on a different row now, and it looks like that data related to Lisa Simpson.
I suppose what I need is for each time MAIN LOG Column R has a Y put in it, it sends a permanent copy to the next free row of the BLOODS tab, which doesn't get re-ordered when another MAIN LOG client has a test requested. ie. if Rod Flanders gets copied to BLOODS Row 4, it stays there, even if Bart Simpson and Lisa Simpson subsequently have bloods requested. They should be copied to Rows 5 and 6 as they had the Column R 'Y' added after Rod Flanders.
Hope that makes sense? Please see screenshots below of my 2 different tabs
