Dynamic Range for Function (Vlookup etc)
 

Hi all.

I am looking to find out if there is any way (without using the Pivot
table feature) to do a Dynamic cell reference as part of a formula.


My worksheet has the following columns.
1. "Dates" ( individuals for all days of the month)
2. "Eng Name" for all dates and is at times sporadic
3. "Worked Items" # for all days worked
4. "Recieved"
5 "Sent"

Now, the problem is : I have about 3000 rows of information. I am
not sure how to write a vlookup with the dates as reference and then a
vlookup.

Simply Put. An engineer may appear in the table for some dynamic
number of times (which is usually unknown). I want to be able to lookup
wrked items etc for an engineer under a given date range (which I need
to calculate as it is going to be pretty dynamic and changes all the
while). Therefore for all the engineers.


Kindly mail me @ for any clarifications. I can
even fire the file i am working on. (Picture inserted)


Thanks a tonnne.


Ishti


--
IshtiaqM
------------------------------------------------------------------------
IshtiaqM's Profile: http://www.msusenet.com/member.php?userid=258
View this thread: http://www.msusenet.com/t-3340066

Since you seem uncomfortable with the use of Pivot Tables, how about using
simple "Auto Filters", which would fit your needs precisely.

Just select your header row, then,
<Data <Filter <Auto Filter

This gives you a small down arrow in each column header, which when
expanded, allows you to pick and choose the criteria contained within that
specific column that you wish to be displayed.
You can choose "Custom" from within this expanded column header, to perform
some calculated searches if you need to.

Once you display the results of your first filter choice (ie .. engineer's
name), you can then expand the date column, and decide the exact date range
you wish to display for this particular engineer.

It's all very simple and straight forward, and perhaps easier then using a
pivot table.
It does, of course, have none of the robustness of the Pivot Table.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"IshtiaqM" wrote in message
...

Hi all.

I am looking to find out if there is any way (without using the Pivot
table feature) to do a Dynamic cell reference as part of a formula.


My worksheet has the following columns.
1. "Dates" ( individuals for all days of the month)
2. "Eng Name" for all dates and is at times sporadic
3. "Worked Items" # for all days worked
4. "Recieved"
5 "Sent"

Now, the problem is : I have about 3000 rows of information. I am
not sure how to write a vlookup with the dates as reference and then a
vlookup.

Simply Put. An engineer may appear in the table for some dynamic
number of times (which is usually unknown). I want to be able to lookup
wrked items etc for an engineer under a given date range (which I need
to calculate as it is going to be pretty dynamic and changes all the
while). Therefore for all the engineers.


Kindly mail me @ for any clarifications. I can
even fire the file i am working on. (Picture inserted)


Thanks a tonnne.


Ishti


--
IshtiaqM
------------------------------------------------------------------------
IshtiaqM's Profile: http://www.msusenet.com/member.php?userid=258
View this thread: http://www.msusenet.com/t-3340066

Hi Ragdyer, Thanks for the reply.

Actually I am a little Newbie in these things. and My audicity is Iam
trying to link these to sheets to individual Engineers (one wrkSheet
per engineer) hoping to use the Vlookup to get values only for that
Engineer for only specific dates. My data range is comming from
multiple sources and have around 30000 on enterprisewide data
collation. Makes it tough for me to use the autofillers.

I even tried to create Dynamic Range (referring to
http://www.contextures.com/xlNames01.html) with no success.

I am soo helpless with this thing right now.


Hopefully i will get it to go as planned.

Thanks


--
IshtiaqM
------------------------------------------------------------------------
IshtiaqM's Profile: http://www.msusenet.com/member.php?userid=258
View this thread: http://www.msusenet.com/t-3340066

If you want a total for a specific engineer for a date range, you could
use the Sumproduct function. There's an example he

http://www.contextures.com/xlFunctio...tml#SumProduct

You could use a formula that refers to the start and end dates that
you've entered on your worksheet, e.g.:

=SUMPRODUCT(--($A$2:$A$44=J1),--($A$2:$A$44<=J2),--($B$2:$B$44=J3),E2:E44)

where the start date is in cell J1, end date in J2 and engineer name in
cell J3

IshtiaqM wrote:
Hi Ragdyer, Thanks for the reply.

Actually I am a little Newbie in these things. and My audicity is Iam
trying to link these to sheets to individual Engineers (one wrkSheet
per engineer) hoping to use the Vlookup to get values only for that
Engineer for only specific dates. My data range is comming from
multiple sources and have around 30000 on enterprisewide data
collation. Makes it tough for me to use the autofillers.

I even tried to create Dynamic Range (referring to
http://www.contextures.com/xlNames01.html) with no success.

I am soo helpless with this thing right now.


Hopefully i will get it to go as planned.

Thanks




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Are you saying that you *don't* have a "Master", composite sheet, containing
links to the individual sheets?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"IshtiaqM" wrote in message
...

Hi Ragdyer, Thanks for the reply.

Actually I am a little Newbie in these things. and My audicity is Iam
trying to link these to sheets to individual Engineers (one wrkSheet
per engineer) hoping to use the Vlookup to get values only for that
Engineer for only specific dates. My data range is comming from
multiple sources and have around 30000 on enterprisewide data
collation. Makes it tough for me to use the autofillers.

I even tried to create Dynamic Range (referring to
http://www.contextures.com/xlNames01.html) with no success.

I am soo helpless with this thing right now.


Hopefully i will get it to go as planned.

Thanks


--
IshtiaqM
------------------------------------------------------------------------
IshtiaqM's Profile: http://www.msusenet.com/member.php?userid=258
View this thread: http://www.msusenet.com/t-3340066