Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
I need the correct formula for the following problem I am experiencing.
If the reference number on sheet 1 column B matches the reference number in column J on sheet 2 then transfer the data on sheet 2 column V. Can anyone help please? Kind regards Megan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
=IF(Sheet1!B1=Sheet2!J1,Sheet2!V1,"")
and copy to any desired row. -- Gary''s Student - gsnu200777 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
try this:
=VLOOKUP(Sheet1!B:B,Sheet2!J:V,13,0) "Megan" wrote: I need the correct formula for the following problem I am experiencing. If the reference number on sheet 1 column B matches the reference number in column J on sheet 2 then transfer the data on sheet 2 column V. Can anyone help please? Kind regards Megan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
I have tried this but it has brought back FALSE?
Any ideas? -- Kind regards Megan "Gary''s Student" wrote: =IF(Sheet1!B1=Sheet2!J1,Sheet2!V1,"") and copy to any desired row. -- Gary''s Student - gsnu200777 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
I have tried this but it brings back NA instead of the info.
Any ideas? -- Kind regards Megan "Binyaaust" wrote: try this: =VLOOKUP(Sheet1!B:B,Sheet2!J:V,13,0) "Megan" wrote: I need the correct formula for the following problem I am experiencing. If the reference number on sheet 1 column B matches the reference number in column J on sheet 2 then transfer the data on sheet 2 column V. Can anyone help please? Kind regards Megan |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
Megan ...
Try ... =vlookup(WS2Range$J$1:$V$10,WS1$B2,13,0) Adjust Range as nec & Copy down ... Kha "Megan" wrote: I have tried this but it brings back NA instead of the info. Any ideas? -- Kind regards Megan "Binyaaust" wrote: try this: =VLOOKUP(Sheet1!B:B,Sheet2!J:V,13,0) "Megan" wrote: I need the correct formula for the following problem I am experiencing. If the reference number on sheet 1 column B matches the reference number in column J on sheet 2 then transfer the data on sheet 2 column V. Can anyone help please? Kind regards Megan |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
Megan ... Forget my other post ... I reversed the values:
Assuming you have headings in Row 1 Try ... =vlookup(WS1$B2,WS2$J$2:$V$10,13,0) Adjust Range J2:V10 as necessary ... Then copy down ... Kha "Megan" wrote: I have tried this but it brings back NA instead of the info. Any ideas? -- Kind regards Megan "Binyaaust" wrote: try this: =VLOOKUP(Sheet1!B:B,Sheet2!J:V,13,0) "Megan" wrote: I need the correct formula for the following problem I am experiencing. If the reference number on sheet 1 column B matches the reference number in column J on sheet 2 then transfer the data on sheet 2 column V. Can anyone help please? Kind regards Megan |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
It still isn't working and is bring back NA - can you look at my formula and
tell me if you spot a problem as I can't see one? I thought that to do a VLOOKUP both worksheets should be laid out exactly the same and be sorted alphabetically? My worksheets are very different and I am unable to sort the data as it is very sensitive. I have used =VLOOKUP('CASH RECVD'!$B6,'Contracts Aug06-Date'!$A$7:$AK$479,13,0) Also does the number 13 represent the column no which contains the data I am trying to transfer? And what does the 0 represent on the end? Kind regards Megan "Ken" wrote: Megan ... Forget my other post ... I reversed the values: Assuming you have headings in Row 1 Try ... =vlookup(WS1$B2,WS2$J$2:$V$10,13,0) Adjust Range J2:V10 as necessary ... Then copy down ... Kha "Megan" wrote: I have tried this but it brings back NA instead of the info. Any ideas? -- Kind regards Megan "Binyaaust" wrote: try this: =VLOOKUP(Sheet1!B:B,Sheet2!J:V,13,0) "Megan" wrote: I need the correct formula for the following problem I am experiencing. If the reference number on sheet 1 column B matches the reference number in column J on sheet 2 then transfer the data on sheet 2 column V. Can anyone help please? Kind regards Megan |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
Megan, To use Vlookup, you require both sheets to have a common data e.g Sheet 1 Column A Column U Dog 12 Sheet 2 Column C Column L Dog bravado To Vlookup "Dog" is common to both sheets. This condition must be satisfied. Secondly, selection of Rows & Columns on the sheet from which extraction is to be made e.g sheet 2 must begin with Column C. Any selection before or after C will fail vlookup. Thirdly count from that selected "first cell" to the cell data resides i.e C -L=10. Your formula should look somewhat (vlookup(A1,sheet2!C1:L1,10,false) wish you luck. Regards, Abiodun "Megan" wrote: I need the correct formula for the following problem I am experiencing. If the reference number on sheet 1 column B matches the reference number in column J on sheet 2 then transfer the data on sheet 2 column V. Can anyone help please? Kind regards Megan |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
FORMULA ASSISTANCE REQUIRED
Hi Megan ...
In your formula ... if your Range is A7:Ak479 (Cash Recvd) ... Cell B6 ... is what you are looking for (Contracts Aug 06-Date) ... Range A7:AK479 is where you are looking for it 13 ... is 13th Col to Right in Range A7:AK479 (A=1, M=13) that you want returned. 0 ... = False. So if (Contracts Aug 06-Date) Col A contains the Value from B6 (Cash Recvd) you would be returning value from Col M (Contracts Aug 06-Date) Note: If value in Cell B6 repeats in your Search Range ... the 1st value found in Col 13 will always be returned. Above said ... from one of your previous Post: WS1 ... Col B ... Contains what I am looking for WS2 ... Col J ... is where this value is located WS2 ... Col V ... is value I want returned when value in Col B is found in Col J in an empty cell enter ... =vlookup(WS1CellB?,WS2RangeJ?:V?,13,0) If my search Range is Cols J?:V? ... then Col V is the 13th Col in the Range This is how you can look for value in Cell B? in Col J & return value found in Col V Substitute ... WS Names, Cells & Ranges as necessary. Kha "Megan" wrote: It still isn't working and is bring back NA - can you look at my formula and tell me if you spot a problem as I can't see one? I thought that to do a VLOOKUP both worksheets should be laid out exactly the same and be sorted alphabetically? My worksheets are very different and I am unable to sort the data as it is very sensitive. I have used =VLOOKUP('CASH RECVD'!$B6,'Contracts Aug06-Date'!$A$7:$AK$479,13,0) Also does the number 13 represent the column no which contains the data I am trying to transfer? And what does the 0 represent on the end? Kind regards Megan "Ken" wrote: Megan ... Forget my other post ... I reversed the values: Assuming you have headings in Row 1 Try ... =vlookup(WS1$B2,WS2$J$2:$V$10,13,0) Adjust Range J2:V10 as necessary ... Then copy down ... Kha "Megan" wrote: I have tried this but it brings back NA instead of the info. Any ideas? -- Kind regards Megan "Binyaaust" wrote: try this: =VLOOKUP(Sheet1!B:B,Sheet2!J:V,13,0) "Megan" wrote: I need the correct formula for the following problem I am experiencing. If the reference number on sheet 1 column B matches the reference number in column J on sheet 2 then transfer the data on sheet 2 column V. Can anyone help please? Kind regards Megan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - further assistance required | Excel Worksheet Functions | |||
Assistance required | Excel Discussion (Misc queries) | |||
Assistance with Formula | Excel Worksheet Functions | |||
Formula Assistance Required | Excel Worksheet Functions | |||
Formula assistance required!!! | Excel Discussion (Misc queries) |