Help copying a formula pattern and colour fill

So I'm pretty much self taught, I can work my way around a spreadsheet but I don't know the quickest most efficient ways so I thought I'd come ask a true expert.

The screen shot below shows what I'm currently working on for a professional sports tipster, now I know what to do and how to do it but doing it fast and with 0 errors is another story.

Now 01.08.2018 is formulated and setup correctly as is 02.08.2018 now the only problem I'm having is when I try highlight and drag to auto formulate its messing the colours up, and also its not copying the pattern so the data is wrong. How can I do this more accurately so it copies the pattern.

Further details, I'm basically trying to work out the strike rate for certain units which we use to bet with so for example 1 unit is £20, 0.5 units is £10 etc etc. So all i want to do is input the data from the daily tracker to the chart but of course theres 29-31 days in a month so as its a new feature I'm trying to catch up and I'm just trying to get the pattern to copy suit so chart 1 is from day one, chart 2 is day 2 and I want to drag it down and it automatically pull data from the remaining days. Not because I'm lazy but because it will mean a lot less errors if i have to manually input it.

Attachment 586567

You're trying to fill down the the merged cell with the date along with the 4 columns x 3 rows next to it? Can't really see what is going wrong without a sample file to play with on something like this.

If you select all of those as a group, then use the fill handle to pull down, the pattern should remain.

Or you could keep 1 day blank as a template, drag the merged cell down to fill the dates in sequence, then use copy & paste for the other columns.

If you have formulas it will copy exact, if you have constants it can go wrong, there are settings to identify pattern type, but generally they don't work to well in the way that you need.

Instead if entering 0.25 units enter ="0.25 units" do the same for the others, then drag down. Does that cure it?

Shouldn't come up with name if you used the double quotes.

Fill patterns work well as long as the values only either increase, or decrease within the range being dragged, not with a mixture. When you get alternating increasing and decreasing values it fails.

You can attach a sample file to the thread, click the 'Go advanced' button, then scroll down and look for the link to 'Manage attachments' (paperclip icon doesn't work).

Says invalid file, maybe because i use a slightly different excel cause im on mac :/

I'm using software called Libreoffice, its the same I just prefer it to excel but systems/formulas etc are exactly the same.

I'm struggling to explain it in a way most understanding.. It's just not doing what its suppose to its simple really.

I just want to take data from 18 sheets and add them to one master sheet so i can identify the strike rate between certain odds.

Maybe that is why it is not filling down the same.

I tried it using ="0.25 units", etc. With the coloured bands, dragged it down and it worked as expected.

Yes that worked, i must've put ' ' instead, now how can i get it to copy the formula pattern.

The formula is like this

Total bets
0.25 units | =sum($'01.08.2018'.H7)
0.50 units | =sum($'01.08.2018'.H8)
1 uniits | =sum($'01.08.2018'.H9)

Won
0.25 units | =sum($'01.08.2018'.I7)
0.50 units | =sum($'01.08.2018'.I8)
1 uniits | =sum($'01.08.2018'.I9)

Lost
0.25 units | =sum($'01.08.2018'.J7)
0.50 units | =sum($'01.08.2018'.J8)
1 uniits | =sum($'01.08.2018'.J9)

For the next date the sum is the same and the only thing that changes is the date to 02.08.2018

Screen Shot 2018-08-19 at 00.39.44.png

Now all i want it to do is copy the formula pattern so i can simply drag and drop the highlighted box and itll fit it out using the pattern for 03.08.2018, 04.08.2018 etc etc

Has a quick look at the libreoffice help / wiki site, looks like not all formulas are created equal. I have provided an Excel formula that I think is what you need based on the information provided.
If there are differences between excel and libre formulas then you will need to make the changes. I do not have sufficiant information to write the formula in that format.

From your description, I assume:-
-That each date is a separate sheet / tab located within the same file.
-That the date '01.08.2018' is located in a merged cell (A2:A4) on the formula sheet.
-Your dates are actual valid dates formatted as dd.mm.yyyy and not text strings.
-That first formula for 'total bets' & '0.25 units' going into C2. Your dates appear to be text, not formatted numbers.

As this is an excel forum, your Excel formula will be something like this

=INDIRECT("'"&TEXT(LOOKUP(1e+100,$A$2:$A2),"dd.mm.yyyy")&"'!R"&(7+(MOD(ROWS(C$2:C2)-1,3))&"C"&(7+COLUMNS($C2:C2)),0)

You will need to change the ranges noted to match the location of your actual data, paying close attention to the positions of the $ prompts.
$A$2:$A2 should refer to the first date as a single cell, where cells are merged it should only refer to the first of the cells that have been merged.
C$2:C2 and $C2:C2 should refer to the cell holding the first formula before you fill the other rows / columns.

Oh god, i get a bit of that but a lot of that is new to me, sorry If im getting annoying Jason.

Can you check this below.

Screen Shot 2018-08-19 at 12.18.03.png

Let's try and clarify a few things first, the formula was based on my listed assumptions being correct. If they were, then the formula, adjusted to your ranges would be

=INDIRECT("'"&TEXT(LOOKUP(1e+100,$A$11:$A11),"dd.mm.yyyy")&"'!R"&(7+(MOD(ROWS(C$11:C11)-1,3))&"C"&(7+COLUMNS($C11:C11)),0)

Enter this one into C11 only, then fill from there. From what I could see on the libre information, I think that you might have to replace ! with . but not sure without having anything to test on.

If any of my assumptions were wrong, then they need to be clarified before I can attempt a formula that might be closer to what you need.

1. Are dates proper dates, or text strings? This applies to the dates in the sheet where the formula will bring the results, and the dates in the original source.
A proper date will show the next day, or a number if you try the formula =A11+1 a text date will give an error.

2. Using your sample formulas in post 14, are the dates in the formulas referring to sheets named "01.08.2018", "02.08.2018", etc. Or a list of dates in another sheet?
If a list of dates in another sheet, where are they? Sheet name and cell references for the first 2 or 3 dates.

There may be more that needs clarifying before this gets resolved, but that will get us started.

If you can save the file in a format that the forum will accept, then that will help (.xlsx would be the common type, or xlsm if your file uses vba, or equivalent).

If not, can you upload to a file share, dropbox or similar? Not sure if excel will open it if it is an unknown file type, but can try.