Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.

Get a Professional Answer

Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

100% Satisfaction Guarantee

Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy, Computer Manager

Category: Microsoft Office

Satisfied Customers: 1936

Experience: 20 years experience providing remote computer support

62934938

Type Your Microsoft Office Question Here...

The-PC-Guy is online now

OK I have a new question I need answered before I can apply

This answer was rated:

★★★★★

OK I have a new question I need answered before I can apply your Macro - I can attach my workbooks once I see the paperclip option - basically what I need is for one workbook sheet to pull data cells from another workbook sheet and populate several cells. The problem is that I need to combine nested IF statements with a VLOOKUP statement. The IF statement must decide if the student is grade 9, 10, 11 or 12 and then use the student's ID to lookup data cells from the appropriate workbook associated with the student's grade level. So the 1st workbook name is ‘1314 GN Fitness Data Test’ and the worksheet within this workbook is named ‘Cumulative’ this workbook sheet must pull the data from a workbook named ‘GN_14_FD’ and the workshhet within this workbook is named ‘Cumulative’ and the data cells are in a named range called ‘CumData’ which is $A1:$BC700. The sample workbooks that I can provide are mentioned above – the data sheets are for students in grade 12 so the IF statements will only work for that grade level from what I am providing you with. I was thinking that the statement I am looking for will look something like: IFERROR(IF( $B4 = “9”, VLOOKUP($A4,[GN_14_FD]Cumulative!CumData,16,False), IF($B4 = “10”, VLOOKUP($A4,[GN_14_FD]Cumulative!CumData,16,False), ($B4 = “11”, VLOOKUP($A4,[GN_14_FD]Cumulative!CumData,16,False), ($B4 = “12”, VLOOKUP($A4,[GN_14_FD]Cumulative!CumData,16,False),””)) If you can help me with this I will leave another bonus for you.

I am sorry I never received an e-mail from you so I did not think you responded to my question yet - once I closed this window I did not see any response from you until I reopened this original question.

I will try and explain as best I can:

The formulas need to be in workbook '1314 GN Fitness Data Test' sheet 'Cumulative' and the 1st cell will be 'P4'. The formula needs to 1st check what grade the student is in (9,10,11,12) then use the VLOOKUP statement to access the proper Data Workbook based on that grade level and pull the data that is in the same corresponding sheet 'Cumulative' and cell 'P4'.

The only Data Workbook I sent you was for grade 12 therefore you may want to start with cell 'P11', however the 1st piece of data for that student is in cell 'T9'.

So basically the formula needs to do the following: from the 1st workbook (1314 GN Fitness Data Test) check (IF) to see what grade the student is in (9,10,11,12) then use the student ID to lookup his scores in the appropriate Data Workbook (GN_14_FD) within the same named sheet (Cumulative) and the same corresponding cell.

I then need to duplicate this formula for all columns P through BC.

Let me know if this helps. Hopefully you can get this done somewhat soon as I need this formula in order to proceed with my project.

Like I said I never received an e-mail like I have always in the past so I never checked to see that you responded to me.

Thanks for getting back to me - this time i received an e-mail so I knew you responded.

The main workbook that receives all the data will now be called:

"1314_GN_Fitness_Results"

The Data workbooks and their associated grades are as follows:

grade 12 named "GN_14_FD"

grade 11 named "GN_15_FD"

grade 10 named "GN_16_FD"

grade 9 named "GN_17_FD"

The main that I sent you did not have the underscore characters, I decided to use underscore characters so all the names are XXXXX XXXXX I hope this makes sense.

I have been very busy and have not tried your formula as of yet but I have time tomorrow to really test everything out and so I will get back to you tomorrow if not tonight.

Thanks for checking and yes I am now getting your e-mails.

OK the students for grade 12 are transferring perfectly but the other three grade levels are off by a few columns. I am sure you are aware that the main file "1314 GN Fitness Data Test" is trying to pull data from four different data workbooks using the exact same 40 columns (P through BC). Your formula is pulling the data from all four data workbooks correctly its just that grade levels 9,10, and 11 are not transferring to the appropriate columns in the main workbook.

I have attached a WORD document that I hope illustrates this. Please let me know what you think?

OK I have ALL 5 files currently opened in Excel - so what do we do now?

I am ready when you are.

I copied your formula with and I changed the file names to match my four data workbooks. Please let me know when you are ready as i have some errands to run and do not want to miss your message. I have about another 40 minutes before I have to leave.

I have until 9:45 Illinois Time.

Thanks

Steve

OK I have to leave for a while but I will let you know when I am back at my computer. It is now 9:30.

Yes, I know that and you should know by now that I will definitely give you a rating after I have tested everything out. Your latest work in terms of the formula now appears to be working - I had to tweak the grade 12 a little bit but it all seems good now.

I have a problem perhaps with your Macro that you gave me the last time - the Macro worked with a drop down list and adjusted every time you used a filter - the problem is every time I try to use a filter even on a different page Excel starts calculating and it takes a few minutes before it hits 100% and I have to wait until it is finished so I have to get that fixed. The exact message on the bottom right of the screen says: 'Calculating (2 processor(s)): __ %.

Even when I turn off automatic calculating which I do not want to do it does this every time I save the file.

What do you suggest?

I have attached the Macro that you sent me - I have changed the sheet names and cells to fit my exact worksheet.