MS Excel wizz's Is there a script for?

banditpowdercoat · #1 04-23-2009, 02:56 PM

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.

brizzo · #2 04-23-2009, 04:28 PM

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

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.

cav~firez22 · #3 04-23-2009, 04:38 PM

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

mike31154 · #4 04-23-2009, 05:28 PM

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.

Glennrf38 · #5 04-23-2009, 06:03 PM

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.

Doo · #6 04-23-2009, 06:26 PM

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...

Quote:

Originally Posted by nazerine

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

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.

brizzo · #7 04-23-2009, 08:18 PM

Quote:

Originally Posted by Glennrf38

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.

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.

Glennrf38 · #8 04-23-2009, 10:38 PM

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

banditpowdercoat · #9 04-24-2009, 04:25 AM

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

mike31154 · #10 04-24-2009, 02:48 PM

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.