corsasport.co.uk
  Not logged in [Login - Register]  
You Are Not Registered Or Not Logged In  

Corsa Sport » Message Board » Off Day » Excel Help Needed PLEASE


New Topic
New Poll 		  Subscribe | Add to Favourites

You are not logged in and may not post or reply to messages. Please log in or create a new account or mail us about fixing an existing one - register@corsasport.co.uk

There are also many more features available when you are logged in such as private messages, buddy list, location services, post search and more.


Author Excel Help Needed PLEASE
Craigos
Member
Registered: 2nd Aug 02
User status: Offline
   25th Apr 08 at 14:17   View User's Profile U2U Member Reply With Quote
I have a formular =(D11/100)*B5

when i drag from say D11 too D42 the formular copies down the say

=(D12/100)*B6
=(D13/100)*B7
=(D14/100)*B8


i want the formular to change but NOT to change B5 EG :

=(D12/100)*B5
=(D13/100)*B5
=(D14/100)*B5


Hope there are some clever people out there !!

Thanks in advance Craig
Matt L
Member
Registered: 17th Apr 06
User status: Offline
25th Apr 08 at 14:26   View User's Profile U2U Member Reply With Quote
this should work... write out a few the correct way ie
=(D12/100)*B5
=(D13/100)*B5
=(D14/100)*B5
then highlight all 3 and drag down that way (hopefully should work
Cosmo
Member
Registered: 29th Mar 01
Location: Im the real one!
User status: Offline
25th Apr 08 at 14:27   View User's Profile U2U Member Reply With Quote
put $B$5 IIRC.

Although has been a while since Ive used Excel for anything more than a simple formula
Craigos
Member
Registered: 2nd Aug 02
User status: Offline
25th Apr 08 at 14:28   View User's Profile U2U Member Reply With Quote
Already tried that but thanks for the positive input
Matt L
Member
Registered: 17th Apr 06
User status: Offline
25th Apr 08 at 14:28   View User's Profile U2U Member Reply With Quote
yeh just tried my method myself and it didnt work lol.
Fonz
Premium Member

Avatar

Registered: 12th May 06
Location: Newbury, Berks
User status: Online
25th Apr 08 at 14:29   View Garage View User's Profile U2U Member Reply With Quote
us ethe "$" sign before anything you dont want to change as the cell position does

ie

=(d12/100)*$B5

drag that and B5 will always be the reference cell in column "B" if you are darging out of B column use "$B$5"

hope that helps?
Matt L
Member
Registered: 17th Apr 06
User status: Offline
25th Apr 08 at 14:29   View User's Profile U2U Member Reply With Quote
if the value in b5 is going to be the same dont use the box and just put the value?

[Edited on 25-04-2008 by Matt L]
Fonz
Premium Member

Avatar

Registered: 12th May 06
Location: Newbury, Berks
User status: Online
25th Apr 08 at 14:29   View Garage View User's Profile U2U Member Reply With Quote
Cosmo you got it right
Tom
Member
Registered: 3rd Apr 02
Location: Wirral, Merseyside
User status: Offline
25th Apr 08 at 14:30   View User's Profile U2U Member Reply With Quote
I hate stuff like that with formula's I work with excel loads but scrape through it mostly, for the sake of typing it in 31 times i'd just do it manually

percentages are gonna be small what you doing?
pow
Premium Member

Avatar

Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
25th Apr 08 at 14:35   View Garage View User's Profile U2U Member Reply With Quote
Using the $ sign stops any changes when copying, eg $A$1

[Edited on 25-04-2008 by pow]
Robbo
Member
Registered: 6th Aug 02
Location: London
User status: Offline
25th Apr 08 at 14:35   View User's Profile U2U Member Reply With Quote
What Cosmo said... make E5 constant by putting $E$5 (or, in the interests of nationality lol) ŁEŁ5
Robbo
Member
Registered: 6th Aug 02
Location: London
User status: Offline
25th Apr 08 at 14:36   View User's Profile U2U Member Reply With Quote
quote:
Originally posted by Tom
for the sake of typing it in 31 times i'd just do it manually
Tom
Member
Registered: 3rd Apr 02
Location: Wirral, Merseyside
User status: Offline
25th Apr 08 at 14:37   View User's Profile U2U Member Reply With Quote
HOLD THE FRIGGING PHONE

CS HAS JUST HAD A POSITIVE IMPACT ON MY JOB
Cosmo
Member
Registered: 29th Mar 01
Location: Im the real one!
User status: Offline
25th Apr 08 at 14:37   View User's Profile U2U Member Reply With Quote
Tom
Member
Registered: 3rd Apr 02
Location: Wirral, Merseyside
User status: Offline
25th Apr 08 at 14:39   View User's Profile U2U Member Reply With Quote
quote:
Originally posted by Robbo
quote:
Originally posted by Tom
for the sake of typing it in 31 times i'd just do it manually


Got it down to a tea dude left hand hovering over laptop mouse ---> right hand over delete button ---> left hand tapping numbers ---> enter I have always been scared of the $ sign tbh
Robbo
Member
Registered: 6th Aug 02
Location: London
User status: Offline
25th Apr 08 at 14:44   View User's Profile U2U Member Reply With Quote
PMSL ya dingus

Step 2: SUMIF formulas
pow
Premium Member

Avatar

Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
25th Apr 08 at 14:45   View Garage View User's Profile U2U Member Reply With Quote
lolz
Craigos
Member
Registered: 2nd Aug 02
User status: Offline
25th Apr 08 at 14:52   View User's Profile U2U Member Reply With Quote
Cosmo. Thank you so much and i have to do it a few hundred times. im building some estimating software !

 
New Topic
New Poll

Corsa Sport » Message Board » Off Day » Excel Help Needed PLEASE 24 database queries in 0.0154049 seconds