Formulas across worksheets

[etm2337]

Hello,

I'm trying to create a "Summary" worksheet by pulling data from other worksheets with in the file. The worksheets are all journals and there is a column in each worksheet that lists components from a dropdown. In the summary worksheet I want to create a spreadsheet where you can easily see if a journal contains a component, plus be able to see all the journals that contain that same component.

First, I created a list in column 'A' that is a listing of all the worksheets by using a formula. Then I listed the components across the top of the columns (the same list that is in the dropdown on the journals worksheets). I'm trying to figure out the correct formula under the component listing that will search the journals worksheet and put a tick mark in the column if it exists and leave it blank if it does not.

Is there an "easy" way to do this?

TIA

 

[Panda514]

Hello,

Does your Summary worksheet have a list of each journal? For example-Journal 1, Journal 2, Journal 3 and those Journal's correspond with worksheets like Sheet 1, Sheet 2, Sheet 3? If so, I think you could use a vlookup that lookups the component across the top of the columns.

I'm assuming on the worksheet for each journal, the components are all listed in column A. I highlighted the sheet name in red to remind you that the sheet should be whatever sheet your Journal Name is on. The blue shows you that you are looking up the Component Name in the top row. The Component name must be the same as how it would appear on the other sheets.

Summary:

Journal Name	Component 1	Component 2	Component 3
Journal 1	=IFNA(IF(VLOOKUP($B$1,Sheet1!$A:$A,1,FALSE)<>"",1,0),"")	=IFNA(IF(VLOOKUP($C$1,Sheet1!$A:$A,1,FALSE)<>"",1,0),"")	=IFNA(IF(VLOOKUP($D$1,Sheet1!$A:$A,1,FALSE)<>"",1,0),"")
Journal 2	=IFNA(IF(VLOOKUP($B$1,Sheet2!$A:$A,1,FALSE)<>"",1,0),"")	=IFNA(IF(VLOOKUP($C$1,Sheet2!$A:$A,1,FALSE)<>"",1,0),"")	=IFNA(IF(VLOOKUP($D$1,Sheet2!$A:$A,1,FALSE)<>"",1,0),"")

<tbody>
</tbody>

Example of Sheet 1 for Journal 1:

Components

Component 1

Component 2

Component 3

<tbody>
</tbody>

 

[etm2337]

Thank you for the response. I kept trying to get this to work using this '=IF(ISNUMBER(SEARCH(B$1,Sheet1!B:B))," ","X")', to no avail. I ended up using this function and it works as I was looking for: =IF(COUNTIF(INDIRECT($A2&"!B:B"),B$1)>0,"X","")