Lookup a value from a certain cell across many worksheets

A

AusSteelMan

Board Regular
Joined
Sep 4, 2009
Messages
208
Hi every one,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>


I have the following set-up: <o:p></o:p>
  • Summary sheet <o:p></o:p>
  • 26 data sheets (labelled Data1 to Data26) which each represent 1 cycle of data
  • 2 hidden sheets:
    • "FirstSheetBeforeData" and "MustBeLastSheetAfterData"
    • All the data sheets are located between these 2
    • The summary sheet is before the "First..."
  • Each Data sheet has 30 records (rows) of information over about 25 columns. The same 30 records (and columns) are on each cycle. <o:p></o:p>
  • Column V on each data sheet has either "Yes" or "No" in it on each row. It will only ever have these options. There will be only 1 "Yes" for each record across all 26 data sheets (and therefore 25 "No"s)<o:p></o:p>
What I would like to do is: <o:p></o:p>
  • Have the summary sheet determine which Data sheet has the "Yes" for each record.<o:p></o:p>
I have done similar things with adding values across a range of sheets, using a formula like: <o:p></o:p>
Code: 
=SUM(FirstSheetBeforeData:MustBeLastSheetAfterData!R9)
<o:p></o:p>
to add up all the R9 cells of the data sheets located between the "First..." and "Last..." sheets.

Does anyone have any tips on how is it possible?

I won't confuse the post by adding other info, since I think this covers it. But if you think there may be something else, please let me know.

Many thanks in advance.
Darren
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
AusSteelMan,

Will the "Yes" in column V, say row 3, be the same for all sheets labelled Data1 to Data26?

What cell in sheet Summary will get this formula:
=SUM(FirstSheetBeforeData:MustBeLastSheetAfterData!R3)

Are there titles in row 1 for all sheets labelled Data1 to Data26?
 
Upvote 0
So I have used a helper column (Col AA) for each record on each data sheet.

Code: 
=IF(V17="Yes",VALUE(MID($A$1,FIND(" ",$A$1)+1,2)),0)
where:
V17 is the "Yes" or "No" for that record
A1 conatins the Sheet name (but with a space) ie "Data 25"

The formula extracts the characters after the space (as text) if the value in Col V is "Yes" and turns it to a value.

Then the Summary sheet sums all the same record values from all 26 sheets. ie 0+0+0+...+25+0=25

It seems a little crude, but works. If anyone knows a better way, please let me know.

The other issue is if there is more than one "yes" (this is not allowed). My solution to this is another helper column (Col AB) on each record on each sheet that is assigned a value of 1 if the value in Col V is "yes". The Summary sheet sums these cells and if the sum>1, then a message is displayed.



Hiker95,
I was typing this reply to my post when yours came in.

Q1: Yes, the cell address for the same record name will be the same on all Data sheets. To be clear, every data sheet will be laid out identically.

Q2: It can be R3 if it needs to be, initially anyway. I intend on sorting the records on the summary sheet based on a value on another column (say Col C). This sort will be actioned after each sheet gets data. The data will be populated into the Data sheets once a week.

I am OK with the SUMming across all sheets, but am struggling with being able effectively "LOOKUP" the same cell across 26 sheets to determine which sheet says "Yes"

A bit more info that might be relevant:
I am building this workbook at the moment. The current workbook is poorly organised and not very intuitive to use.
So for the new workbook:
On each data sheet, Col W will be an addition of several other columns for each record.
The summary sheet collects each sheet's data from Col W and is placed in a column that is labelled the data sheet nunmber eg "26".
Col C on the summary adds all columns that contain the data sheet summary values (eg Col E to AD (26 columns).
The sheet will then be sorted based on the value of Col C

Hope all that makes enough sense (I can Excel Genie if required)

Q3: There are Titles is row 16 (there is other guff between rows 1 and 14)
 
Upvote 0
AusSteelMan,


Based on your original request, and your answers to my questions.


Sample data on three worksheets before the macro:


Excel Workbook
R
3
Summary





Excel Workbook
RSTUV
9101Yes
Data1





Excel Workbook
RSTUV
9126Yes
Data26





Worksheet Summary after the macro is run:


Excel Workbook
R
3227
Summary





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


Code: 
Option Explicit
Sub UpdateSum()
' hiker95, 09/06/2010, ME493130
Dim c As Range, firstaddress As String, SumRow As Long
Application.ScreenUpdating = False
SumRow = 0
With Worksheets("Data1").Columns(22)
  Set c = .Find("Yes", LookIn:=xlValues, LookAt:=xlWhole)
  If Not c Is Nothing Then
    firstaddress = c.Address
    Do
      SumRow = c.Row
      Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
  End If
End With
If SumRow = 0 Then SumRow = 1
Worksheets("Summary").Range("R3").Formula = "=SUM(FirstSheetBeforeData:MustBeLastSheetAfterData!R" & SumRow & ")"
Application.ScreenUpdating = True
End Sub


Then run the "UpdateSum" macro.
 
Upvote 0
Hiker95,

Thankyou very much for spending time to help me, and please accept my apologies as I think I was not clear in what it is I am after.

Here are some extracts of simplified [mock] workbook:

Summary


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 83px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Name</TD><TD style="TEXT-ALIGN: center">Total</TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">Factor on Sheet</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">Bill</TD><TD style="TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">Bob</TD><TD style="TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">Jenny</TD><TD style="TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">Sue</TD><TD style="TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B11</TD><TD>=SUM(C11:G11)</TD></TR><TR><TD>C11</TD><TD>=VLOOKUP($A11,Data1!$A$17:$J$20,10,FALSE)</TD></TR><TR><TD>D11</TD><TD>=VLOOKUP($A11,Data2!$A$17:$J$20,10,FALSE)</TD></TR><TR><TD>E11</TD><TD>=VLOOKUP($A11,Data3!$A$17:$J$20,10,FALSE)</TD></TR><TR><TD>F11</TD><TD>=VLOOKUP($A11,Data4!$A$17:$J$20,10,FALSE)</TD></TR><TR><TD>G11</TD><TD>=VLOOKUP($A11,Data5!$A$17:$J$20,10,FALSE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Data1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Name</TD><TD>A Bunch of numbers being added up</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Sum</TD><TD>Factor</TD><TD>Total</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">7</TD><TD>Yes</TD><TD style="TEXT-ALIGN: right">14</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Bob</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD>No</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Jenny</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6</TD><TD>No</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>Sue</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">2</TD><TD>No</TD><TD style="TEXT-ALIGN: right">2</TD></TR></TBODY></TABLE>


<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H17</TD><TD>=SUM(B17:G17)</TD></TR><TR><TD>J17</TD><TD>=IF(I17="Yes",2*H17,H17)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Data2

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Name</TD><TD>A Bunch of numbers being added up</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Sum</TD><TD>Factor</TD><TD>Total</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD>No</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Bob</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD>No</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Jenny</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD>No</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>Sue</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">4</TD><TD>No</TD><TD style="TEXT-ALIGN: right">4</TD></TR></TBODY></TABLE>
Data3

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Name</TD><TD>A Bunch of numbers being added up</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Sum</TD><TD>Factor</TD><TD>Total</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">2</TD><TD>No</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Bob</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Yes</TD><TD style="TEXT-ALIGN: right">8</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Jenny</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD>No</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>Sue</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">4</TD><TD>No</TD><TD style="TEXT-ALIGN: right">4</TD></TR></TBODY></TABLE>
Data4

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Name</TD><TD>A Bunch of numbers being added up</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Sum</TD><TD>Factor</TD><TD>Total</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD>No</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Bob</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD>No</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Jenny</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">6</TD><TD>Yes</TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>Sue</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">3</TD><TD>No</TD><TD style="TEXT-ALIGN: right">3</TD></TR></TBODY></TABLE>
Data5

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Name</TD><TD>A Bunch of numbers being added up</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Sum</TD><TD>Factor</TD><TD>Total</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD>Bill</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">3</TD><TD>No</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD>Bob</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">5</TD><TD>No</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD>Jenny</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">4</TD><TD>No</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD>Sue</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">2</TD><TD>Yes</TD><TD style="TEXT-ALIGN: right">4</TD></TR></TBODY></TABLE>


What I would like is Col H on the Summary sheet.
It finds which sheet the "Yes" is on for each person and puts the sheet number into Col H.

The previous posts about SUMming across sheets was my start point for trying to figure out how a multisheet lookup is possible. I tried a couple of formulas using LOOKUP, VLOOKUP and this COUNTIF
Code: 
=COUNTIF(Data1:Data5!I17,Yes)
The Lookups returned #NAME? (and I expected as much)
The COUNTIF returned #VALUE! (which I saw as maybe being part the way there)

My only other thing I can think of is to have a helper sheet "Factor" that looks similar in layout to summary, but is populated with the value of Col I from each sheet. Then the Summary sheet could determine which column has the Yes and put that value into Col H

Something like this maybe

Factor

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 83px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Name</TD><TD></TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">Factor on Sheet</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">Bill</TD><TD></TD><TD style="TEXT-ALIGN: center">Yes</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">Bob</TD><TD></TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">Yes</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">Jenny</TD><TD></TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">Yes</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">Sue</TD><TD></TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">Yes</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G11</TD><TD>=VLOOKUP($A11,Data5!$A$17:$J$20,9,FALSE)</TD></TR><TR><TD>H11</TD><TD>=HLOOKUP("Yes",$C11:$G$15,5,FALSE)</TD></TR><TR><TD>G12</TD><TD>=VLOOKUP($A12,Data5!$A$17:$J$20,9,FALSE)</TD></TR><TR><TD>H12</TD><TD>=HLOOKUP("Yes",$C12:$G$15,4,FALSE)</TD></TR><TR><TD>G13</TD><TD>=VLOOKUP($A13,Data5!$A$17:$J$20,9,FALSE)</TD></TR><TR><TD>H13</TD><TD>=HLOOKUP("Yes",$C13:$G$15,3,FALSE)</TD></TR><TR><TD>G14</TD><TD>=VLOOKUP($A14,Data5!$A$17:$J$20,9,FALSE)</TD></TR><TR><TD>H14</TD><TD>=HLOOKUP("Yes",$C14:$G$15,2,FALSE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>



I note that each HLOOKUP formula has to be custom edited for the row number to return the value from. Not a big deal when there are 4 records, but if there was, say, 50 it would be a little time consuming.

Additionally, I foresee the records (names in this example) being added to via macro to all sheets (somehting I have done before), so even just having the HLOOKUP needing to use the bottom row will probably cause me issues.

Is there a better way to do this step, or is there another way altogether?

Maybe I have finally answered my own question. What do you think is a [reasonably] efficient method?

I really do appreciate your time and suggestions.

Thanks,
Darren
 
Upvote 0
I updated to Factor sheet so that the HLOOKUP row does not need to be manually adjusted

Factor

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial Narrow,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 83px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: center">Name</TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">Factor on Sheet</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: center">Bill</TD><TD> </TD><TD style="TEXT-ALIGN: center">Yes</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: center">Bob</TD><TD> </TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">Yes</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="TEXT-ALIGN: center">Jenny</TD><TD> </TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">Yes</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: center">Sue</TD><TD> </TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">No</TD><TD style="TEXT-ALIGN: center">Yes</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>H11</TD><TD>=HLOOKUP("Yes",$C11:$G$15,COUNTA($G11:$G$14)+1,FALSE)</TD></TR><TR><TD>H12</TD><TD>=HLOOKUP("Yes",$C12:$G$15,COUNTA($G12:$G$14)+1,FALSE)</TD></TR><TR><TD>H13</TD><TD>=HLOOKUP("Yes",$C13:$G$15,COUNTA($G13:$G$14)+1,FALSE)</TD></TR><TR><TD>H14</TD><TD>=HLOOKUP("Yes",$C14:$G$15,COUNTA($G14:$G$14)+1,FALSE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Still not sure if this is a good idea or not anyway.

Thanks
Darren
 
Upvote 0
You must log in or register to reply here.

Similar threads

Skrej
  • Solved
All instances of a value tied to a specific entry across multiple sheets
Replies
7
Views
389
Fluff
Fluff
M
  • Solved
PDF creation of different sheet based on column value.
Replies
2
Views
261
mummbles74
M
J
  • Locked
  • Question
Still need help on a daily summary sheet
Replies
1
Views
287
Fluff
Fluff
M
  • Question
Lookups that don't shift when new columns are inserted
Replies
7
Views
341
Peter_SSs
Peter_SSs
G
  • Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
273
GardianAngelUK
G

Forum statistics

Threads
1,214,805
Messages
6,121,656
Members
449,045
Latest member
Marcus05

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