Home |
Search |
Today's Posts |
#1
|
|||
|
|||
2-Level (criteria) Lookup Function Help
I have a sheet I am trying to make work and need some help figuring out a
function that will do what I want. On the first tab of my worksheet I have an "Invoice" set up that is looking for data from other tabs. Each of the other tabs has the same sets of information in short columns (about 10 rows each)-- one column for each month of the year where I imput the info i'm keeping. Each tab has a unique group name. What I would like to have happen is when I enter the the tab or "group" name in one cell on the first tab and then in another cell the month I am looking for, it will post that month's data from the column it's in on that sheet to the "invoice" sheet. I get a little lost in some of these more advanced lookup functions and was hoping someone out there might have an answer for me. Thanks! -- Steven Leuck Builders Electric, Inc. |
#2
|
|||
|
|||
Bit guessing here but assuming that the data you want is in row 1, try
=INDIRECT("'"&A16&"'!"&CHAR(MONTH("01-"&B16)+64)&"1") -- HTH RP (remove nothere from the email address if mailing direct) "Steven Leuck" wrote in message ... I have a sheet I am trying to make work and need some help figuring out a function that will do what I want. On the first tab of my worksheet I have an "Invoice" set up that is looking for data from other tabs. Each of the other tabs has the same sets of information in short columns (about 10 rows each)-- one column for each month of the year where I imput the info i'm keeping. Each tab has a unique group name. What I would like to have happen is when I enter the the tab or "group" name in one cell on the first tab and then in another cell the month I am looking for, it will post that month's data from the column it's in on that sheet to the "invoice" sheet. I get a little lost in some of these more advanced lookup functions and was hoping someone out there might have an answer for me. Thanks! -- Steven Leuck Builders Electric, Inc. |
#3
|
|||
|
|||
Not quite... here's some more exact info:
Sheet 1 (called "invoice" on my tab sets) has two imput criteria cells: C1 asks for "invoice to:" which is the name of the group that corresponds to the tab name where their data is stored. C2 asks for the "month" we are invoicing for (expressed as date, example: 2/1/2005). On each of the subsequent tab sheets there is a column for each month's copier usage for the 10 cost centers they use. For example, February 2005 would be in cells D8:D17. March 2005 would be in cells E8:E17, April in F8:F17, etc. So, when I enter "Group1" into the "Invoice to:" cell on my "invoice" sheet, AND put "2/1/2005" in the "Month" cell, I am hoping to have the data in cells D8:D17 from the "Group1" tab be displayed in cells E17:E26 on my "invoice" sheet. Changing the date in my "month" criteria cell to "3/1/2005", I should be able to see the data from cells E8:E17 from the "Group1" sheet, etc. I hope this makes more sense and is not over convoluted. Thank you, Steven Leuck __________________________________________________ ________ "Bob Phillips" wrote: Bit guessing here but assuming that the data you want is in row 1, try =INDIRECT("'"&A16&"'!"&CHAR(MONTH("01-"&B16)+64)&"1") -- HTH RP (remove nothere from the email address if mailing direct) "Steven Leuck" wrote in message ... I have a sheet I am trying to make work and need some help figuring out a function that will do what I want. On the first tab of my worksheet I have an "Invoice" set up that is looking for data from other tabs. Each of the other tabs has the same sets of information in short columns (about 10 rows each)-- one column for each month of the year where I imput the info i'm keeping. Each tab has a unique group name. What I would like to have happen is when I enter the the tab or "group" name in one cell on the first tab and then in another cell the month I am looking for, it will post that month's data from the column it's in on that sheet to the "invoice" sheet. I get a little lost in some of these more advanced lookup functions and was hoping someone out there might have an answer for me. Thanks! -- Steven Leuck Builders Electric, Inc. |
#4
|
|||
|
|||
In E17
=INDIRECT("'"&$C$1&"'!"&CHAR(MONTH($C$2)+64+2)&ROW ()) and copy down -- HTH RP (remove nothere from the email address if mailing direct) "Steven Leuck" wrote in message ... Not quite... here's some more exact info: Sheet 1 (called "invoice" on my tab sets) has two imput criteria cells: C1 asks for "invoice to:" which is the name of the group that corresponds to the tab name where their data is stored. C2 asks for the "month" we are invoicing for (expressed as date, example: 2/1/2005). On each of the subsequent tab sheets there is a column for each month's copier usage for the 10 cost centers they use. For example, February 2005 would be in cells D8:D17. March 2005 would be in cells E8:E17, April in F8:F17, etc. So, when I enter "Group1" into the "Invoice to:" cell on my "invoice" sheet, AND put "2/1/2005" in the "Month" cell, I am hoping to have the data in cells D8:D17 from the "Group1" tab be displayed in cells E17:E26 on my "invoice" sheet. Changing the date in my "month" criteria cell to "3/1/2005", I should be able to see the data from cells E8:E17 from the "Group1" sheet, etc. I hope this makes more sense and is not over convoluted. Thank you, Steven Leuck __________________________________________________ ________ "Bob Phillips" wrote: Bit guessing here but assuming that the data you want is in row 1, try =INDIRECT("'"&A16&"'!"&CHAR(MONTH("01-"&B16)+64)&"1") -- HTH RP (remove nothere from the email address if mailing direct) "Steven Leuck" wrote in message ... I have a sheet I am trying to make work and need some help figuring out a function that will do what I want. On the first tab of my worksheet I have an "Invoice" set up that is looking for data from other tabs. Each of the other tabs has the same sets of information in short columns (about 10 rows each)-- one column for each month of the year where I imput the info i'm keeping. Each tab has a unique group name. What I would like to have happen is when I enter the the tab or "group" name in one cell on the first tab and then in another cell the month I am looking for, it will post that month's data from the column it's in on that sheet to the "invoice" sheet. I get a little lost in some of these more advanced lookup functions and was hoping someone out there might have an answer for me. Thanks! -- Steven Leuck Builders Electric, Inc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Function Problem | Excel Discussion (Misc queries) | |||
If function using a list of criteria | Excel Discussion (Misc queries) | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
Function that gives Group level? | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |