using cell values as reference points for formulas

Casey · Apr 8th, 2002, 08:57 AM

Hello,
Ive got a question which Im sure has come up before, but I dont know how exactly to phrase it. Ive got a spreadsheet which has data for 100 samples(I work in a lab) listed vertically down the spreadsheet. Each sample has more than one entry and unfortunatly they dont have the same number of entries. What Im trying to do is. I know there are some spaces in the first column between samples. How would I go about sticking a formula in a cell that is down one row and four to the left of the last space? any suggestions?
Thanks
Casey

Derek · Apr 8th, 2002, 09:01 AM

Hi Casey

There is an offset formula, but there might be an easier way. Are you trying to use a formula to list all the samples with the same name (like sorting)?
Can you clarify what you are trying to achieve
regards
Derek

Casey · Apr 8th, 2002, 09:25 AM

Hi Derek,
Ive got about 100 samples and a certain reading is taken at various temperatures. Then the results are put into a textfile. The temps are then listed from top to bottom for each one of the samples. The only thing is occasionally more readings are taken than the normal (not something I can control) so I cant create a formula for one sample and expand it down to the others. So what I was thinking was to have a formula run in reference to the space between samples.
Thanks
Casey

Derek · Apr 8th, 2002, 09:26 AM

Hi again Casey

I'm off to bed but I'll leave you with this bit of code which might help. Change the formula details to fit your formula. Note that before you run the macro you have to select the data. If you have a fixed range
eg G4:G150 replace Selection with Range("G4:G150")

For Each cell In Selection
If cell.Value = "" Then
cell.Offset(1, -4).Formula = "=A1+B1"
End If
Next
End Sub

Hope this helps
regards
Derek

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 08:57 AM	#1
Casey Board Regular Join Date: Feb 2002 Posts: 82	Hello, Ive got a question which Im sure has come up before, but I dont know how exactly to phrase it. Ive got a spreadsheet which has data for 100 samples(I work in a lab) listed vertically down the spreadsheet. Each sample has more than one entry and unfortunatly they dont have the same number of entries. What Im trying to do is. I know there are some spaces in the first column between samples. How would I go about sticking a formula in a cell that is down one row and four to the left of the last space? any suggestions? Thanks Casey

Apr 8th, 2002, 09:01 AM	#2
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi Casey There is an offset formula, but there might be an easier way. Are you trying to use a formula to list all the samples with the same name (like sorting)? Can you clarify what you are trying to achieve regards Derek

Apr 8th, 2002, 09:25 AM	#3
Casey Board Regular Join Date: Feb 2002 Posts: 82	Hi Derek, Ive got about 100 samples and a certain reading is taken at various temperatures. Then the results are put into a textfile. The temps are then listed from top to bottom for each one of the samples. The only thing is occasionally more readings are taken than the normal (not something I can control) so I cant create a formula for one sample and expand it down to the others. So what I was thinking was to have a formula run in reference to the space between samples. Thanks Casey

Apr 8th, 2002, 09:26 AM	#4
Derek Board Regular Join Date: Feb 2002 Location: Perth Australia Posts: 1,567	Hi again Casey I'm off to bed but I'll leave you with this bit of code which might help. Change the formula details to fit your formula. Note that before you run the macro you have to select the data. If you have a fixed range eg G4:G150 replace Selection with Range("G4:G150") For Each cell In Selection If cell.Value = "" Then cell.Offset(1, -4).Formula = "=A1+B1" End If Next End Sub Hope this helps regards Derek