VLOOKUP over multiple sheets

[Guest]

I have a workbook with a summary tab and one tab for ever week.

I need to write a formula that looks for a value from column a, find the
sheet with a matching value in cell e4 and returns the value of cell ac1 of
the worksheet.

This formula will live in column b of the summary sheet.

Example:

Summary Tab

A B
1 19-Jul 19,000(formula result)
2 16-Jul
3 23-Jul
4 30-Jul

Data Tab 1

E AC
1 9-Jul 19,000

It will need to look in cell E1 of every tab in the work book (52 tabs) for
the value found in $a1.

 

[Peo Sjoblom]

Does the lookup value occur more than once in all of the sheets?

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Peo Sjoblom]

Does the lookup value occur more than once in all of the sheets?

--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Biff]

Hi!

One way:

Create a list of the sheet names:

H1 = Sheet2
H2 = Sheet3
H3 = Sheet4
H4 = Sheet5

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDIRECT("'"&INDEX(H$1:H$4,MATCH(TRUE,COUNTIF(INDIRECT("'"&H$1:H$4&"'!E4"),A1)>0,0))&"'!AC1")

Biff

 

[Biff]

Hi!

One way:

Create a list of the sheet names:

H1 = Sheet2
H2 = Sheet3
H3 = Sheet4
H4 = Sheet5

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=INDIRECT("'"&INDEX(H$1:H$4,MATCH(TRUE,COUNTIF(INDIRECT("'"&H$1:H$4&"'!E4"),A1)>0,0))&"'!AC1")

Biff

 

[Biff]

matching value in cell e4 look in cell E1 of every tab

Which is it, E1 or E4? I used E4 in the formula.

Biff

 

[Biff]

matching value in cell e4 look in cell E1 of every tab

Which is it, E1 or E4? I used E4 in the formula.

Biff

 

[Guest]

No the value of E1 on every sheet is equal to the sheet name.

Basically this formula will be copied on the main tab in rows B7 through B58.

It should look in the adjacent cell in Column A, find the value, then search
through Cell E1 of all sheets in the workbook looking for the sheet with a
matching value.

All E1's will be unique.

When it find the sheet with with the matching value, it should return the
value of AC1 as the result.

 

[Guest]

No the value of E1 on every sheet is equal to the sheet name.

Basically this formula will be copied on the main tab in rows B7 through B58.

It should look in the adjacent cell in Column A, find the value, then search
through Cell E1 of all sheets in the workbook looking for the sheet with a
matching value.

All E1's will be unique.

When it find the sheet with with the matching value, it should return the
value of AC1 as the result.

 

[Peo Sjoblom]

Then Biff's solutions should work


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Peo Sjoblom]

Then Biff's solutions should work


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)

 

[Guest]

Would SUMIF work better for this since it is just bringing the value of one
cell?

 

[Guest]

Would SUMIF work better for this since it is just bringing the value of one
cell?

 

[Biff]

You still need the list of sheet names:

=SUMPRODUCT(SUMIF(INDIRECT("'"&H$1:H$4&"'!E1"),A1,INDIRECT("'"&H$1:H$4&"'!AC1")))

Biff

 

[Biff]

You still need the list of sheet names:

=SUMPRODUCT(SUMIF(INDIRECT("'"&H$1:H$4&"'!E1"),A1,INDIRECT("'"&H$1:H$4&"'!AC1")))

Biff

 

[Guest]

How do i remove the #N/A! where the sheet has not been populated with data yet?

 

[Guest]

How do i remove the #N/A! where the sheet has not been populated with data yet?

 

[Biff]

Use the Sumproduct formula (it is better now that we have more info to go
on). It'll just return 0.

Biff

 

[Biff]

Use the Sumproduct formula (it is better now that we have more info to go
on). It'll just return 0.

Biff