Excel Formula Help Please!!!

  • Thread starter Thread starter Mohamed
  • Start date Start date
M

Mohamed

Hi,

I have a problem, I can not figure out how to create a
formula for a certain worksheet. This is what I want to
do. On Worksheet1 I have a table in which I track my data
totals by date. On worksheet2 I have my daily tracking
list. What I want, is for Worksheet1 to automatically
feed off Worksheet2 for that corresponding date. For
example, if the computer knows today is 1/16/04, I want
it to enter my data from worksheet2 into the
corresponding columns in worksheet1. That way I do not
have to manually input the data from one worksheet to
another every day. Please assist, greatly appreaciated.

Thanks
 
Mohamed,

VLOOKUP is what you want

=VLOOKUP(A1, Sheet2!A1:F100,2,FALSE)

will get thhe value in column B corresponding to A1 on sheet1 matching in
column A of sheet2. Change the ,2 to get column C, D and so on.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Mohamed said:
Hi,

I have a problem, I can not figure out how to create a
formula for a certain worksheet. This is what I want to
do. On Worksheet1 I have a table in which I track my data
totals by date. On worksheet2 I have my daily tracking
list. What I want, is for Worksheet1 to automatically
feed off Worksheet2 for that corresponding date. For
example, if the computer knows today is 1/16/04, I want
it to enter my data from worksheet2 into the
corresponding columns in worksheet1. That way I do not
have to manually input the data from one worksheet to
another every day. Please assist, greatly appreaciated.

Thanks
Click to expand...

You cannot do that with a worksheet formula. You would need a macro.
The reason is that you need to trigger the data movement from an event, not
a state.
Try posting in the Excel programming newsgroup.
 
Mohamed
You can use VLOOKUP if you are pulling over one item, or you can use SUMIF if you need to sum up numerous entries. If you descibe your tables with cell references and/or ranges I could put together some formulas for you

Good Luck
Mark Graesse
(e-mail address removed)

----- Mohamed wrote: ----

Hi

I have a problem, I can not figure out how to create a
formula for a certain worksheet. This is what I want to
do. On Worksheet1 I have a table in which I track my data
totals by date. On worksheet2 I have my daily tracking
list. What I want, is for Worksheet1 to automatically
feed off Worksheet2 for that corresponding date. For
example, if the computer knows today is 1/16/04, I want
it to enter my data from worksheet2 into the
corresponding columns in worksheet1. That way I do not
have to manually input the data from one worksheet to
another every day. Please assist, greatly appreaciated.

Thanks
 
One solution would be to create named ranges for each line
item on worksheet2. Then on worksheet1 use a SUMIF, i.e.
=SUMIF(Date2,B2,Row1). Where Date2 is your named range on
worksheet2 for the row containing your dates, B2 is the
cell on worksheet1 where the date you want populated
exists and Row1 is the ramed range for the row containing
the data you want populated from worksheet2 to worksheet1.
Simply change the name (Row1 to Row2) in the formula for
the next line item.
To create named ranges in worksheet2 go to Insert, Name,
Define. Key the name you would like to use, select the
cell reference and click add. Repeat for each range.
 
-----Original Message-----


You cannot do that with a worksheet formula. You would need a macro.
The reason is that you need to trigger the data movement from an event, not
a state.
Try posting in the Excel programming newsgroup.


.
Click to expand...

Paul, never say never. There are many ways to accomplish
Mohamed's task with a formula or formulas.
 
I understand the formula, thanks so very much for the
help, however one slight problem still. The formula needs
to be date sensitive, that is I do not want the numbers
to be entered in the entire column. In theory I want it
matched for that particular date. Just to clarify, for
example, In column A I have the dates Jan1-Jan30 this is
on sheet 1, which does my month to date totals. Now in
column B I need the information inputed from sheet 2 to
match it only for that specific date. If today was 1/15
it would input the data in that row. If it was 1/27 it
would input the data in that row and so on. I tried a
match function and tried nesting the today function
within, but I can not seem to get it. Thanks again, hope
this clarifies the matter. Please reply! Thanks.
 
This is how I (Paul) understood your requirement, which is why I replied
that it can only be done with a macro and not worksheet formulas. Others, I
think, interpreted your post differently and gave replies such as that
below, which do not fit your requirement as I understand it.
 
Mohamed
I still think you can use VLOOKUP or SUMPRODUCT for this. As long as you are not caculating off of the table you are creating, you could use a conditional format to only show cells for the current and previous dates

If you would like to send me the file, or a sample, I can give it a look on Monday

Regards
Mark Graesse
(e-mail address removed)

----- Mohamed wrote: ----

I understand the formula, thanks so very much for the
help, however one slight problem still. The formula needs
to be date sensitive, that is I do not want the numbers
to be entered in the entire column. In theory I want it
matched for that particular date. Just to clarify, for
example, In column A I have the dates Jan1-Jan30 this is
on sheet 1, which does my month to date totals. Now in
column B I need the information inputed from sheet 2 to
match it only for that specific date. If today was 1/15
it would input the data in that row. If it was 1/27 it
would input the data in that row and so on. I tried a
match function and tried nesting the today function
within, but I can not seem to get it. Thanks again, hope
this clarifies the matter. Please reply! Thanks
-----Original Message----
Mohamed
VLOOKUP is what you wan
=VLOOKUP(A1, Sheet2!A1:F100,2,FALSE
will get thhe value in column B corresponding to A1 on
Click to expand...
sheet1 matching i
column A of sheet2. Change the ,2 to get column C, D and so on... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct
Click to expand...
 
Back
Top