Look up

[1988craig]

Hi everyone,

New to the forum & excel- I'm a novice.....

The below sheet is a basic example of a sheet use to schedule works. The schedule consists of approx 300 lines per week. The data is exported from another source and then imported so I cannot add columns.

I am trying to auto populate the start and end date columns B & C based on cloumns D-H having text entered, column B & C I need to populate with the corrosponding date D1-H1.

I currently do this manually by filtering out blanks in column D, entering the required date in B&C & so on through to column H.

I have searched online for a solution but failed, I am unsure which function I actully require to complete.

Many thanks in advance for any assistance.

 

[1988craig]

* Also point one should have been is this possible?

 

[My Aswer Is This]

It's possible.
Would you please explain this more clearly for me:
You said:
I am trying to auto populate the start and end date columns B & C based on cloumns D-H having text entered, column B & C I need to populate with the corrosponding date D1-H1.

Like if I enter anything in this column I want this entered into this column same row

 

[1988craig]

Hello,

basically when a cell is not blank- a name entered into a line between columns D-H, I am trying to pick up the date where the name first appears- task stating on this date, to populate in column B. Then where the name last appears in the same line- task finishing on this date to populate column C.

As per the below but I have just entered the dates manually to hopefully explain what I am trying to achieve.

I hope this is clarifies, many thanks for the assistance!!

 

[Peter_SSs]

@1988craig
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this is what you want.

20 10 10.xlsm
	A	B	C	D	E	F	G	H
1				10/10/2020	11/10/2020	12/10/2020	13/10/2020	14/10/2020
2		Start	End
3	Job 1	10/10/2020	10/10/2020	Dave
4	Job 2	10/10/2020	11/10/2020	Bob	Bob
5	Job 3	10/10/2020	14/10/2020	Steve	Steve	Steve	Steve	Steve
6	Job 4	11/10/2020	11/10/2020		Dave
7	Job 5	12/10/2020	12/10/2020			Dave
8	Job 6	13/10/2020	13/10/2020				Dave
9	Job 7	14/10/2020	14/10/2020					Dave
10	Job 8	12/10/2020	14/10/2020			Bob	Bob	Bob
11	Job 9
Start End

Cell Formulas
Range	Formula
B3:B11	B3	=IFNA(INDEX(D$1:H$1,MATCH(TRUE,INDEX(D3:H3<>"",0),0)),"")
C3:C11	C3	=IFNA(LOOKUP("zzz",D3:H3,D$1:H$1),"")

 

[1988craig]

Hi Peter,

The above has done it, thank you for the formulas.
Regarding the tips- greatly appreciated I was unaware, I shall follow you advice.

Many thanks for your time & the assistance,

Best Regards,
Craig

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

BTW, If the heading dates are always consecutive and any names in the row are in adjoining cells, you could use this simpler formula in column B.

20 10 10.xlsm
	A	B	C	D	E	F	G	H
1				10/10/2020	11/10/2020	12/10/2020	13/10/2020	14/10/2020
2		Start	End
3	Job 1	10/10/2020	10/10/2020	Dave
4	Job 2	10/10/2020	11/10/2020	Bob	Bob
5	Job 3	10/10/2020	14/10/2020	Steve	Steve	Steve	Steve	Steve
6	Job 4	11/10/2020	11/10/2020		Dave
7	Job 5	12/10/2020	12/10/2020			Dave
8	Job 6	13/10/2020	13/10/2020				Dave
9	Job 7	14/10/2020	14/10/2020					Dave
10	Job 8	12/10/2020	14/10/2020			Bob	Bob	Bob
11	Job 9
Start End (3)

Cell Formulas
Range	Formula
B3:B11	B3	=IF(C3="","",C3-COUNTA(D3:H3)+1)
C3:C11	C3	=IFNA(LOOKUP("zzz",D3:H3,D$1:H$1),"")