|
| Author | Thread |
|
|
12/11/2009 03:55:35 PM · #26 |
Originally posted by kirbic:
Originally posted by AJSullivan:
Heres one for you guys:
I have a sheet that uses if statements to pull data from another sheet. That if statement essentailly says that if x1 = todays date, then pull that column. It works plenty well.
The issue is that at the end of day, we have to go into that sheet, highlight the info, copy - paste special - values, in order to not lose that data at midnight.
Were trying to come up with a good way to have that data fall into the columns and remain there. The best we've come up with is just adding a macro that does the copy and paste for you, but honestly clicking the macro button is only one step less than just copying and pasting.
Any ideas? |
One way is just to modify your "if" formula to test for x1 <= to today's date and then it will be true beginning today, and going forward. The two problems with this are:
1.) If the data in the other sheet gets corrupted, so does your sheet (but also, updates to the source data are automatically reflected, a good thing0
2.) the sheet can get *very* large as the number of formulas increases.
Another option is to create a very simple macro that does the copy/paste values operation when you close the file. That way, all data that you acquired from the time you opened the file is made static at the time the file is closed. |
Yeah, none of that will fly. The problem is that its a spreadsheet that captures daily activities. So 8 people enter thier information onto an "INPUT" sheet. Then that information is totaled on another sheet to give a daily look at what occurred, then that info is pulled to a 3rd sheet which compiles it on a monthly view (by day) but on a monthly chart. Its highly inefficient, yet much more efficient than the way they were doing it before (pen and paper and 30 minutes later) This one sheet alone stood out brightly on my year end review, because it increased the accuracy of our stats (which in turn increases forecasting and headcount accuracy) and that it got the managers out of here 30 minutes sooner.
Oh well. I mean its not broke, but i'm always looking for ways to make things work better. |
|
|
|
12/11/2009 04:37:33 PM · #27 |
From what it sounds like you are doing, Visual Basis will set you free. You can control every aspect of a workbook and automate an amazing amount.
Originally posted by AJSullivan:
Originally posted by kirbic:
Originally posted by AJSullivan:
Heres one for you guys:
I have a sheet that uses if statements to pull data from another sheet. That if statement essentailly says that if x1 = todays date, then pull that column. It works plenty well.
The issue is that at the end of day, we have to go into that sheet, highlight the info, copy - paste special - values, in order to not lose that data at midnight.
Were trying to come up with a good way to have that data fall into the columns and remain there. The best we've come up with is just adding a macro that does the copy and paste for you, but honestly clicking the macro button is only one step less than just copying and pasting.
Any ideas? |
One way is just to modify your "if" formula to test for x1 <= to today's date and then it will be true beginning today, and going forward. The two problems with this are:
1.) If the data in the other sheet gets corrupted, so does your sheet (but also, updates to the source data are automatically reflected, a good thing0
2.) the sheet can get *very* large as the number of formulas increases.
Another option is to create a very simple macro that does the copy/paste values operation when you close the file. That way, all data that you acquired from the time you opened the file is made static at the time the file is closed. |
Yeah, none of that will fly. The problem is that its a spreadsheet that captures daily activities. So 8 people enter thier information onto an "INPUT" sheet. Then that information is totaled on another sheet to give a daily look at what occurred, then that info is pulled to a 3rd sheet which compiles it on a monthly view (by day) but on a monthly chart. Its highly inefficient, yet much more efficient than the way they were doing it before (pen and paper and 30 minutes later) This one sheet alone stood out brightly on my year end review, because it increased the accuracy of our stats (which in turn increases forecasting and headcount accuracy) and that it got the managers out of here 30 minutes sooner.
Oh well. I mean its not broke, but i'm always looking for ways to make things work better. |
|
|
|
|
12/17/2009 07:19:19 PM · #28 |
| OK excel smarties. I have a worksheet that is basically a trailing twelve months tally. The cells have conditional formatting. What is the easiest way to remove the month that's falling off, shift the other 11 months over one column, and add the new month all while keeping the current conditional formatting for each cell? |
|
|
|
12/17/2009 08:31:06 PM · #29 |
Originally posted by DrAchoo:
OK excel smarties. I have a worksheet that is basically a trailing twelve months tally. The cells have conditional formatting. What is the easiest way to remove the month that's falling off, shift the other 11 months over one column, and add the new month all while keeping the current conditional formatting for each cell? |
What's best might depend on the structure of the sheet. I'd probably work it so that the tally cells are always pointing to the same twelve ranges, but move the data "underneath" it. If that's not possible, there are other ways to not have to modify your tally cells, but get the data to "roll" properly anyway. And finally, if no level of worksheet sophistication meets the requirement, then a little VBA goes a long way.
|
|
|
|
12/17/2009 10:09:43 PM · #30 |
Here is a formula I built to calculate Langliers Index values from 5 test values,
=C4-((LOG(1/((1/10^((2902.39/(273+D4))+0.02379*(273+D4)-6.498))/(10^(-(1820000)*((78.3)*(273+D4))^-1.5*2^2*(((0.000025)*F4)^0.5/(1+((0.000025)*F4)^0.5)-0.3*((0.000025)*F4)))))))+(LOG(1/(B4*(20/50)*0.001/40)))-(LOG(1/((1/10^(0.01183*(D4)+8.03))/(10^(-(1820000)*((78.3)*(273+D4))^-1.5*2^2*(((0.000025)*F4)^0.5/(1+((0.000025)*F4)^0.5)-0.3*((0.000025)*F4))))^2)))-LOG(2*(E4*0.001/100))-LOG(10^(-(1820000)*((78.3)*(273+D4))^-1.5*(((0.000025)*F4)^0.5/(1+((0.000025)*F4)^0.5)-0.3*((0.000025)*F4)))))
For those that are counting there is a section with 7 levels in it(parenthesis that is)
I am pretty sure under the paste special menu there is a paste formatting selection which pastes in the conditional formatting as well.
Message edited by author 2009-12-17 22:11:00.
|
|
|
|
Current Server Time: 08/25/2025 12:35:12 PM  |
Home -
Challenges -
Community -
League -
Photos -
Cameras -
Lenses -
Learn -
Help -
Terms of Use -
Privacy -
Top ^
DPChallenge, and website content and design, Copyright © 2001-2025 Challenging Technologies, LLC.
All digital photo copyrights belong to the photographers and may not be used without permission.
Current Server Time: 08/25/2025 12:35:12 PM EDT.
|
