Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
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) "Jaisenm" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
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(IND IRECT("'"&H$1:H$4&"'!E4"),A1)0,0))&"'!AC1") Biff "Jaisenm" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
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" wrote in message ... 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(IND IRECT("'"&H$1:H$4&"'!E4"),A1)0,0))&"'!AC1") Biff "Jaisenm" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
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" wrote: 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) "Jaisenm" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
Then Biff's solutions should work
-- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Jaisenm" wrote in message ... 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" wrote: 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) "Jaisenm" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
Would SUMIF work better for this since it is just bringing the value of one
cell? "Jaisenm" wrote: 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" wrote: 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) "Jaisenm" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
You still need the list of sheet names:
=SUMPRODUCT(SUMIF(INDIRECT("'"&H$1:H$4&"'!E1"),A1, INDIRECT("'"&H$1:H$4&"'!AC1"))) Biff "Jaisenm" wrote in message ... Would SUMIF work better for this since it is just bringing the value of one cell? "Jaisenm" wrote: 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" wrote: 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) "Jaisenm" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
How do i remove the #N/A! where the sheet has not been populated with data yet?
"Peo Sjoblom" wrote: Then Biff's solutions should work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Jaisenm" wrote in message ... 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" wrote: 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) "Jaisenm" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP over multiple sheets
Use the Sumproduct formula (it is better now that we have more info to go
on). It'll just return 0. Biff "Jaisenm" wrote in message ... How do i remove the #N/A! where the sheet has not been populated with data yet? "Peo Sjoblom" wrote: Then Biff's solutions should work -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "Jaisenm" wrote in message ... 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" wrote: 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) "Jaisenm" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VLOOKUP with abitlity to choose from multiple defined names. | Excel Worksheet Functions | |||
selecting multiple sheets | Excel Worksheet Functions | |||
Printing Multiple sheets | Excel Discussion (Misc queries) | |||
How do i auto create multiple files from 1 with multiple sheets | Excel Worksheet Functions | |||
linking multiple sheets to a summary sheet | Excel Discussion (Misc queries) |