Using the value of a cell as part of the range in another formula

[BdaBased]

Can I use a formula/cell value to determine the starting point of a range?

for example:
In excel - I type "B4" into cell C1

if I have a formula:
=vlookup(A4,B1:B5,1,false)

can I replace the starting point of this range (namely 'B1') with the result of C1 making the formula effectively
=vlookup(A4,B4:B5,1,false)

many thanks

 

[SkipVought]

Hi,

Why does your lookup table starting point vary?

Please explain what you are trying to do.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BdaBased]

I've moved a little further on and have a solution that works - there may be a much simpler solution of course and any ideas would be appreciated!

I have a list of employee information stored in "Staff Info" tab. The columns show Department, Employee ID, Employee Name. There are four distinct departments and about 400 staff members....this list is refreshed quite often as it's not unusual for staff to be added or to switch departments.

I have created a new tab for each department and I want the records for only that department to magically appear....without having to do any copying/pasting/deleting etc

The formula I have so far in the tab "DEPARTMENT 1" is....

in cell A2:
=MATCH("Dept1",INDIRECT(CONCATENATE("'STAFF INFO'!A",A1+1)):'STAFF INFO'!$A$450,0)+A1

in cell A3:
=MATCH("Dept1",INDIRECT(CONCATENATE("'STAFF INFO'!A",A2+1)):'STAFF INFO'!$A$450,0)+A2

This formula returns the row number of each record where the department = "Dept 1"

I can then (using cell A3 example above) type:
=INDIRECT(CONCATENATE("'STAFF INFO'!B",A3))

into cell B2 of the Dept 1 tab and it returns the value of the second column and second instance of the dept "Dept 1".

Messy I know - but it seems to work............

 

[SkipVought]

I have created a new tab for each department and I want the records for only that department to magically appear....without having to do any copying/pasting/deleting etc

Please explain what is the value added by displaying data for each dept on a separate sheet? Cannot that EASILY be accomplished by (1 BEST) AutoFilter and select the desired DEPT, or (2 BETTER sort of) if you reeeeealy need the data on separate sheets, put a PivotTable on each sheet by DEPT.

But, once again, what is the added value of separate sheets. BTW, I ask this question OFTEN of people requesting reporting solutions in the aerospace company I work for, and I have YET to get a good justification for the expense and inconvenieice of doing so.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BdaBased]

I actually agree with you wholeheartedly. I believe the reason they want separate tabs is for ease of viewing - I too think autofilter would do the job nicely! I will put the question to my superiors and see what they come back with.

thanks for your help

 

[SkipVought]

Ease of viewing? With 400 sheets? That's an oxymoron!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Skip: That's 400 employees - only 4 departments.

But Skip's suggestion of Pivot Table(s) is still your best bet. I'd offer them two options:

[tab]1) a single Pivot Table with Department as a Page Filter
This will give them a single dropdown at the top left corner and the Pivot table will update on the fly as they select their department

[tab]2) a Pivot Table on each of the four sheets
This would be much easier than the process you're currently after

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Ok 4 sheets. I was only off by 2 orders of magnitude!

But seriously, the reason for having ONE report, be it a PivotTable or the source table using the AutoFilter, is that as sure as God made green grass, the reporting criteria will change -- formatting, data content, whatever.

ONE place to FIX stuff--Ideal!

Multiple places to fix the same thing over and over--time consuming and error prone, unless you are spawning each sheet programatically.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}