fill range of cells with corresponding data

[mazan2010]

Hello everyone
I have attached workbook, which have data entry to made layout according to value enter
as the follow
I enter Start well in sheet1 in cell L2, As my example A1
typical layout will fill according to is in sheet3,
so first sample will be loaded in B2 in sheet layout, but if any other value in L2, for example if its B2, it will be in C3 and so on as in sheet3

and the maximum for each letter is 12, mean it start for the letter A, B, C, D, E, F, G, H (1 -12)

the problem now how can create correct column in C in Sheet1 and start with cell like column E
then transfer it to layout to corresponding value as sheet3

I don't know if my explanation its clear but I will also attach photo for plate used in my work

[ChemistB]

I think this is what you are asking for. In Layout B2 copied across and down

Please Login or Register  to view this content.

Can you give an example of what your sheet1 would look like if L2 was to start with B6 for example?

[mazan2010]

@ChemistB thank you so much for your post
as you see first need to correct data in column C in sheet1 because as you see in photo the maximum for each letter A to H is 12 , so after reach for exmple A12 next sample will be in B1 and after B12 next sample will be in C1 and so on

[mazan2010]

Please kindly see example start from B6

I think if we can use column D in sheet1 as helper to identify which row start to put formula
for example if H2 contain B6 so will look to column D for matched value then start to fill column A, B, C as the follow
in column A first row will be CONCATENATED formula between Cell H1 and I1 ( in my example is A18)
second row in column A will be like this formula =IF(OR($A$1="",C2=""),"",LEFT(A1,FIND("SPL",A1)*3)&(SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10))+1)
as in original file


in column B will get data from H3 till finish number of sample

and column C its combination between three column A,B,D in three-line with formula =CONCATENATE(A6,CHAR(10),B6,CHAR(10),C6)

[mazan2010]

Hello everyone
here my new file with some trial to solve problem with change value of start well as in sheet name my trail but still can't solve it even by using some helper column
and also I make sheet case result for example and case layout to show what the correct data to be
I hope it will be clear, I try to do my best to solve this issue but my knowledge still very little in comparing to expert here



As in example in sheet case result L3 and M2 will be change from time to time, according to this start cell in column B will be change according matched Value in F column

[mazan2010]

If there is any advice even with macro how can I do these

[bebo021999]

I use 1 helper column A only to produce list increasing string:

A1:

Please Login or Register  to view this content.

From A2:

Please Login or Register  to view this content.

Then B2 sheet Layout:

Please Login or Register  to view this content.

Fill all table

[mazan2010]

@bebo021999
Wow Amazing Dear bebo for this great solution, I'm very thankful your help and support

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[mazan2010]

But there is small notice that this not have limit for data generated, if you see file in post number 5 you will find L1 in sheet case result refers to how many sample will be work, so generated data need to be fitted to it

[bebo021999]

Jusi add a logical statement at beginning to test:
A2 sheet 1:
=IF(ROWS($1:1)>=$H$4,"",Initial-formula)
With H4 contain number of samples

[mazan2010]

Work very well
is there is way to make data validation for number of sample is matched with selected start well
I mean total well in layout is 96 well, so if start well is A1 and number of sample is 96 it will be good, but in case start well other than A1 and select number of sample 96 or any other number
for example
if I select start well C2, it's meaning the rest will be available is 71 sample, so 96 can't accept

[bebo021999]

In H4: count number of sample with start Well in H2:

=96-(CODE(H2)-65)*12-RIGHT(H2,LEN(H2)-1)+1

[mazan2010]

THANK YOU SO MUCH bEBO021999
ITS WORK VERY WELL