Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If Formula Help please?
How would I type in the formula to select the values of Column F in one
worksheet and link them to a different worksheet Column G, and place them in order by the date on my second sheet. Example: Sheet 1, Column F = Cash deposit totals for the day. Sheet 2, Column G = Running balance of my check register. Would this be done by using the date which is in Column A on both sheets. If so, what is this bad boy formula!! Thanks in Advance! Mark |
#2
|
|||
|
|||
Hi Mark
VLOOKUP :) Personally, i would do it on a different sheet ... so copy column A and column F to sheet 3 (these now become A & B). click in one cell and sort ascending by date now click in cell C2 (assuming you have headers in row 1) and type =VLOOKUP(A2,Sheet2!$A$1:$G$1000,7,0) this will lookup the date in sheet 2 and return the information in column G where there is an exact match ... fill this down your list of dates (double click on fill handle +) now this gets you to the current date, however, i'm guessing you'll want to do this for future dates as well ... so go to the bottom of column A and fill down the dates for another month or so then at the bottom of column B type the formula =VLOOKUP(A#,Sheet1!$A$1:$F$1000,6,0) where # is the current row number and fill down (using the double click method) then fill down the formulas in column C as well now it's set up for the future. Hope this helps Cheers JulieD "Mark" wrote in message ... How would I type in the formula to select the values of Column F in one worksheet and link them to a different worksheet Column G, and place them in order by the date on my second sheet. Example: Sheet 1, Column F = Cash deposit totals for the day. Sheet 2, Column G = Running balance of my check register. Would this be done by using the date which is in Column A on both sheets. If so, what is this bad boy formula!! Thanks in Advance! Mark |
#3
|
|||
|
|||
Thank you Julie,
I made a mistake the two different sheets are in two different workbooks. Example: My documents: Folder called Accounting This folder has 6 different folder names These folders have the two different worksheets to transfer the data.. The information will be linked from workbook to another workbook?? Please reply.. Thanks again for your time!! "JulieD" wrote: Hi Mark VLOOKUP :) Personally, i would do it on a different sheet ... so copy column A and column F to sheet 3 (these now become A & B). click in one cell and sort ascending by date now click in cell C2 (assuming you have headers in row 1) and type =VLOOKUP(A2,Sheet2!$A$1:$G$1000,7,0) this will lookup the date in sheet 2 and return the information in column G where there is an exact match ... fill this down your list of dates (double click on fill handle +) now this gets you to the current date, however, i'm guessing you'll want to do this for future dates as well ... so go to the bottom of column A and fill down the dates for another month or so then at the bottom of column B type the formula =VLOOKUP(A#,Sheet1!$A$1:$F$1000,6,0) where # is the current row number and fill down (using the double click method) then fill down the formulas in column C as well now it's set up for the future. Hope this helps Cheers JulieD "Mark" wrote in message ... How would I type in the formula to select the values of Column F in one worksheet and link them to a different worksheet Column G, and place them in order by the date on my second sheet. Example: Sheet 1, Column F = Cash deposit totals for the day. Sheet 2, Column G = Running balance of my check register. Would this be done by using the date which is in Column A on both sheets. If so, what is this bad boy formula!! Thanks in Advance! Mark |
#4
|
|||
|
|||
Hi Mark
makes no difference really, you can do a VLOOKUP across workbooks .. however, in the VLOOUP formula you have to include path to the workbook and the workbook name e.g. =VLOOKUP(A1,'C:\temp\Excel\My Excel Files\[fleet details.xls]fleet list'!$C2:$D200,2,0) the easiest way to do this is when you're creating the VLOOKUP have both books open and in the second parameter click on the range in the source workbook - excel will fill in the full path & name for you) hope this helps Cheers JulieD "Mark" wrote in message ... Thank you Julie, I made a mistake the two different sheets are in two different workbooks. Example: My documents: Folder called Accounting This folder has 6 different folder names These folders have the two different worksheets to transfer the data.. The information will be linked from workbook to another workbook?? Please reply.. Thanks again for your time!! "JulieD" wrote: Hi Mark VLOOKUP :) Personally, i would do it on a different sheet ... so copy column A and column F to sheet 3 (these now become A & B). click in one cell and sort ascending by date now click in cell C2 (assuming you have headers in row 1) and type =VLOOKUP(A2,Sheet2!$A$1:$G$1000,7,0) this will lookup the date in sheet 2 and return the information in column G where there is an exact match ... fill this down your list of dates (double click on fill handle +) now this gets you to the current date, however, i'm guessing you'll want to do this for future dates as well ... so go to the bottom of column A and fill down the dates for another month or so then at the bottom of column B type the formula =VLOOKUP(A#,Sheet1!$A$1:$F$1000,6,0) where # is the current row number and fill down (using the double click method) then fill down the formulas in column C as well now it's set up for the future. Hope this helps Cheers JulieD "Mark" wrote in message ... How would I type in the formula to select the values of Column F in one worksheet and link them to a different worksheet Column G, and place them in order by the date on my second sheet. Example: Sheet 1, Column F = Cash deposit totals for the day. Sheet 2, Column G = Running balance of my check register. Would this be done by using the date which is in Column A on both sheets. If so, what is this bad boy formula!! Thanks in Advance! Mark |
#5
|
|||
|
|||
Thank you Julie,
The formula works great. Mark "JulieD" wrote: Hi Mark makes no difference really, you can do a VLOOKUP across workbooks .. however, in the VLOOUP formula you have to include path to the workbook and the workbook name e.g. =VLOOKUP(A1,'C:\temp\Excel\My Excel Files\[fleet details.xls]fleet list'!$C2:$D200,2,0) the easiest way to do this is when you're creating the VLOOKUP have both books open and in the second parameter click on the range in the source workbook - excel will fill in the full path & name for you) hope this helps Cheers JulieD "Mark" wrote in message ... Thank you Julie, I made a mistake the two different sheets are in two different workbooks. Example: My documents: Folder called Accounting This folder has 6 different folder names These folders have the two different worksheets to transfer the data.. The information will be linked from workbook to another workbook?? Please reply.. Thanks again for your time!! "JulieD" wrote: Hi Mark VLOOKUP :) Personally, i would do it on a different sheet ... so copy column A and column F to sheet 3 (these now become A & B). click in one cell and sort ascending by date now click in cell C2 (assuming you have headers in row 1) and type =VLOOKUP(A2,Sheet2!$A$1:$G$1000,7,0) this will lookup the date in sheet 2 and return the information in column G where there is an exact match ... fill this down your list of dates (double click on fill handle +) now this gets you to the current date, however, i'm guessing you'll want to do this for future dates as well ... so go to the bottom of column A and fill down the dates for another month or so then at the bottom of column B type the formula =VLOOKUP(A#,Sheet1!$A$1:$F$1000,6,0) where # is the current row number and fill down (using the double click method) then fill down the formulas in column C as well now it's set up for the future. Hope this helps Cheers JulieD "Mark" wrote in message ... How would I type in the formula to select the values of Column F in one worksheet and link them to a different worksheet Column G, and place them in order by the date on my second sheet. Example: Sheet 1, Column F = Cash deposit totals for the day. Sheet 2, Column G = Running balance of my check register. Would this be done by using the date which is in Column A on both sheets. If so, what is this bad boy formula!! Thanks in Advance! Mark |
#6
|
|||
|
|||
Hi Mark
you're welcome & thanks for the feedback Cheers JulieD "Mark" wrote in message ... Thank you Julie, The formula works great. Mark "JulieD" wrote: Hi Mark makes no difference really, you can do a VLOOKUP across workbooks .. however, in the VLOOUP formula you have to include path to the workbook and the workbook name e.g. =VLOOKUP(A1,'C:\temp\Excel\My Excel Files\[fleet details.xls]fleet list'!$C2:$D200,2,0) the easiest way to do this is when you're creating the VLOOKUP have both books open and in the second parameter click on the range in the source workbook - excel will fill in the full path & name for you) hope this helps Cheers JulieD "Mark" wrote in message ... Thank you Julie, I made a mistake the two different sheets are in two different workbooks. Example: My documents: Folder called Accounting This folder has 6 different folder names These folders have the two different worksheets to transfer the data.. The information will be linked from workbook to another workbook?? Please reply.. Thanks again for your time!! "JulieD" wrote: Hi Mark VLOOKUP :) Personally, i would do it on a different sheet ... so copy column A and column F to sheet 3 (these now become A & B). click in one cell and sort ascending by date now click in cell C2 (assuming you have headers in row 1) and type =VLOOKUP(A2,Sheet2!$A$1:$G$1000,7,0) this will lookup the date in sheet 2 and return the information in column G where there is an exact match ... fill this down your list of dates (double click on fill handle +) now this gets you to the current date, however, i'm guessing you'll want to do this for future dates as well ... so go to the bottom of column A and fill down the dates for another month or so then at the bottom of column B type the formula =VLOOKUP(A#,Sheet1!$A$1:$F$1000,6,0) where # is the current row number and fill down (using the double click method) then fill down the formulas in column C as well now it's set up for the future. Hope this helps Cheers JulieD "Mark" wrote in message ... How would I type in the formula to select the values of Column F in one worksheet and link them to a different worksheet Column G, and place them in order by the date on my second sheet. Example: Sheet 1, Column F = Cash deposit totals for the day. Sheet 2, Column G = Running balance of my check register. Would this be done by using the date which is in Column A on both sheets. If so, what is this bad boy formula!! Thanks in Advance! Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions | |||
Excel 2003 - Formula result shows as 0:00 | Excel Worksheet Functions |