MS Excel wizz's Is there a script for?
 

I made a spreadsheet in Excel for all my tank params. It's just a simple Date down the left side, and Month across the top. A page for each parameter. Now, I am grasping at straws here, but wondering if there is a script that will take all the latest input data, and display it? No matter where it is on the chart? Just take the last entry for that "page" and put it in XXX spot on a new page?


Just thinking it would be handier to have all my latest info on one page, not having to scroll through all the pages.

What I think you may be looking for a VLOOKUP()

Lets say you have a separate sheet for nitrates (Name the sheet "Nitrates"), 2 columns with date and reading

ie;

04/10/09 - 5ppm
04/11/09 - 4ppm

etc.

Now on your 'report' sheet, you can do:

=VLOOKUP("04/10/09", Nitrate!$A$1:$B:$B1000, 2, FALSE);

And of course instead of entering the date, you can reference a cell that contains a date.

Now let's do a little error correcting, if you don't have a result for a specified date:

=IF(ISNA(VLOOKUP("04/10/09", Nitrate!$A$1:$B:$B1000, 2, FALSE)), VLOOKUP("04/10/09", Nitrate!$A$1:$B:$B1000, 2, FALSE), "No data")

Hope that points you in the right direction, and my explanation makes sense. I have no formal excel training, but consider myself an excel guru somedays :mrgreen:

Oh yeah, the $A$1 sets those value as static references, so lets say if you drag the formula down to populate more cells, the values do not increment.

If i understand correctly, you want a chart that constantly updates to the current Day/Latest entry?

Been a while since I've done any playing around with Excel and I'm using an older version, but you might be able to use the "Insert Hyperlink" function to do what you need. I've got a modified sheet I found somewhere on the web which does an age calculation for your water to track water changes. I used this feature to link data from a cell on one sheet to another where I also had water change related data.

Not sure what version of Excel you have, but the feature I used is under the "Insert" menu. Simply choose "Insert Hyperlink" and this will open a small window with numerous options. On the left of this window should be a choice for "Place in This Document". Once you choose that icon, you should then see a cell reference option to the right. That will allow you to find and choose the cell on whichever sheet in the document you wish to display in the cell on the main sheet you're working on. HTH.

Nazarine. You almost have it. change the ranges from $A$1:$B:$B1000 to just A$:B$
It actually makes the vlookup a bit faster. Other than that I agree. I think that is the best way unless he goes with VBA. Depending on what he is looking for.

will this not lock it to one spot only?
He will have to change the "vlookup" every time he changes the location of the data...

Nice!! I was always wondering if there was a better way than a specific table range. That tip will help with some of my dynamic sheets that have data pulled from a database.

I would go with a VBA app myself but I'm kind of a Excel Geek.......

OK, I'm lost here.
cav-fire has a copy of whta I have, Heres a link to what I am using

http://www.banditpowdercoat.com/Down...parameters.xls

Is there a reason you've broken the date into two columns, i.e., month across the top and day down the left? Why not have the entire date in one cell, which can be formatted as a date type cell? That way you could use one column/row for the entire date value and then use the second column/row for all your parameters on one sheet, vice having a separate sheet for each parameter. Then you can work on a separate sheet to consolidate the info using the value lookup function recommended by the other dudes/dudettes.

umm, I dunno

Something like this....

http://hlj1yw.blu.livefilestore.com/...oAeeSW/eg2.jpg

The last date entry uses the function which automatically inserts today's date. All the previous ones are entered manually as the date the parameters were taken. There's also a feature in Excel (Freeze Panes under the Window menu) which allows you to lock the top row so that when you scroll down, it continues to be displayed for reference.

Hey Mike, thx. Locking the top row would be neat. That page looks good. I don't know why I never thought of putting it that way.

No probs and no need for complex look up formulae this way, but you can still do it if you wish to produce a graph or something on a separate page. To lock/freeze the top row, in my example pic, you would select the entire row 2 by clicking on the the number 2 on the left (second row), then click the Window menu and choose the "Freeze Panes" option.

Ya, the graph thing would be nice. I'm more used to looking at graphs and such. Easier to undersatnd and see the changes over time. Man, theres alot in Excel I don't know hehehe

Just tried a quick line graph for Ca using the graph wizard and wasn't really all that great since the values were all 400. So in this case it's a flat line. Need more varied data to have a nifty graph I think.

Ya, same here. But untill 10 min ago, I never knew I could do a graph.

It's a powerful application and you can learn a lot just using the help feature, if you know what you're looking for and have time on your hands. Most people (including myself) only ever scratch the surface of what's available, maybe make use of 5% of the apps capability. I've noticed many people use Excel to make lists etc. because it's in a nice grid/table format. If you're not making any kind of calculations, graphing or data tracking, a table in the word processor of your choice makes a list just as well. You just need to format the table to show gridlines or cell borders. MS Word even has the capability of formatting cells in tables with rudimentary calculations. Mind you I'm still using the Office 2000 version of all this stuff. No idea what can be done with the latest & greatest.

http://www.mrexcel.com/learn-excel.html

Hands down one of the best excel books I've ever read.. Actually it's probably one of the best techy books I've ever read!

When the book first came out you could download the eBook for free; too bad the offer has ended :(