Hi there Excel masters!
Ok, here goes. Please excuse me, I'm fairly new to this.
I'm trying to create a formula for work that will look up information on an external spreadsheet based on conditions. I want to be able so search a date in one cell, then a department in another cell then display (in the formulated 3rd cell) the result based on the 5th cell down from the one it found. I'll try and explain it a bit easier...
The spreadsheet ROW I'm editing looks like this: [date] [department] [result]. The target information ROW looks like [25/07/12] [credit] [sales] [service] [internet] - these cells have a numeric value 5 cells down that I want. So, if I searched for '25/07/12' in the date cell and 'sales' in the department cell then I want the result to be the value of the celll '5 cells down' from the sales column in the external spreadsheet.
Make sense? So far I I've tried HLOOKUP to find the specific cell which has been successful - =HLOOKUP(E25, '[Quarter 1 FY12.xls]2012 02 25'!$1:$6, 1, FALSE). But then I need to somehow nest perhaps OFFSET in the formula and incorporate the 'department' condition... I think.
The external workbook is called Quarter 1 FY12.xls and contains 13 'Sheets' (Hence 'Quarter 1' - it displays the the first 13 weeks in the year) and each sheet has 7 'dates' with the same format as mentioned above.
I hope someone can help!
P.S. I know little to nothing about VB, so for me is not an alternative - unless someone writes the code for me :P
Ok, here goes. Please excuse me, I'm fairly new to this.
I'm trying to create a formula for work that will look up information on an external spreadsheet based on conditions. I want to be able so search a date in one cell, then a department in another cell then display (in the formulated 3rd cell) the result based on the 5th cell down from the one it found. I'll try and explain it a bit easier...
The spreadsheet ROW I'm editing looks like this: [date] [department] [result]. The target information ROW looks like [25/07/12] [credit] [sales] [service] [internet] - these cells have a numeric value 5 cells down that I want. So, if I searched for '25/07/12' in the date cell and 'sales' in the department cell then I want the result to be the value of the celll '5 cells down' from the sales column in the external spreadsheet.
Make sense? So far I I've tried HLOOKUP to find the specific cell which has been successful - =HLOOKUP(E25, '[Quarter 1 FY12.xls]2012 02 25'!$1:$6, 1, FALSE). But then I need to somehow nest perhaps OFFSET in the formula and incorporate the 'department' condition... I think.
The external workbook is called Quarter 1 FY12.xls and contains 13 'Sheets' (Hence 'Quarter 1' - it displays the the first 13 weeks in the year) and each sheet has 7 'dates' with the same format as mentioned above.
I hope someone can help!
P.S. I know little to nothing about VB, so for me is not an alternative - unless someone writes the code for me :P