Data from 3rd Party software changes Position on Spreadsheet

[WWST]

The spreadsheet I have attached functions correctly but the problem is due to the position of the data. The spreadsheet gets its data from a 3rd party and I have no control over it, so its constantly changing positions in columns B-K in the worksheet. I have highlighted on the worksheet in yellow and blue how the data changes positions. Any help on how to fix this problem would be much appreciated.

[than_gold]

You could define the range & use it in formula.
try check on name manger.

[Richard Buttrey]

You say you have no control over it but how is the data coming into the workbook? Is it manually copied and pasted?
What do you mean by 'changes positions'? Do you simply means it starts on a dfferent row or is it shifted across the columns

The suggestion of using a range name might be appropriate but it would probably have to be a dynamic range name that could automatically detect where the data starts.

However I don't yet understand how the cells elsewhere on the Main Triggers sheet would need to be different if the data wasin a different position.

I think you'll need to add two Bet Angel sheets which shows data in different positions and how each one maps through to two examples of the Main Triggers sheet.

[WWST]

Richard thanks for the help, much appreciated. I will take some screenshots and upload them so its easier to understand.

[than_gold]

Pls provide the excel with what you looking for instead of images.

[WWST]

When I say I have no control over the data, what I mean is. In the Bet Angel software I select the spreadsheet and worksheet and it sends the data to that spreadsheet and worksheet. If I open up an empty excel workbook and select that workbook in betangel it will send the data. So there is absolutely no code in excel that retrieves the data.

The data is sent to the same rows and columns but as I said previously a section of the data changes positions. I have attached 2 screenshots to show how the data changes positions (2nd screenshot is blank row in between to make it easier on the eye)

So say for example I select the first check box on the main trigger page as I want to trade Scoop the Pot and then in the space of the next 30 minutes Scoop the Pot and Like the Sound change positions as shown in 2nd screenshot. What happens is the software will trade Like the Sound instead.

Any help appreciated.

[WWST]

Pls provide the excel with what you looking for instead of images.

Than_Gold I cant provide the excel because it wont show how the data changes positions as I have no control over it. I have made the changes myself by copying and pasting.

[WWST]

I should have added I understand the Maintrigger worksheet will have to be totally changed but this is not an issue.

[Richard Buttrey]

Difficult to comment without understanding what the back office software is and whether there are any other parameters you can set before invoking the 'send to Excel workbook' functionality.

Given that the data changes order that rather suggests that the data is ordered in some way, probably by date and time and that a record has been updated since you last received the data. There are no time stamps in the pcitures you show but maybe you should check that out.

In addition is this the only way to export the data. Are you able to download a csv file that you could open manually and reorder as appropriate before copying and pasting into the production workbook?

[WWST]

There are no other parameters I can set before invoking the send to Excel workbook.

The data changes are due to the odds. The favourite get ranked at the top.

Below is posted from betangel forum. I have experimented with the VLook Up and Match but am having problems including the VBA code

If you've copied a list of the runners into another sheet, you can always use the 'Match' function to get the row number, and the Vlookup to get the price.
No matter where Like the Sound appears on the Bet Angel sheet, it will always point to the correct row, so even if it moves to the end of the list, the correct row number will be returned.

If you want to back Like the Sound, for example, in vba i would write something like:

RowNum=sheets("Sheet2").Range("B2")
Sheets("Bet Angel").cells(RowNum,12)="Back"

[Richard Buttrey]

I don't think we've yet seen a workbook with any data in it, just blank cells in the Bet Angel sheet.

Please upload a working example and manually add the results you expect to see and clearly explain how the results cells on the Triggers are mapped to the Bet Angel sheet. i.e. the rules you apply to determine the results and how they are ordered.

There will have to be some common piece of information between the two in order to be able to create a formula to bridge between the two.

[WWST]

I have attached the spreadsheet. Data on BetAngel Spreadsheet B9 to K14 has been copied and pasted for demonstation purposes. (As mentioned previously there wouldnt be any code in here anyway as its sent directly from Bet Angel software)

How it works is - When worksheet Main Triggers has the P3 check boxes selected, Cell F14 is equal to G7 and the figure in F17 is below the figures in J3/K3 the order is sent from L9 in the BetAngel worksheet to Trade Like The Sound. This works fine, the problem is when the data changes as highlighted in the screenshots. When it changes as shown in screenshots instead of trading Like the Sound it would trade Scoop the Pot.

[Richard Buttrey]

This is still very confusing.

Please Upload a workbook that contains two or more sets of Bet angel and Triggers sheets where the data is different and in a different order on the Bet Angles sheets. At the moment the order of the runner names is the same on each sheet.

We need to understand how it differs if say the 1st and 3rd runners are swapped.

There are other confusing points. You say F14 = G7 but clearly is doesn't.
Neither do I understand the statement

"and the figure in F17 is below the figures in J3/K3 the order is sent from L9 in the BetAngel worksheet to Trade Like The Sound."

at the moment that is double dutch to me.

Remember you know exactly what all this data represents and how you use it, you probably use it on a daily basis. You need to understand we start with zero knowledge of what you're trying to do so you need to take us by the hand and spell out in quite precise detail what you do, where you change cell values or pick differentcheck boxes and what should happen.

Please don't ask us to look at both the workbook and pictures in this forum, they may be taken at different times, put all the explanations and notes in the workbook.

On a purely excel related point, is it really vital that you have merged cells. Most of us avoid them like the plague since they just cause too many problems

[WWST]

Richard thanks again for the help. I have adapted the spreadsheet and added some text so hopefully this makes it clearer. I understand what you are saying when you say you are starting with zero knowledge regarding the betangel software/spreadsheet.

[Richard Buttrey]

OK,

On the Main Triggers2 sheet the order of the runners is exactly the same as the Bet Angel2 data, so why don't the normal formula that link back to B9, B11, B13..etc on the Bet Angel 2 sheet work?

Are you expecting that J3 (say) on MainTriggers2 for Scoop the Pot should be the same value 2.18 that was in J5 on the earlier MainTriggers Sheet.

If so how would you expect the system to know the next set of Data has changed. To know that the system will need to have a copy of what the earlier data said, which implies that before the data is updated a macro is needed to take a copy of MainTriggers so that it can be used subsequently.

This may come down to a question of how Excel detects a change is being made if it is as automatic as you suggest.

[WWST]

Richard I have simplified the spreadsheet. Information in the Updated Information worksheet. Any help appreciated.