Change # by 1 in a formula

  • Thread starter Thread starter jeannie v
  • Start date Start date
J

jeannie v

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,
 
is not necessary to post the same question many times

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"jeannie v" escreveu:
 
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
 
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?
 
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
 
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,
 
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. :-(
 
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.
 
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,
 
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
 
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
 
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.
 
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
 
Back
Top