• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Range: Retrieve Value from Merged Cells

J

jdppep

Member
I am attempting to retrieve values from a series of merged cells without VBA. I realize merged cells can cause complications; however, I think this can still be done.

On Sheet: "Productivity", I am trying to match the productivity of each staff to the various tasks. In cells $C$7:$J$7, these should be the names of each staff member from the "ProcessingLog" sheet.

On Sheet: "ProcessingLog", I need a dynamic named range for the staff in cells $C$2:$AP$2. These cells are merged 5 columns wide.

I have attempted the formula in "Productivity!$C$7:$J$7", to no avail:
=INDEX(ProcessingLog!$C$2:$AP$2,1,COLUMN()-MOD(COLUMN()-1,5))

Attempting to do the following:
1. Create dynamic named range using merged cells: ProcessingLog!$C$2:$AP$2
2. Copy text without blanks or extra information, from dynamic named range to: Productivity!$C$7:$J$7
3. Match name on Productivity sheet to task (column B), then sum corresponding data on ProcessingLog! by 2 and 4 week intervals from giving date in cell: Productivity!$C$4

Please see the attached file.

Thank you!!
 

Attachments

  • ROI_Processing_Log-Example.xlsx
    112.8 KB · Views: 22
Hi, jdppep!
Could you post again the same file with the Productivity worksheet manually filled as per your requirements? Specially to discard ambiguities due to "without blanks or extra information" condition.
Regards!
 
Hi SirJB7!
Thanks for the reply. Please see the updated attachment.
Thanks!
 

Attachments

  • ROI_Processing_Log-Example.xlsx
    112.8 KB · Views: 14
Hi, jdppep!
So there were blanks, ha! As I supposed... :eek:
But am I wrong or not the data in 2nd worksheet isn't related at all with that of 1st worksheet? This last one has no data for Dec 2nd.
If I'm right upload again a new workbook with coherent information.
Regards!
 
The data in the second worksheet (Productivity!) should be the sum of the data in the first worksheet (ProcessingLog!). Frequently, the data will be incomplete as multiple users fill in their respective information on the ProcessingLog! sheet. Additionally, the staff typically do not work on weekends (the grey highlighted rows on ProcessingLog!); however, they do on occasion. Therefore, these date stamps will often be blank. Some staff only work a few days per week, etc.

The most recent information uploaded was several days ago (11/27). I want the Productivity! sheet to calculate 2 and 4-week sums, then divide by count of entries by respective staff during that time (days worked), regardless of completeness of the ProcessingLog! sheet.

Does this help?
 
Hi, jdppep!

Yes, it helps. In the meanwhile maybe you want to give a look at the following link, I've just ended to do something alike there:
http://chandoo.org/forum/threads/project-assistance.13500/#post-80680

Here at least you've yet defined the top, left and main dynamic named ranges, even the last should be tweaked to:
=DESREF(ProcessingLog!$C$6;0;0;FILAS(Date);COLUMNAS(Task)) -----> in english: =OFFSET(ProcessingLog!$C$6,,,ROWS(Date),COLUMNS(Task))
as it's fixed to 40 for the time being.

Regards!
 
Hi, jdppep!

Give a look at this file:
https://dl.dropboxusercontent.com/u..._Log-Example (for jdppep at chandoo.org).xlsx

It uses your 3 previous dynamic named ranges, but with a slightly change in their names since 2 of them were reserved words, even valid for names:
Date -----> DateList
Task -----> TaskList
Log -----> LogTable
and this 2 new ones:
MergedList: =DESREF(TaskList;-1;;1;) -----> in english: =OFFSET(TaskList,-1,,1,)
DateCell: =Productivity!$C$4

The unique formula is:
C9: =SUMA(INDIRECTO(DIRECCION(COINCIDIR(DateCell;DateList;0)+FILA(LogTable)-1;COINCIDIR(C$7;MergedList;0)-1+RESIDUO(FILA()+1;7)-2+COLUMNA(LogTable)-1;4;1;"ProcessingLog")&":"&DIRECCION(MIN(COINCIDIR(DateCell;DateList;0)+FILA(LogTable)-1+ENTERO((FILA()+1)/7)*7-1;FILA(LogTable)+FILAS(LogTable)-1);COINCIDIR(C$7;MergedList;0)-1+RESIDUO(FILA()+1;7)-2+COLUMNA(LogTable)-1;4;1))) -----> in english: =SUM(INDIRECT(ADDRESS(MATCH(DateCell,DateList,0)+ROW(LogTable)-1,MATCH(C$7,MergedList,0)-1+MOD(ROW()+1,7)-2+COLUMN(LogTable)-1,4,1,"ProcessingLog")&":"&ADDRESS(MIN(MATCH(DateCell,DateList,0)+ROW(LogTable)-1+INT((ROW()+1)/7)*7-1,ROW(LogTable)+ROWS(LogTable)-1),MATCH(C$7,MergedList,0)-1+MOD(ROW()+1,7)-2+COLUMN(LogTable)-1,4,1)))
which should be copied across and down as required, as long you keep the starting row and column for each group and the inter group spacing (actually 3 rows).

I filled the ProcessingLog worksheet with formula-generated values for testing purposes.

The orange areas in the Productivity worksheet (row 30 and columns M:N) can be deleted as they are only for testing purposes and to make it easy to understand the formula, since they are components referenced within it.

Column M: =ENTERO((FILA()+1)/7) -----> in english: =INT((ROW()+1)/7)
retrieves a 1 for the 1st 4-row group, 2 for the 2nd, and so on if necessary.

Column N: =RESIDUO(FILA()+1;7)-2 -----> in english: =MOD(ROW()+1,7)-2
retrieves a 1, 2, 3, 4 for each row of each group.

Row 30: =COINCIDIR(C7;MergedList;0) -----> in english: =MATCH(C7,MergedList,0)
retrieves the no. of group of the 5-column merged cells A, B, C, ... at 1st worksheet row 2.

Hence the structure of the formula is:
=SUM(INDIRECT('ProcessingLog'<1st_cell>:<2nd_cell>))

Where <1st_cell> is in the form of:
ADDRESS(MATCH(DateCell,DateList,0)+ROW(LogTable)-1,MATCH(C$7,MergedList,0)-1+MOD(ROW()+1,7)-2+COLUMN(LogTable)-1,4,1,"ProcessingLog")
i.e.,
ADDRESS(<row>,<column>,,4,1,<worksheet>)
with:
<row>: MATCH(DateCell,DateList,0)+ROW(LogTable)-1
<column>: <formula_of_row_30>-1+<formula_of_column_N>+COLUMN(LogTable)-1

<2nd_cell> is analog to <1st_cell> but adjusting the row as column M formula:
<row>: MATCH(DateCell,DateList,0)+ROW(LogTable)-1+<formula_of_column_M>-1
embedded into a MIN function for border conditions (end of table):
MIN(<row>,ROW(LogTable)+ROWS(LogTable)-1)

Just advise if any issue.

Regards!
 
Wow, thank you!! I have not had a chance to completely go through this. What formula can I use across CJ:J7 on Productivity! to retrieve the appropriate merged value?
 
Hi, jdppep!
File fixed and re-uploaded, download it again from same previous link.
Formula for C7: =INDICE(MergedList;1;(COLUMNA()-3)*5+1) -----> in english: =INDEX(MergedList,1,(COLUMN()-3)*5+1)
Regards!
 
Hi, jdppep!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: But please, next time don't use merged cells, they should be forbidden. In this case you were lucky, but don't abuse. Instead of that use Center On Selection, it has the same visual effect and doesn't mess with ranges.
 
Hi SirJB7!

I agree with the merged cell complication. I am trying to clean up someone else's mess; however, I have another application this solution will also fix.

I made a couple changes to the productivity sheet. When I added lines, the references changed and are no longer correct. I added a section which needs to sum the date ranges from the StartDate (F4) to the EndDate (H4).

I want the 2 and 4-week sums you previously worked on to be calculated based on the EndDate.

Please see the attachment. Thanks again!
 

Attachments

  • ROI_Processing_Log-Example.xlsx
    113.8 KB · Views: 16
Hi, jdppep!

Nop, you weren't supposed to do that. Remember this?
SirJB7 said:
The orange areas in the Productivity worksheet (row 30 and columns M:N) can be deleted as they are only for testing purposes and to make it easy to understand the formula, since they are components referenced within it.

Column M: =ENTERO((FILA()+1)/7) -----> in english: =INT((ROW()+1)/7)
retrieves a 1 for the 1st 4-row group, 2 for the 2nd, and so on if necessary.

Column N: =RESIDUO(FILA()+1;7)-2 -----> in english: =MOD(ROW()+1,7)-2
retrieves a 1, 2, 3, 4 for each row of each group.

Row 30: =COINCIDIR(C7;MergedList;0) -----> in english: =MATCH(C7,MergedList,0)
retrieves the no. of group of the 5-column merged cells A, B, C, ... at 1st worksheet row 2.
Click to expand...
So now the 1st group has a 1, then 2nd one a 2, and the 3rd one a 3, when these 2 last should have 1 and 2 for proper calculation. The original formula at N column has to be fixed for 2-4 week ranges (check the bold quote), and a new criteria has to be written for the from/to date range.

It'll take me a couple of days to find out the time, I guess.

Regards!
 
Since you were able to define the merged list, would it be possible to use a SUMIF function, based on multiple matching criteria to populate the productivity table(s)?
 
Hi, jdppep!
One thing is defining a range with merged cells and writing a formula that retrieves the relative position of each non-empty (1st) cell of each merged cell range, and another is using a function like SUMIF with criteria ranges rolling over merged cells.
I neither say yes or no, I haven't analyzed it yet and I don't imagine it right now, but you could give it a try and check it.
Regards!
 
You must log in or register to reply here.
Back
Top