HELP ME WITH EXCEL FORMULA FOR MOVING DATA??

Question

I need to know what the VLOOKUP FORMULA is for moving numbers between these 2 sheets because I can not figure out the formula myself. I have tried all day, and I can not get it.

JUST LIKE IT SHOWS IN THE SCREENSHOT ABOVE. I have 2 sheets named "Testing Day 1" and "Month 1 Week 1". I want to pull the number in cell Q12 from "Testing Day 1" and then drop that number into the cell B97 in "Month 1 Week 1".

I know I need to enter a VLOOKUP FORMULA (or another formula that does the same thing) into the cell B97 in Month 1 Week 1. But I don't know exactly how to enter all the variables in the formula to make it work. I do know the formula needs to look something like this though.

=VLOOKUP(Q12, Testing Day 1!$B$97, 14, FALSE)

I want to do the exact same thing in the screenshot below too. Pull the number from cell Q12 in Testing Day 1 and drop that number into cell C11 in Master File using a VLOOKUP FORMULA again (or another formula that does the same thing).

JUST PLEASE TELL ME HOW TO ENTER THIS FORMULA PROPERLY?? IT IS DRIVING ME COMPLETELY INSANE??

Answer 1

I am totally confused. The following formula that you say works is simply looking itself up on the same worksheet.

=VLOOKUP('TEST DAY 1'! A12,'TEST DAY 1'!A12:N14,13,FALSE)

It is not looking up anything that matches one worksheet to the other worksheet. You could have entered  the following formula in B97 because that is all the Vlookup formula is doing.

='TEST DAY 1'! M12

In the worksheet that you uploaded, the row labels appear to be different to the row labels in your original screen shot.

It appears to me because of the different layouts of the worksheets, all you really need to do where you require a value from another sheet is to type an = sign in the destination cell and then change worksheets to the one containing the required value and click on the source cell and then press Enter.

PS. This has been added with editing my post:

Some of my confusion is due to the following.

In your Screen shots in your last post you have B97 in "Test Day 1" and M12 in "Month 1 Week 1".

In your example workbook B97 is in Month 1 (Week 1) and M12 in "Test Day 1".

I still stand by my comment that you can populate Month 1 (Week 1) B97 with the formula ='TEST DAY 1'!M12

Answer 2

Hello,

I think you are looking at the wrong sheet. Because it is not looking itself up on the same sheet.

All of the formulas in the sheet MONTH 1 (WEEK 1) are looking up data in cells on the sheet TEST DAY 1. The formula in cell B97 on the MONTH 1 (WEEK 1) sheet is looking up the data in cell Q12 on the sheet TEST DAY 1. Also, the cell M97 on the sheet MONTH 1 (WEEK 1) is looking up the data in cell Q26 on the sheet TEST DAY 1.

They come from 2 different sheets. Not the same one.

That Microsoft Rep who sorted it out also said you can do the exact same thing by using a much simpler formula in the form of:

FOR CELL B97 ENTER THIS:  ='TEST DAY 1'!Q12

And do the same thing with other cells. So cell M97 on the sheet MONTH 1 (WEEK1) is getting its data from cell Q26 on the sheet TEST DAY 1. Therefore, you would enter this:

FOR CELL M97 ENTER THIS:  ='TEST DAY 1'!Q26

Answer 3

I am not looking at the wrong sheet. The formula that you are using is not matching anything between the Source location and the Destination location and the Vlookup part of your formula is applied to the same sheet.

Explanation of and Example of the Vlookup Function.

Firstly the example uses 2 locations, a Source and Destination. In more complex cases you can have a situation where there is actually 3 locations where it matches something from one location to a second location and then returns a value to a third location but we won't go into that now.

The Vlookup function is used to find something in the source location that matches something in the destination location. It then returns a value from the source and inserts it in the destination. (The formula is entered at the destination location).

It then returns a value from a column that is a specific number of columns to the right of the found matching value.

The formula you posted looks something up on TEST DAY 1 with the value to find referenced on the same sheet.The value to find should be on the destination sheet (the sheet with the formula).The following formula simply finds the value in cell A12 on TEST DAY 1 and returns the value from the 13th column to the right of column A (Counting from Column A). It is not looking for a matching value to a cell on the destination sheet.

=VLOOKUP('TEST DAY 1'! A12,'TEST DAY 1'!A12:N14,13,FALSE)

It is the same as entering =TEST DAY 1'! A12

I think that you can probably use a formula like the above for most of your data because I don't really see the necessity to be looking up the matching values to find the required data. Just enter an = sign in the destinaltion cell and then navigate to the source cell and click on it and then press Enter and it is finished.

The following Screen Shots are not related to your data but they represent an example only of the Vlookup Function to return a value after a lookup to find a matching value/string.

Answer 4

Look I don't care how it works, as long as it works. As long as it works I really couldn't care less. I don't know how his formula works but it works. So the problem is really taken care of now.