ExcelBanter - Vlookup or Hlookup

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Vlookup or Hlookup (https://www.excelbanter.com/excel-discussion-misc-queries/210173-vlookup-hlookup.html)

Vlookup or Hlookup

HI

I CANNOT GET MY HEAD AROUND THIS ONE SO AFTER A WEEK OF TRYING HERE IS MY
PROBLEM
IN COLUMN AB, ON WORK BOOK "OFFICE MNAGERS CALANDER.XLS, WORK SHEET 'DATES'
i HAVE THE DAILY DATES FOR EACH DAY FOR 2010 OVER 365 ROWS USING
=DATE(2010,1,1)+ROW()-1

IN ANOTHER WORK BOOK, THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE
DETAILS' , STARTING IN COLUMN C ROW 3 THE DATE OF THE FIRST DATE OF THE TOUR,
THEN D3 THEN E3 , THEN F3

I HAVE MANAGED ON THE, OFFICE MANAGERS CALANDER.XLS USINIG INDIRECT FUNCTION
AND iF FUNCTION TO BE ABLE TO PICK OUT THESE DATES AGAINST THE 365 DAYS WHICH
LEAVES ME WITH IN COLUMN AD
O
O
O ETC(AGAINST DATES NOT ON TOUR)
AND
1
1
1 ETC AGAINST DATES THAT ARE ON TOUR

MY AIM IS TO GET ACTUAL CITIES AGAINST THE FIXED DATE LIST INSTEAD OF THE 0
OR 1

ACTUAL CITIES ARE THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE DETAILS'
, STARTING IN COLUMN C ROW 5 THE DATE OF THE FIRST DATE OF A TOUR, THEN D5
THEN E5 , THEN F5 ETC

HOPE i HAVE DESCRIBED THIS WELL ENOUGH TO ALLOW SOMEBODY TO COME UP WITH A
FORMULA

THANKS FOR LOOKING

Stew

Vlookup or Hlookup

Please hit caps key and retype this without shouting
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"stew" wrote in message
...
HI

I CANNOT GET MY HEAD AROUND THIS ONE SO AFTER A WEEK OF TRYING HERE IS MY
PROBLEM
IN COLUMN AB, ON WORK BOOK "OFFICE MNAGERS CALANDER.XLS, WORK SHEET
'DATES'
i HAVE THE DAILY DATES FOR EACH DAY FOR 2010 OVER 365 ROWS USING
=DATE(2010,1,1)+ROW()-1

IN ANOTHER WORK BOOK, THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE
DETAILS' , STARTING IN COLUMN C ROW 3 THE DATE OF THE FIRST DATE OF THE
TOUR,
THEN D3 THEN E3 , THEN F3

I HAVE MANAGED ON THE, OFFICE MANAGERS CALANDER.XLS USINIG INDIRECT
FUNCTION
AND iF FUNCTION TO BE ABLE TO PICK OUT THESE DATES AGAINST THE 365 DAYS
WHICH
LEAVES ME WITH IN COLUMN AD
O
O
O ETC(AGAINST DATES NOT ON TOUR)
AND
1
1
1 ETC AGAINST DATES THAT ARE ON TOUR

MY AIM IS TO GET ACTUAL CITIES AGAINST THE FIXED DATE LIST INSTEAD OF THE
0
OR 1

ACTUAL CITIES ARE THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE
DETAILS'
, STARTING IN COLUMN C ROW 5 THE DATE OF THE FIRST DATE OF A TOUR, THEN D5
THEN E5 , THEN F5 ETC

HOPE i HAVE DESCRIBED THIS WELL ENOUGH TO ALLOW SOMEBODY TO COME UP WITH A
FORMULA

THANKS FOR LOOKING

Stew

Vlookup or Hlookup

Dear Bernard

At 65 Years of age I have learned that shouting does not get you anywhere fast

Is there at technical reason for your request or is it your preference not
to read messages in the upper case

Best Regards

Stewart

"Bernard Liengme" wrote:

Please hit caps key and retype this without shouting
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"stew" wrote in message
...
HI

I CANNOT GET MY HEAD AROUND THIS ONE SO AFTER A WEEK OF TRYING HERE IS MY
PROBLEM
IN COLUMN AB, ON WORK BOOK "OFFICE MNAGERS CALANDER.XLS, WORK SHEET
'DATES'
i HAVE THE DAILY DATES FOR EACH DAY FOR 2010 OVER 365 ROWS USING
=DATE(2010,1,1)+ROW()-1

IN ANOTHER WORK BOOK, THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE
DETAILS' , STARTING IN COLUMN C ROW 3 THE DATE OF THE FIRST DATE OF THE
TOUR,
THEN D3 THEN E3 , THEN F3

I HAVE MANAGED ON THE, OFFICE MANAGERS CALANDER.XLS USINIG INDIRECT
FUNCTION
AND iF FUNCTION TO BE ABLE TO PICK OUT THESE DATES AGAINST THE 365 DAYS
WHICH
LEAVES ME WITH IN COLUMN AD
O
O
O ETC(AGAINST DATES NOT ON TOUR)
AND
1
1
1 ETC AGAINST DATES THAT ARE ON TOUR

MY AIM IS TO GET ACTUAL CITIES AGAINST THE FIXED DATE LIST INSTEAD OF THE
0
OR 1

ACTUAL CITIES ARE THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE
DETAILS'
, STARTING IN COLUMN C ROW 5 THE DATE OF THE FIRST DATE OF A TOUR, THEN D5
THEN E5 , THEN F5 ETC

HOPE i HAVE DESCRIBED THIS WELL ENOUGH TO ALLOW SOMEBODY TO COME UP WITH A
FORMULA

THANKS FOR LOOKING

Stew

Vlookup or Hlookup

Stew -

In the online world, typing in ALL CAPS is considered to be
yelling ... plus, it's hard to read ...

//ray

Vlookup or Hlookup

Hi Stew,
I am having problems following your request. If you could maybe put some
sample data in your request, it might be easier to follow. For instance, it
sounds like you have two workbooks and both contain dates. I would probably
put a small sample like this:

Office Mgr Calendar:
Column AB Column AC
01/01/10 ?
01/02/10 ?

Road Acct:
Col C Col D Col E
01/01 01/02 01/03
city city city

If my guess at setup is correct, it sounds like you need an Hlookup formula
that would pull in the name of the city. Maybe that is what you were using?
I'm not certain if you would even need the indirect & if functions but
without knowing the whole picture, I can't say.
--
Constance

"stew" wrote:

HI

I CANNOT GET MY HEAD AROUND THIS ONE SO AFTER A WEEK OF TRYING HERE IS MY
PROBLEM
IN COLUMN AB, ON WORK BOOK "OFFICE MNAGERS CALANDER.XLS, WORK SHEET 'DATES'
i HAVE THE DAILY DATES FOR EACH DAY FOR 2010 OVER 365 ROWS USING
=DATE(2010,1,1)+ROW()-1

IN ANOTHER WORK BOOK, THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE
DETAILS' , STARTING IN COLUMN C ROW 3 THE DATE OF THE FIRST DATE OF THE TOUR,
THEN D3 THEN E3 , THEN F3

I HAVE MANAGED ON THE, OFFICE MANAGERS CALANDER.XLS USINIG INDIRECT FUNCTION
AND iF FUNCTION TO BE ABLE TO PICK OUT THESE DATES AGAINST THE 365 DAYS WHICH
LEAVES ME WITH IN COLUMN AD
O
O
O ETC(AGAINST DATES NOT ON TOUR)
AND
1
1
1 ETC AGAINST DATES THAT ARE ON TOUR

MY AIM IS TO GET ACTUAL CITIES AGAINST THE FIXED DATE LIST INSTEAD OF THE 0
OR 1

ACTUAL CITIES ARE THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE DETAILS'
, STARTING IN COLUMN C ROW 5 THE DATE OF THE FIRST DATE OF A TOUR, THEN D5
THEN E5 , THEN F5 ETC

HOPE i HAVE DESCRIBED THIS WELL ENOUGH TO ALLOW SOMEBODY TO COME UP WITH A
FORMULA

THANKS FOR LOOKING

Stew

Vlookup or Hlookup

You live and learn

Thanks Ray

"Ray" wrote:

Stew -

In the online world, typing in ALL CAPS is considered to be
yelling ... plus, it's hard to read ...

//ray

Vlookup or Hlookup

Hi Constance

Promise not to " Shout"

Office Mgr Calendar:
Column AB Column AC
01/01/10 =IF(DATE(2010,1,1)+ROW()-1='[The Road accounting
program.xls]Tour Profile'!$B$127,IF(AB1='[The Road accounting
program.xls]Tour Profile'!$B$137,"FALSE",(DATE(2010,1,1)+ROW()-1)))
01/02/10 =IF(DATE(2010,1,1)+ROW()-1='[The Road accounting
program.xls]Tour Profile'!$B$127,IF(AB1='[The Road accounting
program.xls]Tour Profile'!$B$137,"FALSE",(DATE(2010,1,1)+ROW()-1)))
Is Correct

In the aboveTour Profile $B$127 Gives start date of tour
Inthe above Tour Profile $B$137 Gives last date tour

I know it is cumbersome but it is the only thing my limited knowledge can
come up with

In Column AD
Row 1 =IF(AC1=FALSE,0,IF(AC1="FALSE",0,1))

Which gives me the "0" or "1"

Road Acct:
Col C Col D Col E
01/01 01/02 01/03
city city city

Is correct

Maybe this helps

Thanks for looking

Stew

"Constance" wrote:

Hi Stew,
I am having problems following your request. If you could maybe put some
sample data in your request, it might be easier to follow. For instance, it
sounds like you have two workbooks and both contain dates. I would probably
put a small sample like this:

Office Mgr Calendar:
Column AB Column AC
01/01/10 ?
01/02/10 ?

Road Acct:
Col C Col D Col E
01/01 01/02 01/03
city city city

If my guess at setup is correct, it sounds like you need an Hlookup formula
that would pull in the name of the city. Maybe that is what you were using?
I'm not certain if you would even need the indirect & if functions but
without knowing the whole picture, I can't say.
--
Constance

"stew" wrote:

HI

I CANNOT GET MY HEAD AROUND THIS ONE SO AFTER A WEEK OF TRYING HERE IS MY
PROBLEM
IN COLUMN AB, ON WORK BOOK "OFFICE MNAGERS CALANDER.XLS, WORK SHEET 'DATES'
i HAVE THE DAILY DATES FOR EACH DAY FOR 2010 OVER 365 ROWS USING
=DATE(2010,1,1)+ROW()-1

IN ANOTHER WORK BOOK, THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE
DETAILS' , STARTING IN COLUMN C ROW 3 THE DATE OF THE FIRST DATE OF THE TOUR,
THEN D3 THEN E3 , THEN F3

I HAVE MANAGED ON THE, OFFICE MANAGERS CALANDER.XLS USINIG INDIRECT FUNCTION
AND iF FUNCTION TO BE ABLE TO PICK OUT THESE DATES AGAINST THE 365 DAYS WHICH
LEAVES ME WITH IN COLUMN AD
O
O
O ETC(AGAINST DATES NOT ON TOUR)
AND
1
1
1 ETC AGAINST DATES THAT ARE ON TOUR

MY AIM IS TO GET ACTUAL CITIES AGAINST THE FIXED DATE LIST INSTEAD OF THE 0
OR 1

ACTUAL CITIES ARE THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE DETAILS'
, STARTING IN COLUMN C ROW 5 THE DATE OF THE FIRST DATE OF A TOUR, THEN D5
THEN E5 , THEN F5 ETC

HOPE i HAVE DESCRIBED THIS WELL ENOUGH TO ALLOW SOMEBODY TO COME UP WITH A
FORMULA

THANKS FOR LOOKING

Stew

Vlookup or Hlookup

I should add that Date on Road acc is on row 3 and city on row 5

Thanks
Stew

"stew" wrote:

Hi Constance

Promise not to " Shout"

Office Mgr Calendar:
Column AB Column AC
01/01/10 =IF(DATE(2010,1,1)+ROW()-1='[The Road accounting
program.xls]Tour Profile'!$B$127,IF(AB1='[The Road accounting
program.xls]Tour Profile'!$B$137,"FALSE",(DATE(2010,1,1)+ROW()-1)))
01/02/10 =IF(DATE(2010,1,1)+ROW()-1='[The Road accounting
program.xls]Tour Profile'!$B$127,IF(AB1='[The Road accounting
program.xls]Tour Profile'!$B$137,"FALSE",(DATE(2010,1,1)+ROW()-1)))
Is Correct

In the aboveTour Profile $B$127 Gives start date of tour
Inthe above Tour Profile $B$137 Gives last date tour

I know it is cumbersome but it is the only thing my limited knowledge can
come up with

In Column AD
Row 1 =IF(AC1=FALSE,0,IF(AC1="FALSE",0,1))

Which gives me the "0" or "1"

Road Acct:
Col C Col D Col E
01/01 01/02 01/03
city city city

Is correct

Maybe this helps

Thanks for looking

Stew

"Constance" wrote:

Hi Stew,
I am having problems following your request. If you could maybe put some
sample data in your request, it might be easier to follow. For instance, it
sounds like you have two workbooks and both contain dates. I would probably
put a small sample like this:

Office Mgr Calendar:
Column AB Column AC
01/01/10 ?
01/02/10 ?

Road Acct:
Col C Col D Col E
01/01 01/02 01/03
city city city

If my guess at setup is correct, it sounds like you need an Hlookup formula
that would pull in the name of the city. Maybe that is what you were using?
I'm not certain if you would even need the indirect & if functions but
without knowing the whole picture, I can't say.
--
Constance

"stew" wrote:

HI

I CANNOT GET MY HEAD AROUND THIS ONE SO AFTER A WEEK OF TRYING HERE IS MY
PROBLEM
IN COLUMN AB, ON WORK BOOK "OFFICE MNAGERS CALANDER.XLS, WORK SHEET 'DATES'
i HAVE THE DAILY DATES FOR EACH DAY FOR 2010 OVER 365 ROWS USING
=DATE(2010,1,1)+ROW()-1

IN ANOTHER WORK BOOK, THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE
DETAILS' , STARTING IN COLUMN C ROW 3 THE DATE OF THE FIRST DATE OF THE TOUR,
THEN D3 THEN E3 , THEN F3

I HAVE MANAGED ON THE, OFFICE MANAGERS CALANDER.XLS USINIG INDIRECT FUNCTION
AND iF FUNCTION TO BE ABLE TO PICK OUT THESE DATES AGAINST THE 365 DAYS WHICH
LEAVES ME WITH IN COLUMN AD
O
O
O ETC(AGAINST DATES NOT ON TOUR)
AND
1
1
1 ETC AGAINST DATES THAT ARE ON TOUR

MY AIM IS TO GET ACTUAL CITIES AGAINST THE FIXED DATE LIST INSTEAD OF THE 0
OR 1

ACTUAL CITIES ARE THE ROAD ACCOUNTING PROGRAM.XLS, WORK SHEET 'DATE DETAILS'
, STARTING IN COLUMN C ROW 5 THE DATE OF THE FIRST DATE OF A TOUR, THEN D5
THEN E5 , THEN F5 ETC

HOPE i HAVE DESCRIBED THIS WELL ENOUGH TO ALLOW SOMEBODY TO COME UP WITH A
FORMULA

THANKS FOR LOOKING

Stew

Vlookup or Hlookup

There is some useful newsgroup guidance for new users at
http://www.anta.net/misc/nnq/
--
David Biddulph

"stew" wrote in message
...
You live and learn

Thanks Ray

"Ray" wrote:

Stew -

In the online world, typing in ALL CAPS is considered to be
yelling ... plus, it's hard to read ...

//ray