Linking formula to Date

  • Thread starter Thread starter Jackie
  • Start date Start date
J

Jackie

I want to link formula to date so that if I change the date the formula
gets changed and pulls the data for that particular date.

For Eg: Column A1 has all the dates, B1 has emp code, In C1 I write a
formula i.e. vlookup so that I get the data for that emp for that
particular date. Now if I change the Date I should get the Data for that
employee for that date

Thanks


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Hi Jackie

The easiest way is to add another column at Column A. You dates are now
in Column B and emp codes in Column C and the related data in Column D.
In A1 enter: =A1&B1 and copy down.

Now select say E1and go to Data>Validation and choose list and use
A1:A100 as the Source. Now use

=VLOOKUP(E1,$A$1:$D$100,3,False)

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
For Eg: Column A1 has all the dates, B1 has emp code, In C1 I write a
formula i.e. vlookup so that I get the data for that emp for that
particular date. Now if I change the Date I should get the Data for that
employee for that date
Click to expand...

Depends how your source database looks like.
A. column A 'date', column B empcode, column C data
you can use =DGET() function - the olnly thing is that you have to remember
to setup criteria range properly, that is: you need use headers for
criteria.
For instance:
your database : named region 'data' - includes both data and column headers,
let's assume that column headers are: date, empcode, data
criteria range:
A1 header for date column in 'data' region, that is 'date'.
A2 header for empcode column in 'data' - 'empcode'
A2 date you are looking for, B2 empcode you are looking for
the syntax for the function is:
=DGET("DATA",3,A1:B2)
For further analysis (all the dates, one empcode) use =TABLE() array
function.

B. if employee codes are in rows (are the row headers) and dates are in
columns (column headers)
you need to perform a two way lookup:

=OFFSET(A4;MATCH(A1;rows_;);MATCH(B1;columns_))

where A4 is the top left 'corner' cell of your data/table, A1 is the date
you are looking for,
rows_ - named range for row headers, B1 - your date, dates_ - column headers
 
Hi Dave,

Please let me explain again
=VLOOKUP($C9,'\\ops-fs\PST_Rpts\Internal\PST Daily Reports\Daily Report
- March 04\TDR\[MyMail Report February 29.xls]Msn
PST'!$C$60:$O$109,6,FALSE)

this is the actual formula I use to get the data.
Please note that there is a file by name MyMail Report February 29.xls
and we have got different file for Each day. The Problem is while
creating a new file for the new month I need to change the date for each
and every row that represent a diff date like february 29, March 01 and
so on...

however I have a column that contains the days of the month like March
01,March 02 and so on...

In my file I have got Dates as heading underwhich I have written the
above mention vlookup formula to .
For Eg.

Cell B3 has Srno & Cell C3 has Emp Code & Cell D3 has First Name while
Cell E3 has Last Name and in Cell F3 has 28-Feb, Cell G3 has 29-Feb....
& so on.
And I trap data under each of these days by using above mention formula.
The Problem is I need to rewrite the formula while creating file for the
new month that contains diff set of days.

Is it possible to use my formula so take reference from the date field
that is Cell F3, G3.. and so on. by doing this all I need to do this is
to change the days in the above mention cells to create the new file
for the new month.

I hope you understand my problem.

Thanks
M E N






***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
If that other file is open, they you could use excel's =indirect() function.

But that won't work on closed workbooks.

But Harlan Grove posted a function that retrieves the value from a separate
instance of excel:
http://google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Hi Dave,

Please let me explain again
=VLOOKUP($C9,'\\ops-fs\PST_Rpts\Internal\PST Daily Reports\Daily Report
- March 04\TDR\[MyMail Report February 29.xls]Msn
PST'!$C$60:$O$109,6,FALSE)

this is the actual formula I use to get the data.
Please note that there is a file by name MyMail Report February 29.xls
and we have got different file for Each day. The Problem is while
creating a new file for the new month I need to change the date for each
and every row that represent a diff date like february 29, March 01 and
so on...

however I have a column that contains the days of the month like March
01,March 02 and so on...

In my file I have got Dates as heading underwhich I have written the
above mention vlookup formula to .
For Eg.

Cell B3 has Srno & Cell C3 has Emp Code & Cell D3 has First Name while
Cell E3 has Last Name and in Cell F3 has 28-Feb, Cell G3 has 29-Feb....
& so on.
And I trap data under each of these days by using above mention formula.
The Problem is I need to rewrite the formula while creating file for the
new month that contains diff set of days.

Is it possible to use my formula so take reference from the date field
that is Cell F3, G3.. and so on. by doing this all I need to do this is
to change the days in the above mention cells to create the new file
for the new month.

I hope you understand my problem.

Thanks
M E N

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
Click to expand...
 
Back
Top