Issue with references between cells

W

wimdaniel

New Member
Joined
Apr 4, 2011
Messages
3
Hello,

I have a problem with the references in my sheets, and I have tried to solve it on my own but I can't solve it.

The situation is that pupils have to answer 8 questions in a fixed following in a computer program. The output consists of the result of the question, their name and the duration on 1 row spread over 3 columns. Not every pupils will make 8 questions, some answer only 2 questions e.g.

<table border="0" cellpadding="0" cellspacing="0" width="192"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="3" class="xl24" style="height: 12.75pt; width: 144pt;" width="192" height="17">ORIGINAL DATA OUTPUT</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">result</td> <td>name</td> <td>time</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>john</td> <td class="xl24">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">incorrect</td> <td>john</td> <td class="xl24">120</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl24">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl24">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">incorrect</td> <td>mike</td> <td class="xl24">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl24">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">incorrect</td> <td>mike</td> <td class="xl24">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl24">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">incorrect</td> <td>mike</td> <td class="xl24">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>pete</td> <td class="xl24">100</td> </tr> </tbody></table>

What I want is the following:
<table border="0" cellpadding="0" cellspacing="0" width="256"><col style="width: 48pt;" width="64" span="4"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">question</td> <td style="width: 48pt;" width="64">result</td> <td style="width: 48pt;" width="64">name</td> <td style="width: 48pt;" width="64">time</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">1</td> <td>john</td> <td>correct</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">2</td> <td>john</td> <td>incorrect</td> <td align="right">120</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">3</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">4</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">5</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">6</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">7</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">8</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">1</td> <td>correct</td> <td>mike</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">2</td> <td>correct</td> <td>mike</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">3</td> <td>incorrect</td> <td>mike</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">4</td> <td>correct</td> <td>mike</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">5</td> <td>incorrect</td> <td>mike</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">6</td> <td>correct</td> <td>mike</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">7</td> <td>incorrect</td> <td>mike</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">8</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">1</td> <td>pete</td> <td>correct</td> <td align="right">100</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">2</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">3</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">4</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">5</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">6</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">7</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">8</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
The blanc rows habe no content in the cells. Does anybody how to solve this problem?

(unfortunately I can't attach my excel file in this thread)

Thanks in advance.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Try this:-
Results start "D1".
Code: 
[COLOR="Navy"]Sub[/COLOR] MG04Apr34
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Range("D1:G1") = Array("Question", "Result", "Name", "Time")
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
        ReDim ray(1 To Rng.Count * 8, 1 To 4)
            [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
                .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        n = n + IIf(n = 0, 1, 8)
        .Add Dn.Value, Array(n, 0)
        c = 0
            [COLOR="Navy"]For[/COLOR] Rw = n To n + 7
                c = c + 1
                    [COLOR="Navy"]If[/COLOR] Rw = n [COLOR="Navy"]Then[/COLOR]
                         ray(Rw, 1) = c: ray(Rw, 2) = Dn.Offset(, -1): ray(Rw, 3) = Dn: ray(Rw, 4) = Dn.Offset(, 1)
                    [COLOR="Navy"]Else[/COLOR]
                         ray(Rw, 1) = c
                    [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Rw
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
        Q(1) = Q(1) + 1
        ray(Q(0) + Q(1), 2) = Dn.Offset(, -1): ray(Q(0) + Q(1), 3) = Dn: ray(Q(0) + Q(1), 4) = Dn.Offset(, 1)
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("D2").Resize(n + 7, 4) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thank you very much Mick,

Your solution is great!!!

I was hoping that I could use your solution with 3 more columns that I created from the original dataset in excel; however, I don't know what to change since I'm only used to work with formula.

How does your solution looks like when this is the ' original dataset'

<table border="0" cellpadding="0" cellspacing="0" width="393"><col style="width: 48pt;" width="64" span="3"> <col style="width: 55pt;" width="73"> <col style="width: 48pt;" width="64" span="2"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">result </td> <td style="width: 48pt;" width="64">name</td> <td style="width: 48pt;" width="64">time</td> <td style="width: 55pt;" width="73">whole route</td> <td style="width: 48pt;" width="64">help route</td> <td style="width: 48pt;" width="64">attempts</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>adam</td> <td class="xl22">100</td> <td align="right">12345</td> <td align="right">123</td> <td align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">incorrect</td> <td>mike</td> <td class="xl22">120</td> <td align="right">45</td> <td>
</td> <td align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl22">100</td> <td align="right">12345</td> <td align="right">123</td> <td align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl22">100</td> <td align="right">45</td> <td>
</td> <td align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl22">100</td> <td align="right">12345</td> <td align="right">123</td> <td align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">incorrect</td> <td>mike</td> <td class="xl22">100</td> <td align="right">32145</td> <td align="right">321</td> <td align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>pete</td> <td class="xl22">100</td> <td align="right">145</td> <td align="right">1</td> <td align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>john</td> <td class="xl22">100</td> <td align="right">12345</td> <td align="right">123</td> <td align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">incorrect</td> <td>john</td> <td class="xl22">120</td> <td align="right">12345</td> <td align="right">123</td> <td align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl22">100</td> <td align="right">1245</td> <td align="right">12</td> <td align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>mike</td> <td class="xl22">100</td> <td align="right">45</td> <td align="right">45</td> <td align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">incorrect</td> <td>mike</td> <td class="xl22">100</td> <td align="right">45</td> <td align="right">45</td> <td align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">correct</td> <td>rooney</td> <td class="xl22">150</td> <td align="right">1345</td> <td align="right">13</td> <td align="right">1</td> </tr> </tbody></table>
This is de (final) requested ouput:
<table border="0" cellpadding="0" cellspacing="0" width="448"><col style="width: 48pt;" width="64" span="7"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">question</td> <td style="width: 48pt;" width="64">result </td> <td style="width: 48pt;" width="64">name</td> <td style="width: 48pt;" width="64">time</td> <td style="width: 48pt;" width="64">whole route</td> <td style="width: 48pt;" width="64">help route</td> <td style="width: 48pt;" width="64">attempts</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td>correct</td> <td>adam</td> <td align="right">100</td> <td align="right">12345</td> <td align="right">123</td> <td align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">2</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">3</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">4</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">5</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">6</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">7</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">8</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" align="right" height="17">1</td> <td>incorrect</td> <td>mike</td> <td align="right">120</td> <td align="right">45</td> <td>
</td> <td align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>etc..</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
Thanks in advance.

Regards Wim
 
Upvote 0
Hi, Try this:-
Change sheet Names in code where shown.!!
Code: 
[COLOR="Navy"]Sub[/COLOR] MG04Apr28
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
 
 With Sheets("Sheet4") '[COLOR="Green"][B]Change to data sheet name[/B][/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
      [COLOR="Navy"]End[/COLOR] With
        ReDim ray(1 To Rng.Count * 8, 1 To 7)
            [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
                .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        n = n + IIf(n = 0, 1, 8)
        .Add Dn.Value, Array(n, 0)
        c = 0
            [COLOR="Navy"]For[/COLOR] Rw = n To n + 7
                c = c + 1
                    [COLOR="Navy"]If[/COLOR] Rw = n [COLOR="Navy"]Then[/COLOR]
                         ray(Rw, 1) = c: ray(Rw, 2) = Dn.Offset(, -1): ray(Rw, 3) = Dn: ray(Rw, 4) = Dn.Offset(, 1)
                         ray(Rw, 5) = Dn.Offset(, 2): ray(Rw, 6) = Dn.Offset(, 3): ray(Rw, 7) = Dn.Offset(, 4)
                    [COLOR="Navy"]Else[/COLOR]
                         ray(Rw, 1) = c
                    [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] Rw
    [COLOR="Navy"]Else[/COLOR]
        Q = .Item(Dn.Value)
        Q(1) = Q(1) + 1
        ray(Q(0) + Q(1), 2) = Dn.Offset(, -1): ray(Q(0) + Q(1), 3) = Dn: ray(Q(0) + Q(1), 4) = Dn.Offset(, 1)
        ray(Q(0) + Q(1), 5) = Dn.Offset(, 2): ray(Q(0) + Q(1), 6) = Dn.Offset(, 3): ray(Q(0) + Q(1), 7) = Dn.Offset(, 4)
        .Item(Dn.Value) = Q
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
With Sheets("sheet5") '[COLOR="Green"][B]Change to results sheet name[/B][/COLOR]
  .Range("A2").Resize(n + 7, 7) = ray
  .Range("A1:G1") = Array("Question", "Result", "Name", "Time", "Whole Route", "Help Route", "Attemps")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks a lot Mick,

I'm very pleased with your aid, and I managed to make it work!

Problem solved!

Regards Wim
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
619
smozgur
smozgur
N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
111
Peter_SSs
Peter_SSs
T
  • Question Question
Running Average of last n row if sum is between a certain value
Replies
1
Views
72
Peter_SSs
Peter_SSs
L
  • Question Question
How to create a column that adds cells with a cell in common
Replies
6
Views
89
Fluff
Fluff
F
  • Question Question
VBA Macro Issue: Replacing Simple Formula with Complex Formula Causes Error
Replies
2
Views
103
flashgordie
F

Forum statistics

Threads
1,224,548
Messages
6,179,448
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top