Dynamic Range for Function (Vlookup etc)

[IshtiaqM]

Hi all.

I am looking to find out if there is any way (without using the Pivo
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 a
not sure how to write a vlookup with the dates as reference and then
vlookup.

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


Kindly mail me @ (e-mail address removed) for any clarifications. I ca
even fire the file i am working on. (Picture inserted)


Thanks a tonnne.


Isht

 

[Ragdyer]

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.

 

[IshtiaqM]

Hi Ragdyer, Thanks for the reply.

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

I even tried to create Dynamic Range (referring t
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.

Thank

 

[Debra Dalgleish]

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

http://www.contextures.com/xlFunctions01.html#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

 

[Ragdyer]

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