PDA

View Full Version : Find date and return value



NatsJou
07-02-2014, 01:31 AM
I have to paste monthly figures from one Excel workbook to another workbook

I want to write a code that search for the departments name within the first workbook and return a value of a specific column (named availability) to the other workbook

For example I want to get Department A's availability figure from the first workbook and copy it into the second workbook.

The first workbook has only one sheet called summary that contains all the data for all the departments. The second workbook has a sheet for every department.

OG Loc
07-02-2014, 02:00 AM
I have to paste monthly figures from one Excel workbook to another workbook

I want to write a code that search for the departments name within the first workbook and return a value of a specific column (named availability) to the other workbook

For example I want to get Department A's availability figure from the first workbook and copy it into the second workbook.

The first workbook has only one sheet called summary that contains all the data for all the departments. The second workbook has a sheet for every department.

That can certainly be done, have you tried anything so far?

You are essentially wanting to do a VLOOKUP function, looking up something from the first book in the second. In fact if you just copied the sheet from the first book to the second you could use a bank of VLOOKUP formulae to achieve everything without having to do any code.

Are you familiar with VLOOKUP at all? If not I can recommend the tutorial video made my yours truly as a quick introduction!
http://www.youtube.com/watch?v=AlD03Arf5Oc&list=UUk-MVP61VQHMdfETVNQEFww

NatsJou
07-02-2014, 04:51 AM
Hi

I first started with VLOOKUP, but then it means I will have to write a VLOOKUP for every different department and there are over 100.

The VLOOKUP would look something like this:
VLOOKUP(May,B1:B100, 3, false)

This means it will look for the month of May in Column B (that is the table range) and then return the value next to may in the 3rd column and lastly the table is not sorted (false).

But because we use different months as well and we have a sheet for every department, I will have to put in a vlookup in every departments sheet.

Thought there may be an easier way to do everything at once using VBA but I am no good with it at all.

Bob Phillips
07-02-2014, 04:53 AM
Post your workbooks so we can see what you are looking at. Will both workbooks already be open?

NatsJou
07-02-2014, 05:19 AM
Both workbooks can be open yes, not necessary that the code opens them.

Attached are examples of the workbooks. Workbook 1 is where I need to get the data to paste into workbook 2. I highlighted the data in both workbooks so you can see what data must go where.

Workbook 2 is updated every month, so the month changes (but the code can be changed accordingly).

The sheets for the different departments in workbook also do not all have the same format... so sometimes data will be in say the 5th column and in other sheets it will be in the 3rd or another column.

Bob Phillips
07-02-2014, 05:56 AM
If you change the label in row 3 of the Summary sheet to the same as in column A of the Department sheets, you can use

=INDEX(INDIRECT("'[Workbook 2.xlsx]Department "&A4&"'!A:M"),MATCH(E$3,INDIRECT("'[Workbook 2.xlsx]Department "&A4&"'!A:A"),0),MATCH($B$1,INDIRECT("'[Workbook 2.xlsx]Department "&A4&"'!2:2"),0))