Forza, Excel, and Me

[Blooze]

Just what the Title says...  the three of us, we be mates.  This Thread will be dedicated to How-To's, Questions, and so on, about handy little and large things concerning the Best Add-On Forza's Got... Microsoft Excel (2003)

Now, first up - a plea for help.  Does anybody know how to turn a string function into an address?

Example:
Cell A4 in my Stable Worksheet contains the following function.  The referenced worksheet/cell contains the full name of a car, in this case the Cougar and "1970 Mercury Cougar" is displayed.
='C:\Forza4\GICheeze\Muscle\[G_F-Stock.xls]Cougar'!$A$1

What I would like to do is...
cell A1 contains only the worksheet name - C:\Forza4\GICheeze\Muscle\[G_F-Stock.xls]Cougar
cell A2 contains a string function - ="='"&A1&"'!$A$1"

and have cell A2 display the same "1970 Mercury Cougar" but instead it produces a string copy of the function in cell A4 in the first paragraph.

If I use the INDIRECT function I get a #REF! error.  The function Help shows a string function returning a value, but it is quite simple - ie, not an address in a foreign worksheet. 

If I could get this to work, you have no idea how simple I can make data collection for my Stable worksheet.  Right now it requires hand referencing each value in the target tuning sheet.  What I have in mind would be to enter the Worksheet name, and the entire row of 30 data values would auto magically update.

Searches on the knowledge base have produced nothing of any value.

Any help would be appreciated.

$

[Fit4aking]

Wish I could be more help Blooze.  Seems I pick up Excel For Dummies every time I'm in Barnes & Noble but I never buy the thing.  Setting up a dedicated spreadsheet is a bit beyond me at this point so most of your post was Greek. 

[Blooze]

Now, here is the output of a little jobbie that I been messing with for a couple hours this morning...

Sandy Creek Road

Driver	Car	Time
Cheeze	71 Javelin-AMX	113.871
Cheeze	70 Cougar	114.279
Cheeze	65 GTO	115.761
xx	xx	xx

Or how it looks under the editor's hood...

Code: [Select]

[b][u]Sandy Creek Road[/u][/b]
[table]
[tr]
[td][center][b][u]          Driver          [/u][/b][/center][/td]
[td][center][b][u]                       Car                      [/u][/b][/center][/td]
[td][center][b][u]          Time          [/u][/b][/center][/td]
[/tr]

[tr][td]Cheeze[/td][td]71 Javelin-AMX[/td][td][center]113.871[/center][/td][/tr]
[tr][td]Cheeze[/td][td]70 Cougar[/td][td][center]114.279[/center][/td][/tr]
[tr][td]Cheeze[/td][td]65 GTO[/td][td][center]115.761[/center][/td][/tr]

[tr][td]xx[/td][td]xx[/td][td][center]xx[/center][/td][/tr]

[/table]


The important part for this discussion is the three rows that contain the lap information.  The rest of it is just boiler plate.  Well, the three rows are too, but they are created with live data from a spreadsheet.  I wanted to share the functions that I use with you.  I would give you the sheet, but the functions are all connected to other spreadsheets, in other workbooks even, and those types of functions don't travel well.

First, here is what the function for the Javelin looks like in the spread sheet when not calculated.  I call this function my Boiler Plate Function and will refer to it as BPF from now on...

="

"&IF(D5="",$D$1,D5)&"

"&IF(E5="",$E$1,E5)&"

"&TEXT(F5,"0.000")&"

"

Code: [Select]

="[tr][td]"&IF(D5="",$D$1,D5)&"[/td][td]"&IF(E5="",$E$1,E5)&"[/td][td][center]"&TEXT(F5,"0.000")&"[/center][/td][/tr]"
Cell $D$1 contains "Cheeze".   If D5 is empty, you see that, otherwise you see whatever is typed in.

Cell E5 contains this function; =VLOOKUP($B5,'4G_StStock.xls'!Stable,20,0)

In this function, $B5 contains the Car's name. 

NOTE: The Car's Name, Cell A1 in the tuning sheet is used as a primary key though out all my spread sheets.  That is why I always use a reference to that cell when I want to display the name.  That way the name only exists in one place, if I edit the name, all references are immediately updated, and that way I can count on using the name as a key - they are always the same. 

But what I am looking for here is the car's nick name, and this is stored in the #20 column of the Stable worksheet.

Cell $E$1 contains the words "A Car".  That is used in the BPF if E5 is empty.

Cell F5 contains this function; =VLOOKUP($B5,NoAm!Hotlap,$F$1,0)

This is the same deal, only this time I am looking up the car name in the leaderboard sheet named "NoAm", and in the range named "Hotlap".  The data I am after is in the column whose number is contained in cell $F$1, or in this case, the column number 8 (the column that contains Infineon Long times.)  The reason I do it this way is that I can change all of the report targets by just entering the column number in one place... $F$1.

What I end up with is a 3 column table, with the Driver Name, the Car Nickname, and the Time.  It can be sorted on any field and it will hold its data as long as the Car Name is (Column B) is included in the sort.  (I guess I have a 4 column table then...  )  The boiler plate function could be sorted as well, but it doesn't have to be.  It just pumps out what ever info is in cells D, E, and F 5.

This looks like a bunch of jibberish, no doubt.  But if you think about it, and where you have your information stored in you spread sheets, it will start to make a little sense.

And, it makes maintaining a leaderboard for one of these challenges a hell of a lot easier than entering all that BBCode by hand...

$

[Fit4aking]

While a lot of it is jibberish I can see what you are saying about the BBCode.  It takes up far more characters than the information you are trying to portray.  Include the boiler plate info in that and you have way more text than should be necessary to simply maintain a list of names and descending order values associated with them. (Times in this case)

As valuable as it may be in your individual organizational efforts wouldn't you still have to populate a sheet with the names of the entrants, their car, and time before swapping it over to a BB table and updating it?  Not that I think that is a bad idea but it is a temporary file that can be stored for review here instead of clogging up your hard drive.  There has to be a more efficient way to simply insert a name, car name, and time and have the template order them correctly without having to refer back to a bunch of different cells of a spread sheet or a workbook.

Just brainstorming here but we are list-making at this point in table form, not necessary an overly complicated thing to do for the sake of keeping score.  However, the sheet that I use mostly uses columns to input data and outputs them in subsequent columns, I rarely use rows with anything other than keeping track of lap times but even that is a pain in the arse most times.  I haven't had a solid track list to populate since FM2 and I really don't do the data mining like some here. 

Very interesting stuff Blooze, I will follow along and hope the bulb comes on and I can see this for what it is and not as a foriegn language.

[Blooze]

This a true thing...  somebody has to enter the data somewhere.

But, eventually somebody else wants to sort the data...  or something has changed and a piece of data has to be changed.

And there is the rub.

Working with a BBCode table is not an easy thing to do.  Sorting is accomplished with Cutting one row, and inserting it in another position in the table.  Changing the data is difficult because when there is a lot of it, locating the field that needs changing is bugger, specially with this little editor window.

Using the spreadsheet doesn't change the fact that I will have to enter the data...

It might be worthwhile to note that I only have to enter and edit data when keeping a leaderboard for a challenge.  I will be posting my Sandy Creek times for all of my SS cars later on today or tomorrow.  I didn't have to enter any data for that, that wasn't already in my spreadsheets somewhere.

...but, once entered manipulating the data becomes a whole lot simpler.

And I'm all about Simpler.  Why, there's many folks that say I am about as simple as I could be... 

$

[Tonka Crash]

Blooze, on your first problem with the indirect function.  Indirect only works when the referenced spreadsheet is open. As soon as the sheet G_F-Stock.xls sheet is closed the cell goes #REF. There is a work around. Someone wrote a function indirect.ext in the Morefunc add-in available here

Cell A1 D:\[G_F-Stock.xls]Cougar   
Cell A2  =INDIRECT.EXT("'"&A1&"'!A4")  returns 1970 Mercury Cougar

I'm using Excel 2003 and it works on my installation with the Morefunc add-in

[Blooze]

Indirect only works when the referenced spreadsheet is open. As soon as the sheet G_F-Stock.xls sheet is closed the cell goes #REF.

Now I wonder why them Helpsheeters didn't mention that?  The buggers...

Thanks a bunch Tonka.  I'll let ya know how it works out.

$

[Tonka Crash]

Wish I could be more help Blooze.  Seems I pick up Excel For Dummies every time I'm in Barnes & Noble but I never buy the thing.  Setting up a dedicated spreadsheet is a bit beyond me at this point so most of your post was Greek.

I've learned more about Excel playing Forza than I ever did for real work and I've actually used Excel for work since around '98.  I'd never seen the indirect function Blooze had trouble with until this morning.  I don't know how I'll use it, but it looks like a slick tool for certain applications.

[Moosejaw]

Blooze,

I know you are after a Excel solution to you problem, but I just wanted to point you in the direction of a BBCode editor I've started using called the WYSIWYG BBCode Editor (http://www.shajul.net/software/wysiwyg-bbcode-editor/).

It gives you a simple split screen where you enter your text and format it as you want it in the top pane.  Press a button and it converts it to BBCode in the bottom pane.

It might be worth a look?

[Blooze]

I'll give it a looksee Moose - thanks!

Did the editor solve the problem you had posted about a while ago?

$

[Open2nd]

I haven't read the entirety of the thread but if you want a persistent leaderboard couldn't you just embed a googledocs spreadsheet? Was that even something that was being discussed? I'm so lost!

[DirtDriver]

Casey, if you can tell me HOW to do that I would appreciate it! The steps given in the help screen for google docs does not fit the available steps you can take on the spreadsheet. I can't make it work.

DD

[Moosejaw]

Did the editor solve the problem you had posted about a while ago?
$

It's enabled me to easily create a "template" for my posts that I can save and and go back to as and when I want (see the cars stats in the Rides thread in Moose's Motors).

I haven't read the entirety of the thread but if you want a persistent leaderboard couldn't you just embed a googledocs spreadsheet?

Now that sounds like a good idea. Do you know if this can be done?

[Open2nd]

I know they've been embedded over at Exodus before and it is running the same basic architecture as this board if I'm not mistaken.  Warped may know more about it, I haven't done anything with googledocs other than look at them, sorry.

[Moosejaw]

The best I can get is a link that opens a new window with the data in, see my example:

https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=0Arh-NiYllcotdENZRUl4cHlRVGRZU3daZHVEMXlTTXc&single=true&gid=0&range=a3%3Ae19&output=html

Searching the internet has revealed there may be some new BBCodes to do with google docs (http://www.vbulletin.org/forum/showthread.php?t=250782). The tags [gs] and [/gs] are required to embed part of a google spreadsheet but I have know idea if this code is freely available to plug into a SMF board.