I am trying to figure out a way to drag cell ranges, and have Excel recognize the pattern of the range. I have several thousand ranges I need to edit, and manualy copying new ranges becomes extremely time consuming. The ranges will reflect cells in the pattern A2:A3, A4:A5, A6:A7, etc. When I try to use the drag technique, Excel copies in the following manner, A2:A3, A3:A4, A4:A5, etc. Excel is not recognizing the pattern. Even if I try to drag multiple cell ranges, say 3 or four ranges, Excel is still not dragging them correctly. Is there a way I can get Excel to drag my ranges in the appropriate pattern without using a Macro. This is for a client that I have verifed is not authorized to use macros at her work site.
Using Drag to keep range patterns w/out Macro
- Thread starter gkmety
- Start date
Thank you for welcoming me to the community! I am fairly new to using Excel as a "Power User", and this site has been helpful to clear many things up for me.
Here are two formulas, using the ranges. The difficulty I have is dragging the cell containing the first formula, and then getting it to match the second formula. When I try to do this, I remove the anchors, so the values should change. But again, Excel wants to drag in the format D2:D3, D3:D4, D4:D5...when I need D2:D3, D4:D5, D6:D7, etc. There are unique values in each range, D2:D3, D4:D5, etc., that belongs together. I hope this helps. Thank you.
=IF(AND(COUNTIF('Test Data'!$D$2:$D$3,"Building Healthy Connections"),'Test Data'!L$2>0,'Test Data'!L$2='Test Data'!L$3),SUM(1+0),"NA")
=IF(AND(COUNTIF('Test Data'!$D$4:$D$5,"Building Healthy Connections"),'Test Data'!L$4>0,'Test Data'!L$4='Test Data'!L$5),SUM(1+0),"NA")
Here are two formulas, using the ranges. The difficulty I have is dragging the cell containing the first formula, and then getting it to match the second formula. When I try to do this, I remove the anchors, so the values should change. But again, Excel wants to drag in the format D2:D3, D3:D4, D4:D5...when I need D2:D3, D4:D5, D6:D7, etc. There are unique values in each range, D2:D3, D4:D5, etc., that belongs together. I hope this helps. Thank you.
=IF(AND(COUNTIF('Test Data'!$D$2:$D$3,"Building Healthy Connections"),'Test Data'!L$2>0,'Test Data'!L$2='Test Data'!L$3),SUM(1+0),"NA")
=IF(AND(COUNTIF('Test Data'!$D$4:$D$5,"Building Healthy Connections"),'Test Data'!L$4>0,'Test Data'!L$4='Test Data'!L$5),SUM(1+0),"NA")
The next hurdle this spreadsheet poses is adding additional categories. For example:
=IF(AND(COUNTIF(INDIRECT("'Test Data'!D" & ROW($A1)*2 & ":D" & ROW($A1)*2+1),
"Building Healthy Connections"),INDIRECT("'Test Data'!L" & ROW($A1)*2)>0,
INDIRECT("'Test Data'!L" & ROW($A1)*2)=INDIRECT("'Test Data'!L" & ROW($A1)*2+1)),1,"NA")
represents 1 person, for one category (in this case, Building Healthy Connections). There are 11 additional categories that need to be added for each person. So the first step is to copy this cell, and only have it change the category name, which I can do based on the cell reference. But then I have to copy that further for 50 people.
In the example below, cell range L2:L3 represents an initial score, and a final score for Child 1, based on whatever provider(D2:D3) they may have used (they can have more than 1 provider, hence two cells), and so on for up to 50 children. The first method worked really well for dragging the formula down to the seventh child. But I will need a way to add the formula, so it stays the same for the first 121 cells, only changing the provider name, and then changes formulas when I get to child 2. Cell D2:D3 will contain the provider name. L2:L3 contain the actual initial and final scores. I hope this all makes sense…You can see how this can get out of control of I have to add even one additional provider..and the client is talking about adding up to 20 more! I Appreciate any help.
Building Healthy Connections Same Outcome for Child 1
Provider 2 Same Outcome for Child 1
Provider 3 Same Outcome for Child 1
Provider 4 Same Outcome for Child 1
Provider 5 Same Outcome for Child 1
Provider 6 Same Outcome for Child 1
Provider 7 Same Outcome for Child 1
Provider 8 Same Outcome for Child 1
Provider 9 Same Outcome for Child 1
Provider 10 Same Outcome for Child 1
Provider 11 Same Outcome for Child 1
=IF(AND(COUNTIF(INDIRECT("'Test Data'!D" & ROW($A1)*2 & ":D" & ROW($A1)*2+1),
"Building Healthy Connections"),INDIRECT("'Test Data'!L" & ROW($A1)*2)>0,
INDIRECT("'Test Data'!L" & ROW($A1)*2)=INDIRECT("'Test Data'!L" & ROW($A1)*2+1)),1,"NA")
represents 1 person, for one category (in this case, Building Healthy Connections). There are 11 additional categories that need to be added for each person. So the first step is to copy this cell, and only have it change the category name, which I can do based on the cell reference. But then I have to copy that further for 50 people.
In the example below, cell range L2:L3 represents an initial score, and a final score for Child 1, based on whatever provider(D2:D3) they may have used (they can have more than 1 provider, hence two cells), and so on for up to 50 children. The first method worked really well for dragging the formula down to the seventh child. But I will need a way to add the formula, so it stays the same for the first 121 cells, only changing the provider name, and then changes formulas when I get to child 2. Cell D2:D3 will contain the provider name. L2:L3 contain the actual initial and final scores. I hope this all makes sense…You can see how this can get out of control of I have to add even one additional provider..and the client is talking about adding up to 20 more! I Appreciate any help.
Building Healthy Connections Same Outcome for Child 1
Provider 2 Same Outcome for Child 1
Provider 3 Same Outcome for Child 1
Provider 4 Same Outcome for Child 1
Provider 5 Same Outcome for Child 1
Provider 6 Same Outcome for Child 1
Provider 7 Same Outcome for Child 1
Provider 8 Same Outcome for Child 1
Provider 9 Same Outcome for Child 1
Provider 10 Same Outcome for Child 1
Provider 11 Same Outcome for Child 1
Hmm. Rather than trying to adapt existing formulas to a siutation being revealed to us bit by bit, perhaps we could go the other way? If you can show us the layout of your current data, and what your want as an output, we might be able to come up with a better formula layout? If you copy data directly here to the forum, enclose it in backticks (`) (above the Tab key), or you can upload a sample file. Remember, the best example shows several cases of what you have, and what you'd like to see as an output.
I have posted a sample file. Basically, the end result I would like to achieve is an easier way to add additional providers to the Service Provider worksheet and additional services to the Service Type worksheets, for each child 1-5, and across the 4 scoring metrics, Same, Positive, Worse, and No Evidence.
For example, I would like to add Respite Provider as a new provider, for each child, and then have that reflected for the 4 scoring metrics.
Service Type is the same principle, I just added that for more diversity.
I may need to add 20 additional providers in the future. In my working file, there are 50 children reflected. So the manual, Find/Replace All method is not feasible.
Thank you all so much.
http://speedy.sh/bdeyH/Upload-Sample-Project-beta1.xlsx
For example, I would like to add Respite Provider as a new provider, for each child, and then have that reflected for the 4 scoring metrics.
Service Type is the same principle, I just added that for more diversity.
I may need to add 20 additional providers in the future. In my working file, there are 50 children reflected. So the manual, Find/Replace All method is not feasible.
Thank you all so much.
http://speedy.sh/bdeyH/Upload-Sample-Project-beta1.xlsx