Hi,
I have to change something in one of my projects (reports) and i think i am
going crazy, i really need your help.
I have a report with 2 worksheets.
In the first one i have columns for: acct#, acct name, address, phone.......
In the second worksheet i have again acct# (vlookup from first worksheet),
acct name (vlookup from first worksheet) and another 31 columns with days of
the month.
The sales rep will add in each day a number (how many hours they spend in
that day for that account).
So far so good. Now if an Sales rep is adding a new account in the first
worksheet, they have to arrange the accounts ascending (vlookup is working
just with ascending numbers) in the second worksheet will be a mess.
The acc # and acc name will be fine but the hours (numbers) will not match
anymore the correct accounts.
I need somehow to keep together the acc # + acc name + hours( the all row
with numbers for each account) after we add a new account and we rearrange
ascending all the accounts.
Please help.
Thanks,
Alex

VLOOKUP() does not HAVE to have an ordered list to work with:

=VLOOKUP(C1,A1:B5,2,FALSE)
as a very simple example. The ,FALSE) parameter at the end says that the
lookup table's 1st column may or MAY NOT be ordered. With the ,FALSE) 4th
parameter, the table's first column does not have to be sorted.

So perhaps just rewriting the VLOOKUP formulas will do the job for you?

"Alex" wrote:

Hi,
I have to change something in one of my projects (reports) and i think i am
going crazy, i really need your help.
I have a report with 2 worksheets.
In the first one i have columns for: acct#, acct name, address, phone.......
In the second worksheet i have again acct# (vlookup from first worksheet),
acct name (vlookup from first worksheet) and another 31 columns with days of
the month.
The sales rep will add in each day a number (how many hours they spend in
that day for that account).
So far so good. Now if an Sales rep is adding a new account in the first
worksheet, they have to arrange the accounts ascending (vlookup is working
just with ascending numbers) in the second worksheet will be a mess.
The acc # and acc name will be fine but the hours (numbers) will not match
anymore the correct accounts.
I need somehow to keep together the acc # + acc name + hours( the all row
with numbers for each account) after we add a new account and we rearrange
ascending all the accounts.
Please help.
Thanks,
Alex

Hi,
You wright, VLOOKUP does not have to have an ordered list, the problem is -
i need (for many reasons) to have that list ordered ascending. Sorry my
mistake.
Can i insert a formula in this cells for hours (let say VLOOKUP) and still
the sales rep to add their numbers, even if a formula it is in that cell???
Thank you for your time.

"JLatham" wrote:

VLOOKUP() does not HAVE to have an ordered list to work with:

=VLOOKUP(C1,A1:B5,2,FALSE)
as a very simple example. The ,FALSE) parameter at the end says that the
lookup table's 1st column may or MAY NOT be ordered. With the ,FALSE) 4th
parameter, the table's first column does not have to be sorted.

So perhaps just rewriting the VLOOKUP formulas will do the job for you?

"Alex" wrote:

Hi,
I have to change something in one of my projects (reports) and i think i am
going crazy, i really need your help.
I have a report with 2 worksheets.
In the first one i have columns for: acct#, acct name, address, phone.......
In the second worksheet i have again acct# (vlookup from first worksheet),
acct name (vlookup from first worksheet) and another 31 columns with days of
the month.
The sales rep will add in each day a number (how many hours they spend in
that day for that account).
So far so good. Now if an Sales rep is adding a new account in the first
worksheet, they have to arrange the accounts ascending (vlookup is working
just with ascending numbers) in the second worksheet will be a mess.
The acc # and acc name will be fine but the hours (numbers) will not match
anymore the correct accounts.
I need somehow to keep together the acc # + acc name + hours( the all row
with numbers for each account) after we add a new account and we rearrange
ascending all the accounts.
Please help.
Thanks,
Alex

You cannot type a number into a cell that has a formula without overwriting
the formula.


Gord Dibben MS Excel MVP

On Thu, 4 Mar 2010 06:40:05 -0800, Alex
wrote:

Hi,
You wright, VLOOKUP does not have to have an ordered list, the problem is -
i need (for many reasons) to have that list ordered ascending. Sorry my
mistake.
Can i insert a formula in this cells for hours (let say VLOOKUP) and still
the sales rep to add their numbers, even if a formula it is in that cell???
Thank you for your time.

"JLatham" wrote:

VLOOKUP() does not HAVE to have an ordered list to work with:

=VLOOKUP(C1,A1:B5,2,FALSE)
as a very simple example. The ,FALSE) parameter at the end says that the
lookup table's 1st column may or MAY NOT be ordered. With the ,FALSE) 4th
parameter, the table's first column does not have to be sorted.

So perhaps just rewriting the VLOOKUP formulas will do the job for you?

"Alex" wrote:

Hi,
I have to change something in one of my projects (reports) and i think i am
going crazy, i really need your help.
I have a report with 2 worksheets.
In the first one i have columns for: acct#, acct name, address, phone.......
In the second worksheet i have again acct# (vlookup from first worksheet),
acct name (vlookup from first worksheet) and another 31 columns with days of
the month.
The sales rep will add in each day a number (how many hours they spend in
that day for that account).
So far so good. Now if an Sales rep is adding a new account in the first
worksheet, they have to arrange the accounts ascending (vlookup is working
just with ascending numbers) in the second worksheet will be a mess.
The acc # and acc name will be fine but the hours (numbers) will not match
anymore the correct accounts.
I need somehow to keep together the acc # + acc name + hours( the all row
with numbers for each account) after we add a new account and we rearrange
ascending all the accounts.
Please help.
Thanks,
Alex