Dynamic cell referencing – 'outside the box' thinking required?

[krazykaj]

Hello all,

I am hoping someone with some genius problem solving skills and excel know-how can possible help guide me in terms of the below…

---Background: skip if you like---
I am helping out some research analysts. These analysts all have big spreadsheets called ‘models’. Depending on the research they are doing, each model is wildly different. These models are used to calculate numbers and generate data.
The thing is, the end product, the data being generated, is consistent across the board. They are all spitting out the same numbers but just on different products. So while each analyst differs greatly in terms of method, research, maths, excel skill …. They all needs to ultimately generate the same data.

Here is where I am trying to help – at the moment they are using a ‘Database Sheet’ that links to a SQL database and uploads the generated data from the models.
This Database Sheet is basically a locked/fixed sheet and the analyst uses the Name Manager to set ranges. The database sheet effectively consolidates all the required data in one place and then through about 50 million (slight exaggeration) of vba code, it uploads it to the database.

The problem is 2 fold. First, the person that built this Database Sheet no longer works here. They built it 9 years ago for an old version of excel; So there is no support for it, it was badly coded and nobody know what it is doing or how it works or if something crashes/breaks (especially as we are all moving to office 2010) nobody knows how to fix it.
Second, for its simple mandate, the database sheet is extremely complicated to use. You can’t do or edit anything in the sheet for fear that the macro won’t work and error are uploaded into the database.


---Solution Request---
What I want to do is completely remove this database sheet and move away from vba and macros as much as possible!

Is there something smart, efficient and easy to implement that I can design for these analysts to link data that they are generating here, there and everywhere in their models to a fixed format/template sheet? I am thinking I can then just spit out an e.g. csv file that I can just hand to IT and they can map the upload to the server. Easier, cleaner, IT can support it and the upload process moves out of the hands of the analyst. No bulking crazy macro laden spreadsheets required.

I don’t really want to use the name manager. It is a bit tedious and complicated for the analysts.
Is there a way to somehow flag a cell that it contains a specific data point?


*Simplified example*
Say the analyst needs to generate how many oranges are eaten per year, how many boxes are used to transport these oranges and the price target of the cost of oranges. 3 numbers.
Depending on the analyst they are going to have these three final calculation numbers who knows where in a spreadsheet. These numbers could be next to each other, or spread out in three different sheets.
Is there a way to maybe make a custom formula/reference that they can tag a cell with?
So say the number that tells me how many oranges are eaten per year is in Sheet3 cell H14 and the formula is =sum(H1:H13)
Could I make something so that the analyst can type this formula instead: = sum(H1:H13){OrangesEaten}
Then, in a totally separate template file I can just point the sheet to the analysts workbook and it will look through the workbook, every cell, and find the cell with the {OrangesEaten} flag and just pull the data from that?

Or something along those lines?

I really want to make this more streamlined and easy to use. I also really want to make it as dynamic and flexible as possible. No fixed references and as little VBA/macros as possible that actually process data….



Hope that makes sense.
I am open to ANY suggestions or solutions that could work.
The current process is horrible, hugely time consuming and crashes/breaks half the time

Thank you for any help, time or suggestions you may have.
It is greatly appreciated.
KJ

 

[sijpie]

I appreciate your drive to steer away from macros (vba), yet if you want to do this purely with formulas you are going to have very similar problems as with badly designed VBA, namely one change to the datasheet, an added column or row, and everything falls flat.

A properly setup vba code will in fact be far more fool proof and reliable then urely functions. A properly coded system will search for the values it needs wherever they have moved on the sheet. It is all in the specifications. Data processing should not be difficult and a pretty simple VBA can probably do everything required. More difficult will be to map out what the analysts are doing, why, and what is the most efiicient way to code this. Why are they using differing methods? All this needs to be looked at from the start, and then robustly coded.

 

[krazykaj]

Thank you sijpie,

I appreciate the comments and thoughts.
The problem is as you said, being able to map the data that the analysts are creating.

In terms of the analysts using different methods, unfortunately is it hard to get them doing anything the same as they are effectively working out numbers for investment products. The universe is so large, and the analysts all have their different style (which is part of the value add) I cannot really restrict them. Hence I am trying to see if there are any genius ideas around some method that allows the analyst to do what they want, but easily flag fields. That way the onus is on them to 'flag' the data, wherever it is, and I can then easily pull it.

I am not closed to using VBA/macros ... I just want to ensure I am not using it just because it can. I want to make this as easy and flexible as possible. I Probably won't be supporting this in a massive way so it needs to make sense and be dynamic enough that it really shouldn't need any editing/development because the analysts can do whatever they want....

Even if using VBA, is there a way to maybe make a custom formula/reference that they can tag a cell with as I describer above?
That concept to me seems the best so far.... ?

Anyway,
thank you again for your time and help.
kindest regards
KJ

 

[sijpie]

Something like that should be possible, some UDF (user defined function) or routine that asks some info and then flags cells or fills ranges. But the developer would need to sit with the sheet and see what is required.

An alternative is to use conditional formatting. To get the term searched for and colour all the cells containing that term. If the analysts don't know about conditional formatting or find it to cumbersome to set up an easy macro could help them.
If you were to put this macro in a module, and set up a shortcut key for it or a button on the sheet (the latter probably less easy to do if the sheet is constantly renewed) then you can try it out on a few people to see what they think.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CondForm()<br><SPAN style="color:#007F00">' request a search term and highlight all occurances</SPAN><br><SPAN style="color:#007F00">' of the search term in the entire sheet. Previous</SPAN><br><SPAN style="color:#007F00">' conditional formatting is erased</SPAN><br><br>    <SPAN style="color:#00007F">Dim</SPAN> sSearch <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#007F00">' get the searchterm from the user</SPAN><br>    sSearch = InputBox("Enter cell text to highlight", "Highlight search term")<br>    <SPAN style="color:#00007F">With</SPAN> Cells<br>        .FormatConditions.Delete    <SPAN style="color:#007F00">' delete existing conditional formatting</SPAN><br>        .FormatConditions.Add Type:=xlTextString, String:=sSearch, _<br>            TextOperator:=xlContains<br>        .FormatConditions(.FormatConditions.Count).SetFirstPriority<br>        <SPAN style="color:#00007F">With</SPAN> .FormatConditions(1).Interior<br>            .PatternColorIndex = xlAutomatic<br>            .ThemeColor = xlThemeColorAccent6<br>            .TintAndShade = -0.249946592608417<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        .FormatConditions(1).StopIfTrue = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Fazza]

"Is there something smart, efficient and easy to implement ..."

For a first step, simple & efficient is to have one dedicated worksheet for the results. The analysts can weave their magic anyway they like across multiple worksheets but then - like the current set up - have a dedicated worksheet purely for the summary results. Either using two rows or two columns. I'll assume two columns

So cell A1 might be "oranges eaten" and cell B1 a super simple formula to link to the result cell whereever it is in the workbook.
Then cell A2 "oranges wasted" and in cell B2 a super simple formula to link to the result cell whereever it is in the workbook.
Then cell A3 "trucks used" and in cell B3 a super simple formula to link to the result cell whereever it is in the workbook.
etc. etc

Without VBA, this would be set up manually. I'm thinking this single summary worksheet would be best & that you may not want VBA to make it for you, but without VBA it needs the analyst to set it up manually. With VBA, it could be automated.

Being a single location for the desired data, and in a simple format, then one can work with it however is best to do the next step.

I suspect is likely to be a good approach to have such a summary sheet.