If Formula Help please?

[Guest]

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

 

[JulieD]

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

 

[Guest]

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]

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

 

[Guest]

Thank you Julie,
The formula works great.
Mark

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

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]

Hi Mark

you're welcome & thanks for the feedback

Cheers
JulieD

Thank you Julie,
The formula works great.
Mark

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

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!!


:

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

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