I'm stressed because I have to trace the referred cell in the formula, go to the first/second column where the description exists, and look for the next formula. Considering it is multiple sheets that goes to 200-300, I am stressed of getting around in excel. Any tips? I wish my team can just use python, R, stata, or any other languages. Something more descriptive than sending me to cell B2 after sending me to cell B108 or something. It's stressful going up and down like this. Is there any way to navigate better?
Tell them you can't excel with Excel. In my most recent brush with Excel, I just had to add up nos. in cells, but luckily the cells I had to consider had a fixed recurring pattern of being uniformly three cells apart, so instead of selecting them individually each time, I just went like "=C2+C5+C8..." in the formula bar.
Excel has a basic programming language. If you know any other programming language, it shouldn't take all too long to learn it and use it, maybe a few hours. That should allow you to do what you need, if you're figure it out Edit: said programming language is actually just macros but yeah, shouldn't take all too much effort if you already know programming
ah that sounds tough, I know excel is capable of analyses but I've never used it like that. Id rather use SPSS, SAS or R....Maybe this link can help with organizing your data? https://support.microsoft.com/en-us...ab8-f543-4fda-85ed-76bb0295ffc4#ID0EBBL=macOS
it's not that... it's reading someone else's excel. God... I wish for excel to give us ability to label their columns and rows.
I think it is VBA and something tells me you are better of using python to generate all the excel formulas I understand that python/r/etc is forbidden but cant you use it to sort of generate your excel sheet with formulas filled in? At the very least having proper variables than random cells will solve a lot of bs you are facing. Then again you will have to mark all cells in proper variables first so idk. https://stackoverflow.com/questions/62915308/adding-formulas-to-excel-spreadsheet-using-python https://stackoverflow.com/questions/39195957/write-formula-to-excel-with-python https://stackoverflow.com/questions/51348874/save-pandas-dataframes-with-formulas-to-xlsx-files Heck I guess you just straight up use pandas too... You can store the cell addresses as variables and abuse fstrings or something, at the end an excel sheet is generated with formulas so I think it shouldnt interfere with your team... maybe... >.> Good luck <.<
like I've said above... it's the matter of readability. my team done everything in excel. so... what you see as C6, or C102 in its formula is just... what does that mean?
There should be a function under formula tabs or something that let's u trace precedent and dependents
https://stackoverflow.com/questions/733019/declaring-variables-in-excel-cells/733044 Okay apparently you can name cells in excel... Edit: just tested and it works,
and? it's rather tricky to make a representative name for all of them. a time series data where the column is time, and row is variable name. maybe I'll learn vba to name them all lol.
It's pretty difficult to mess up excel. If they've given you spaghetti code, it's pretty obvious that they shouldn't have been using excel in the first place. I'm wondering if the easiest way to get around it is to program a c# console application to read the file for you.
That is why you should use LibreOffice Calc! It can not only code in VBA, but python, BeanShell and javascript.
You can name a range. and use =INDEX(weirdRangeName, 5) to use it as an array And that is just a transposed sheet, isnt it? It should make no real difference
excel? code? not really spaghetti code. no code involved except in formula. at this point maybe I'll just load it as a dataframe in python/R. less hassle for me.