Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
Greeting Experts:
I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
=B34*(5+TODAY()-DATE(2007,12,13))
-- David Biddulph "jeannie v" wrote in message ... Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
is not necessary to post the same question many times
-- regards from Brazil Thanks in advance for your feedback. Marcelo "jeannie v" escreveu: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
Try basing it on a date, i.e. today is 13th December so if you want it to be
*5 today try =B34*(TODAY()-DATE(2007,12,8)) tomorrow this will be the equivalent of B34*6 and so on, incrementing by 1 each day "jeannie v" wrote: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
Thank you....is there another way to do this? My problem is that I have a
different # of observation days a month that are being evaluated by analysis excluding weekends, holidays and the first day of the month...so the date doesn't help...I want it to say the Target for today is 5 times the Daily Target Rate, but tomorrow it will be 6 times the Daily Target Rate. Can you help? -- jeannie v "David Biddulph" wrote: =B34*(5+TODAY()-DATE(2007,12,13)) -- David Biddulph "jeannie v" wrote in message ... Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
Understand that the "TODAY" formula is volitile. True if you use it,
tomorrow when you open the file it will be 1 more, but next week if you go back and open the old file it will be 7 more (ie: it will be for THAT day)............if you are saving the files each day, you would be better off with manual entry or a macro to set a firm date. Vaya con Dios, Chuck, CABGx3 "jeannie v" wrote: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
Thank you CLR.......... for the info....If i want to build the macro for a
firm date, could you tell me how I can do that? Thank you, -- jeannie v "CLR" wrote: Understand that the "TODAY" formula is volitile. True if you use it, tomorrow when you open the file it will be 1 more, but next week if you go back and open the old file it will be 7 more (ie: it will be for THAT day)............if you are saving the files each day, you would be better off with manual entry or a macro to set a firm date. Vaya con Dios, Chuck, CABGx3 "jeannie v" wrote: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
I suggest that you go away and think about what your question really is.
When you know what you want to ask us, then I'm sure someone who understands your question will do their best to answer you, but I must admit to being totally confused. In one breath you are saying "the date doesn't help...", and in the next you are saying "the Target for today is 5 times the Daily Target Rate, but tomorrow it will be 6 times the Daily Target Rate", and in your original question you said "I have a formula that changes by 1 each day...Example: B34*5.....on the next day it would be =B34*6 and so on...." You want to change it each day but without using the date? No, I can't fathom it. :-( -- David Biddulph "jeannie v" wrote in message ... Thank you....is there another way to do this? My problem is that I have a different # of observation days a month that are being evaluated by analysis excluding weekends, holidays and the first day of the month...so the date doesn't help...I want it to say the Target for today is 5 times the Daily Target Rate, but tomorrow it will be 6 times the Daily Target Rate. Can you help? -- jeannie v "David Biddulph" wrote: =B34*(5+TODAY()-DATE(2007,12,13)) -- David Biddulph "jeannie v" wrote in message ... Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
I'm just reading this thread with interest. Realizing that I know nothing
except what you have written, I would reiterate one part of CLR's post but state it in the form of a question: would you be better off with manual entry? If you have to open the worksheet each day that applies, what could be simpler than typing, say in cell B33 or another cell that is convenient, that variable number that you want--5 or 6 or 10 or 16--since you would presumably already have the formula =B34*B33 in some cell. I also recognize the saying "different folks, different strokes" might apply. It might be worth your while to explore ways to automate this. For some people, the challenge and the process are important. Sometimes though, the benefit might not be worth the effort. "jeannie v" wrote: Thank you CLR.......... for the info....If i want to build the macro for a firm date, could you tell me how I can do that? Thank you, -- jeannie v "CLR" wrote: Understand that the "TODAY" formula is volitile. True if you use it, tomorrow when you open the file it will be 1 more, but next week if you go back and open the old file it will be 7 more (ie: it will be for THAT day)............if you are saving the files each day, you would be better off with manual entry or a macro to set a firm date. Vaya con Dios, Chuck, CABGx3 "jeannie v" wrote: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
Thank you for your thoughts...my problem is that once I can ge this thing to
auto-change the number, I can create the macro to copy that formula to 32 additional worksheets automatically. Maybe I'm looking at this as less easy than it should be..do you think? Thanks for any thoughts, -- jeannie v "TRYING" wrote: I'm just reading this thread with interest. Realizing that I know nothing except what you have written, I would reiterate one part of CLR's post but state it in the form of a question: would you be better off with manual entry? If you have to open the worksheet each day that applies, what could be simpler than typing, say in cell B33 or another cell that is convenient, that variable number that you want--5 or 6 or 10 or 16--since you would presumably already have the formula =B34*B33 in some cell. I also recognize the saying "different folks, different strokes" might apply. It might be worth your while to explore ways to automate this. For some people, the challenge and the process are important. Sometimes though, the benefit might not be worth the effort. "jeannie v" wrote: Thank you CLR.......... for the info....If i want to build the macro for a firm date, could you tell me how I can do that? Thank you, -- jeannie v "CLR" wrote: Understand that the "TODAY" formula is volitile. True if you use it, tomorrow when you open the file it will be 1 more, but next week if you go back and open the old file it will be 7 more (ie: it will be for THAT day)............if you are saving the files each day, you would be better off with manual entry or a macro to set a firm date. Vaya con Dios, Chuck, CABGx3 "jeannie v" wrote: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
This, in the WorkbookOpen module of your Template, saved with the cell A1
empty, will install the date that the template is opened.....and it will remain that way forever no matter how many times it's opened. Private Sub Workbook_Open() If Range("a1").Value = "" Then Range("a1").Value = Date & " " & Time Else End If End Sub But, that said, you can instill a hard-date any time you wish just by selecting a cell and doing Ctrl-semicolon.....which might better serve your needs. hth Vaya con Dios, Chuck, CABGx3 "jeannie v" wrote: Thank you CLR.......... for the info....If i want to build the macro for a firm date, could you tell me how I can do that? Thank you, -- jeannie v "CLR" wrote: Understand that the "TODAY" formula is volitile. True if you use it, tomorrow when you open the file it will be 1 more, but next week if you go back and open the old file it will be 7 more (ie: it will be for THAT day)............if you are saving the files each day, you would be better off with manual entry or a macro to set a firm date. Vaya con Dios, Chuck, CABGx3 "jeannie v" wrote: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
how 'bout having an auto_open macro that every time you open the file
it increments a certain cell by one? obviously you wouldn't be opening it on weekends or holidays. you could even have it ask you first, before it does it, in case you've already opened it for the first time that day................ public sub auto_open() dim ws as worksheet dim target as range dim myNumber as long set ws = activeworksheet set target = ws.range("a1") if msgbox ("Do you want to increment the number?", vbyesno) = vbyes then myNumber = myNumber + 1 else myNumber = myNumber end if end sub then your formula would be =B34*A1. the cell you designate as "target" could be hidden in an outside-the-print-area column, or even on another worksheet (fix range to reflect if on another worksheet). just an idea :) susan On Dec 13, 2:39 pm, jeannie v wrote: Thank you....is there another way to do this? My problem is that I have a different # of observation days a month that are being evaluated by analysis excluding weekends, holidays and the first day of the month...so the date doesn't help...I want it to say the Target for today is 5 times the Daily Target Rate, but tomorrow it will be 6 times the Daily Target Rate. Can you help? -- jeannie v "David Biddulph" wrote: =B34*(5+TODAY()-DATE(2007,12,13)) -- David Biddulph "jeannie v" wrote in message ... Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v- Hide quoted text - - Show quoted text - |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
i forgot to add this in:
myNumber = target.value On Dec 13, 3:25 pm, Susan wrote: how 'bout having an auto_open macro that every time you open the file it increments a certain cell by one? obviously you wouldn't be opening it on weekends or holidays. you could even have it ask you first, before it does it, in case you've already opened it for the first time that day................ public sub auto_open() dim ws as worksheet dim target as range dim myNumber as long set ws = activeworksheet set target = ws.range("a1") if msgbox ("Do you want to increment the number?", vbyesno) = vbyes then myNumber = myNumber + 1 else myNumber = myNumber end if end sub then your formula would be =B34*A1. the cell you designate as "target" could be hidden in an outside-the-print-area column, or even on another worksheet (fix range to reflect if on another worksheet). just an idea :) susan On Dec 13, 2:39 pm, jeannie v wrote: Thank you....is there another way to do this? My problem is that I have a different # of observation days a month that are being evaluated by analysis excluding weekends, holidays and the first day of the month...so the date doesn't help...I want it to say the Target for today is 5 times the Daily Target Rate, but tomorrow it will be 6 times the Daily Target Rate. Can you help? -- jeannie v "David Biddulph" wrote: =B34*(5+TODAY()-DATE(2007,12,13)) -- David Biddulph "jeannie v" wrote in message ... Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
While I'm waiting for your reply to David's latest post (don't know if one is
forthcoming), I'm pursuing my original idea. If you just need to copy that one formula from one worksheet (I'll call this Sheet 1) to 32 other worksheets (I'll call them Sheets 2 to 33), simply linking the relevant cell within Sheets 2 to 33 to the cell in Sheet 1 could do the trick. This means taking the time up front to establish the links in Sheets 2 to 33, but after you do that, you then just manually input into Sheet 1 and, voila, Sheets 2 to 33 would be updated automatically. The basic idea is if one cell needs to have the same content as another, one good approach is to input into the first and link the second to it. Hope I'm not confusing you. This helps me personally to think of the process. "jeannie v" wrote: Thank you for your thoughts...my problem is that once I can ge this thing to auto-change the number, I can create the macro to copy that formula to 32 additional worksheets automatically. Maybe I'm looking at this as less easy than it should be..do you think? Thanks for any thoughts, -- jeannie v "TRYING" wrote: I'm just reading this thread with interest. Realizing that I know nothing except what you have written, I would reiterate one part of CLR's post but state it in the form of a question: would you be better off with manual entry? If you have to open the worksheet each day that applies, what could be simpler than typing, say in cell B33 or another cell that is convenient, that variable number that you want--5 or 6 or 10 or 16--since you would presumably already have the formula =B34*B33 in some cell. I also recognize the saying "different folks, different strokes" might apply. It might be worth your while to explore ways to automate this. For some people, the challenge and the process are important. Sometimes though, the benefit might not be worth the effort. "jeannie v" wrote: Thank you CLR.......... for the info....If i want to build the macro for a firm date, could you tell me how I can do that? Thank you, -- jeannie v "CLR" wrote: Understand that the "TODAY" formula is volitile. True if you use it, tomorrow when you open the file it will be 1 more, but next week if you go back and open the old file it will be 7 more (ie: it will be for THAT day)............if you are saving the files each day, you would be better off with manual entry or a macro to set a firm date. Vaya con Dios, Chuck, CABGx3 "jeannie v" wrote: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Change # by 1 in a formula
And to save time in doing this you can group Sheets 2 to 32 together
so that you only have to enter the linking formula in one of the sheets and it will appear in all the other sheets. Something like: =Sheet1!B5 Remember to ungroup the sheets afterwards. Then you just enter the number into Sheet1 cell B5. Hope this helps. Pete On Dec 14, 1:15 am, TRYING wrote: While I'm waiting for your reply to David's latest post (don't know if one is forthcoming), I'm pursuing my original idea. If you just need to copy that one formula from one worksheet (I'll call this Sheet 1) to 32 other worksheets (I'll call them Sheets 2 to 33), simply linking the relevant cell within Sheets 2 to 33 to the cell in Sheet 1 could do the trick. This means taking the time up front to establish the links in Sheets 2 to 33, but after you do that, you then just manually input into Sheet 1 and, voila, Sheets 2 to 33 would be updated automatically. The basic idea is if one cell needs to have the same content as another, one good approach is to input into the first and link the second to it. Hope I'm not confusing you. This helps me personally to think of the process. "jeannie v" wrote: Thank you for your thoughts...my problem is that once I can ge this thing to auto-change the number, I can create the macro to copy that formula to 32 additional worksheets automatically. Maybe I'm looking at this as less easy than it should be..do you think? Thanks for any thoughts, -- jeannie v "TRYING" wrote: I'm just reading this thread with interest. Realizing that I know nothing except what you have written, I would reiterate one part of CLR's post but state it in the form of a question: would you be better off with manual entry? If you have to open the worksheet each day that applies, what could be simpler than typing, say in cell B33 or another cell that is convenient, that variable number that you want--5 or 6 or 10 or 16--since you would presumably already have the formula =B34*B33 in some cell. I also recognize the saying "different folks, different strokes" might apply. It might be worth your while to explore ways to automate this. For some people, the challenge and the process are important. Sometimes though, the benefit might not be worth the effort. "jeannie v" wrote: Thank you CLR.......... for the info....If i want to build the macro for a firm date, could you tell me how I can do that? Thank you, -- jeannie v "CLR" wrote: Understand that the "TODAY" formula is volitile. True if you use it, tomorrow when you open the file it will be 1 more, but next week if you go back and open the old file it will be 7 more (ie: it will be for THAT day)............if you are saving the files each day, you would be better off with manual entry or a macro to set a firm date. Vaya con Dios, Chuck, CABGx3 "jeannie v" wrote: Greeting Experts: I have a formula that changes by 1 each day...Example: = B34*5.....on the next day it would be =B34*6 and so on....How can I do this without manual entry? Thank you for your help, -- jeannie v- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
want to copy formula, only change one number in formula | Excel Worksheet Functions | |||
How does Data Validation change with a formula change? | Excel Worksheet Functions | |||
how do I change "fx" in formula bar to "=" | Setting up and Configuration of Excel | |||
formula to change 1 to a / and still add | Excel Worksheet Functions |