If statement and location of workbook

[greekboyuk]

Hi,

I am trying to use some if statements to bring data depending on some cells however I am struggling a bit. I think that maybe a vb code is required.

I have a report sheet where I would like to bring data from external workbooks depending the cells' value.

Short description:

Cell B2="Employee Name"
Cell B3= "Type of Analysis"
Cell B4= "Year"
Cell B5= "Month"

All the above cells will change and the outcome will be displayed below as explained in attached sample. Below where "CellB2Value" that represent value of the above cell in this instance "Employee Name".


Cell A8= "Value of B3" of which the below cells depending
Cell A9= If value of A8 = "Support", locate&bring_value from "'C:\TEST\TIMESHEETS\CellB4Value\CellB2Value\[CellB2Value_TIMESHEET_CellB4Value.xls]]CellB5Value'!$B$6. (Support should pick up all values from !$B6:!$B11 and bring the results in the cells A9:A14)

Cell A9= If value of A8 = "Control", locate&bring_value from "'C:\TEST\TIMESHEETS\CellB4Value\CellB2Value\[CellB2Value_TIMESHEET_CellB4Value.xls]]CellB5Value'!$B12 (Control should pick up values in order from !$B12:$B20 and bring the results in Report Sheet cells A9:A17)

Cell A9= If value of A8 = "Project", locate&bring_value from "'C:\TEST\TIMESHEETS\CellB4Value\CellB2Value\[CellB2Value_TIMESHEET_CellB4Value.xls]]CellB5Value'!$B12 (Control should pick up all values in order from !$B12:$B20 and bring the results in Report Sheet cells A9:A18)

Cell A9= If value of A8 = "Misc", locate&bring_value from "'C:\TEST\TIMESHEETS\CellB4Value\CellB2Value\[CellB2Value_TIMESHEET_CellB4Value.xls]]CellB5Value'!$B31 (Control should pick up all values in order from !$B29:$B31 and bring the results in Report Sheet cells A9:A11)

Cell A9= If value of A8 = "Absence", locate&bring_value from "'C:\TEST\TIMESHEETS\CellB4Value\CellB2Value\[CellB2Value_TIMESHEET_CellB4Value.xls]]CellB5Value'!$B31 (Control should pick up all values in order from !$B32:$B33 and bring the results in Report Sheet cells A9:A10)


There are 6 different If statements that should be used in Cells A9:A and the data will change once data from Report Workbook Cell B2,B3,B4,B5 changes. I have attached a copy sample of the report and one of the many users time-sheets that it should locate and bring up the data.

Many Thanks

greekboy_uk

[greekboyuk]

Can anyone help me please???

[delforum]

I don't think you need VB for this unless you want to automate report creation. If all you want to do is select the users and month from the pull down and print the Reports page you are looking at, you can use VLOOKUP, or even better INDEX(range to return,MATCH(value,range to look in,0),column number of the range you chose).

To make the variables included in the file names, you can use INDIRECT to turn strings into actual coded file names names. You may have to link the different workbooks together so they can access each other.

Here is an example of that INDIRECT
http://excel.tips.net/Pages/T002813_...alidation.html

They use =INDIRECT("[Book2]Sheet1!D6"), but the Book2 needs to be open.

[stnkynts]

If you want to use a macro here is a way to do it:

Please Login or Register  to view this content.