• 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.

Linking issue

C

cyberx1

New Member
I have an issue that I can't seem to resolve.
I have a single workbook with 80 tabs containing a fill-in form. Each tab is filled in by a seperate user. I also have a linked rollup tab that has a row for each tab.
Here is my challenge - currently there is a cell on each tab that has several entries (no more than 12) representing quality review errors, all bunched together in one cell. What I tried to do on a test file is include 24 cells (one for the reference number and once for the spelled out error) so each review error could be entered in its own cell. Now this part works fine, but my main issue now is how do I roll this additional data up via linking so I have continguous records that I can pivot?
Thank you for any suggestions.
Mark
 
json said:
eerrrhmmm.... can you possibly upload a sample file?
Click to expand...
Oh, sure.

I hope the sample file is clear. Each tab represents a single record, but I'm a little lost on how to acount for the multiple error codes and error descriptions on the rollup tab, as I wish to keep all of the data as contiguous as possible so I can pivot the data and otherwise use the data for other reports.

Thnak you kindly.

Mark
 

Attachments

  • Rollup.xlsx
    10.5 KB · Views: 12
json said:
eerrrhmmm.... can you possibly upload a sample file?
Click to expand...

Oh, each tab can contain from 1 to 12 errors (average is about 2 or so). Also, actual tab is a word-like format, but cell references are made from the Rollup tab to form a single records for each tab (that is the intent anyways).

Mark
 
Hi, cyberx1!

A few questions:

a)
cyberx1 said:
I have a single workbook with 80 tabs containing a fill-in form. Each tab is filled in by a seperate user. I also have a linked rollup tab that has a row for each tab.
Click to expand...
A row for each tab or 12 rows for each tab?

b)
cyberx1 said:
Here is my challenge - currently there is a cell on each tab that has several entries (no more than 12) representing quality review errors, all bunched together in one cell.
Click to expand...
In one cell or in 12 cells?

c)
cyberx1 said:
What I tried to do on a test file is include 24 cells (one for the reference number and once for the spelled out error) so each review error could be entered in its own cell. Now this part works fine, but my main issue now is how do I roll this additional data up via linking so I have continguous records that I can pivot?
Click to expand...
I don't understand this. the 24 cells are those of your uploaded file? I guess that they aren't.

d)
cyberx1 said:
Also, actual tab is a word-like format, but cell references are made from the Rollup tab to form a single records for each tab (that is the intent anyways).
Click to expand...
Same as previous, what do you mean by word-like format?

e) Which would be the worksheets name for each person? Like in your uploaded file or with any name structure?

Consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!
 
SirJB7 said:
Hi, cyberx1!

A few questions:

a)
A row for each tab or 12 rows for each tab?

b)
In one cell or in 12 cells?

c)
I don't understand this. the 24 cells are those of your uploaded file? I guess that they aren't.

d)
Same as previous, what do you mean by word-like format?

e) Which would be the worksheets name for each person? Like in your uploaded file or with any name structure?

Consider uploading (a) sample file(s) (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Regards!
Click to expand...
--------------------------
Hi.

Thank you all for your patience.

Please notice on the sample file that each tab shows 12 cells down for the Error Reference Number and 12 cells down for the Error Description - this is what I meant by 24 cells - sorry for the confusion.

In repsonse to above (categorically):

a) - c) This is the solution that I am seeking. I'm trying to configure the file so that I can have one linked record on the rollup tab for each tab, but becasue user can enter more than one Error Reference Number and corresponding Error Description (average is 2 entires per tab, but can be up to 12), I'm having difficulty in setting up the rollup tab for pivot table run. Maybe I need to create a record for each tab by repeating all of the linking cell data with the exception of the Error Reference number and Error Description - just have 12 entires irregardless of number of actul error entries made - just not sure. Ideally I'd like to have onle the nmber of actual Error Reference nUmber and Error Description data instead of wasting 12 rows on the rollup tab for each record (this is 960 rows of data!).

d) Meaning that each tab is set up like a form with text (i.e. name, error type, etc.). Each of the user entered cells is linked to the Rollup tab as a row with the intent to have one record per tab, but this more that one error is causing me difficulties in term of design. I think that I illustrated this on the sample file.

e) Each tab (not including the rollup tab) represents a person (user who enters data into this tab). 80 tabs = 80 people entering data in Excel shared mode - this part works just fine.

I hope that I have cleared up some understadning here. I think the solution is easy but I also think that I have developed soluton scope creep and this is why I'm seeking assistance.

I am more than willing to answer any additional clarifications as asked.

Thank you again and I really anticpate your responses(s).

Mark in Plano, Texas :)
 
Hi, cyberx1!

So:
a) You have 12 rows with 2 columns for each tab (as in the sample file and not as in the issue description).
b), c) & d) Check the 3 different layouts in my uploaded file and confirm which one you want, if light orange, light blue or light lile (try to avoid the 3rd. one).
e) You didn't answered which would be the names of the 80 worksheets/tabs (if Sample 1, Sample 2, ..., or any non structured name)

Regards!
 

Attachments

  • Linking issue - Rollup (for cyberx1 at chandoo.org).xlsx
    11.5 KB · Views: 5
SirJB7 said:
Hi, cyberx1!

So:
a) You have 12 rows with 2 columns for each tab (as in the sample file and not as in the issue description).
b), c) & d) Check the 3 different layouts in my uploaded file and confirm which one you want, if light orange, light blue or light lile (try to avoid the 3rd. one).
e) You didn't answered which would be the names of the 80 worksheets/tabs (if Sample 1, Sample 2, ..., or any non structured name)

Regards!
Click to expand...
Thank you.

Each tab (with exception of rollup tab) stands for an employees's name. The actual file would have 80 different tabs, one for each empoyee, one rollup tab, and one pivot table tab based on the rollup tab.

I like the light orange option. This would work, correct, if an employees enters either 1 or up to 12 errors? The problem that I am having is tryign how to only get the number of rows where there is an error entry for a given record. So if there arer 7 entires, then only those seven entries would be captured. I'm assuming that the other cells will contains the exact same data to faciltate a pivot rollup. So, if an employee enters 7 errors (that is 1 error reference number and the corresponding error description for reach error), then the rollup woudl essention shows 7 seeprate records for this, with only the error refernec number and error description chnaging on each record, correct?

I think my biggest challenge is how to only get the number of errors from each employee tab where there are error entires and ignoring the remainder and going on to the next record (all via linking). This is my original thinking anyways - you may have a solution or different approach.

Mark
 
Hi Mark,

As I can see or understood by your sample file and the solution (format) provided by SirJB7, you can get the view of format (light orange color format in excel file sent by SirJB7) by using filter. But I think the view format is not the issue (or requirement) and you want to use this 'rollup' sheet data for pivot table. Are you not able to use this data format for Pivot table and hence you wanted to remove blank fields in 'rollup' sheet? Or the format you wanted is your requirement for any other purpose?

Please rectify me if I am incorrect.
 
Balli said:
Hi Mark,

As I can see or understood by your sample file and the solution (format) provided by SirJB7, you can get the view of format (light orange color format in excel file sent by SirJB7) by using filter. But I think the view format is not the issue (or requirement) and you want to use this 'rollup' sheet data for pivot table. Are you not able to use this data format for Pivot table and hence you wanted to remove blank fields in 'rollup' sheet? Or the format you wanted is your requirement for any other purpose?

Please rectify me if I am incorrect.
Click to expand...
Hi.

Yes, precisely.
 
cyberx1 said:
Hi.

Yes, precisely.
Click to expand...

But I think the view format is not the issue (or requirement) and you want to use this 'rollup' sheet data for pivot table. Are you not able to use this data format for Pivot table and hence you wanted to remove blank fields in 'rollup' sheet?
 
Last edited by a moderator:
Hi Mark,

I have worked on your file and created a pivot table. Is this format you are looking for?

I have added some extra link to data for this.
 

Attachments

  • Rollup.xlsx
    14.7 KB · Views: 5
Balli said:
Hi Mark,

I have worked on your file and created a pivot table. Is this format you are looking for?

I have added some extra link to data for this.
Click to expand...
Thnak you Balli.

Close - but I'm trying to find a way to capture the errors with the zeroes. in other words, I want to only capture the actual numebr of types errors by the user on a given tab onto the rollup tab, and not capture the remaining cells with no values. For example, if Employee A (say Sample1 tab) only enters 3 Error Reference Numbers and 3 corresponding Error Descriptions (like on the Rollup.xlsx file), I only want to capture these two combinations onto the rollup tab and not the remaining 10 error cells with no values. I appreciate your help on this. I'm an intermediate to advanced (non-programming) Excel user and I just can't seem to figure this one out but I'm up against a deadline.
 
"I'm trying to find a way to capture the errors with the zeroes"....I am little confused you want with or without zeroes.
If you want only actual values like 3 error & 3 error descriptions in "Sample 1 tab", the Pivot (attached) is showing the same thing.
Now you please explain what is the issue / problem if (suppose in "Sample 1 tab") there is remaining 10 error cells (without value) are exist in "Rollup" tab. Because as I understood u want to use this sheet ("rollup" tab) for Pivot table and the 10 error cells (without value) will not create any problem in your Pivot Table or Pivot Table view because we have option to filter.
 

Attachments

  • Rollup.xlsx
    14.6 KB · Views: 4
Balli said:
"I'm trying to find a way to capture the errors with the zeroes"....I am little confused you want with or without zeroes.
If you want only actual values like 3 error & 3 error descriptions in "Sample 1 tab", the Pivot (attached) is showing the same thing.
Now you please explain what is the issue / problem if (suppose in "Sample 1 tab") there is remaining 10 error cells (without value) are exist in "Rollup" tab. Because as I understood u want to use this sheet ("rollup" tab) for Pivot table and the 10 error cells (without value) will not create any problem in your Pivot Table or Pivot Table view because we have option to filter.
Click to expand...
Thank you Kindly Balli.

Pivot table looks nicer, but my real issue still remains...notice on the Rollup tab that rows 5-13 and 17-25 still contain zeros. What I am looking to do is have each record with active error data show contiguously. For example, rows 2-5, then rows 13-16 to show as rows 2-7 with no blank rows. Please see the rollup tab for an illustration of what I need the rollup tab to look like (so although the data shows as is on the Sample tabs, which is fine, we need to somehow get rid of the rows without active errro data on the rollup tab). I'm sure some combination of functions can accomplish this task, just not sure what that combination is. If I can't find a solution then I know I have no choice than to use the file as you have contructed, and I also have constructed in a very similar manner. I need the rows to show contiguoulsy so I can use the data for other purposes besides a pivot table, although I will stil be using the pivot table as well. We're almost there. Thank you for yoru continued insight into resolviong this issue. :)
 

Attachments

  • Rollup1.xlsx
    14.7 KB · Views: 1
Narayan Sir file had a macro.
I had assign it to a button on rollup sheet. Just press it, data will appear.

Regards,
 

Attachments

  • Rollup(1).xlsm
    19.8 KB · Views: 1
cyberx1 said:
Oh, I see. Had to open macro window. Will this macro work if any if any tab has no record?

Mark :)
Click to expand...

Somendra Misra said:
Narayan Sir file had a macro.
I had assign it to a button on rollup sheet. Just press it, data will appear.

Regards,
Click to expand...
After entering a test 3rd tab called Sample3, and including just the heading and no data, I got a type mismatch error when running the macro. It is possible that a tab cold have no data, but all tabs wil have headings.

Mark
 
Just see the attached file. I had done two things.
1. Added an an extra sheet Sample3 with a simple formula in column A &B.
2. Added few line of codes in macro.

Just see if this is working.

I know Narayan Sir will come with some better code that I had proposed as soon as he will see this. Till than bear with mine code ;)

Regards,
 

Attachments

  • Rollup(1).xlsm
    22.7 KB · Views: 3
Seems
Somendra Misra said:
Just see the attached file. I had done two things.
1. Added an an extra sheet Sample3 with a simple formula in column A &B.
2. Added few line of codes in macro.

Just see if this is working.

I know Narayan Sir will come with some better code that I had proposed as soon as he will see this. Till than bear with mine code ;)

Regards,
Click to expand...
Seems to work OK. But when I add another tab, I get a type mismatch error when running macro again.

We're getting very close.

Mark
 
You must log in or register to reply here.
Back
Top