MS Excel Question

[Graueradler]

How do I ( or can I) use relative referencing between sheets in a workbook. I'm doing a monthly report where some of the data (like fuel inventory and truck meter readings) in the current month report (beginning data) is the same as some of the data in the previous months report (month's end data). The previous months sheet is always immediately preceding this months sheet in the workbook. When I create a link by point and click, the reference becomes absolute. I need something that in cell (for example) C6 of the current sheet says "= Cell C10 in the sheet immediately ahead of this one".

 

[TMetzinger]

You put the name of the sheet, then an explanation point, then the cell.

Best to name each sheet, so the formula for May would say April!C6.

 

[mikea]

You put the name of the sheet, then an explanation point, then the cell.

Best to name each sheet, so the formula for May would say April!C6.

That's not what he wants. He wants (April!-1!)!C6 or however you would reference a sheet relatively.

 

[mikea]

I think I know how it cold be done.

Use a R1C1 reference for the sheet.
http://office.microsoft.com/en-us/excel/HP051983231033.aspx

Now I just need to know what that looks like..

FOUND IT!
http://www.microsoft.com/technet/scriptcenter/resources/officetips/mar05/tips0301.mspx

et objExcel = CreateObject("Excel.Application")
objExcel.Visible = TRUE
objExcel.Workbooks.Add

Set objWorksheet = objExcel.Worksheets(2)
objWorksheet.Activate

objWorksheet.Cells(1, 1).Value = "A"

To switch back to sheet 1, toss in these two lines of code:

Set objWorksheet = objExcel.Worksheets(1)
objWorksheet.Activate

So you can do:

integer iSheet=2

Set objWorksheet = objExcel.Worksheets(iSheet-1)
objWorksheet.Activate

set ThisWorkbook.Label.objWorksheet="TheCurrentSheet"

or something like that and then you could use `TheCurrentSheet`!C6

 

[Graueradler]

That's not what he wants. He wants (April!-1!)!C6 or however you would reference a sheet relatively.

Right Mike. I have a master blank spreadsheet that I copy, name for the present month and place behind the previous months sheet in the workbook. Presently, I either manually enter the carry over data or set the current month cell equal to the appropriate previous month cell by "= point & click". The point and click method creates an absolute reference to the sheet pointed to which next month, won't be the previous month any longer. It will be two months previous.

I thought that there would be an easy way to do it that I just wasn't able to find.

Skyhog suggested the following script:

Public Function SheetBefore( Optional sheetRange as Excel.Range) as Variant
Dim numberIdx as Integer
If sheetRange Is Nothing Then sheetRange = Application.Caller
numberIdx = sheetRange.Parent.Index
Set SheetBefore = Sheets(numberIdx-1).Range(sheetRange.Address)
End Function

and call it with =SheetBefore(C10)

 

[mikea]

Right Mike. I have a master blank spreadsheet that I copy, name for the present month and place behind the previous months sheet in the workbook. Presently, I either manually enter the carry over data or set the current month cell equal to the appropriate previous month cell by "= point & click". The point and click method creates an absolute reference to the sheet pointed to which next month, won't be the previous month any longer. It will be two months previous.

I thought that there would be an easy way to do it that I just wasn't able to find.

Skyhog suggested the following script:

Public Function SheetBefore( Optional sheetRange as Excel.Range) as Variant
Dim numberIdx as Integer
If sheetRange Is Nothing Then sheetRange = Application.Caller
numberIdx = sheetRange.Parent.Index
Set SheetBefore = Sheets(numberIdx-1).Range(sheetRange.Address)
End Function

and call it with =SheetBefore(C10)

That will do it, too. I don't have a copy of Excel in front of me. You'd have to hack at it a bit.

You can get the Worksheet by calling teh Workbook and asking for the iNdex which is what SkyHog is telling...and what I had.

The Perl mantra: There's more than one way to do it.

 

[EdFred]

*cough* database *cough*

 

[Greebo]

Ed is technically right. The type of tracking you're doing - recurring data structures over time - would be better suited in a database than in a spreadsheet.

However, unless you have access to a good database developer (even in MS Access doing it *right* is hard), stick with Excel.

 

[kevin47881]

Here's the way I have one of my Excel spreadsheets set up. Not sure it will work in your circumstance however.

=VLOOKUP($B$10,$S$18:$V$27,2)

 

[KennyFlys]

I have Logbook Pro. It's a great program and it did cost; about a hundred books. But, you can see they indeed put a lot of work into it.

You might look at its demo version if you haven't already. I haven't found anything they haven't thought of.

While it might be nice to have a cheaper version, I can certainly understand wanting a return on time invested in such a product. They have written it to accommodate a wide range of pilots.

The only suggestion I can make is if one does go to electronic log books, also invest in subscribing to an on-line storage to update each night as well as a local backup to an external drive. Periodically print hard copies and store them off-site.

Maybe Greg can attest to this. I've heard some of the veteran airline pilots have simply gone to just adding a single line of 600 hours to their log at the end of each year.

 

[AuntPeggy]

How do I ( or can I) use relative referencing between sheets in a workbook. I'm doing a monthly report where some of the data (like fuel inventory and truck meter readings) in the current month report (beginning data) is the same as some of the data in the previous months report (month's end data). The previous months sheet is always immediately preceding this months sheet in the workbook. When I create a link by point and click, the reference becomes absolute. I need something that in cell (for example) C6 of the current sheet says "= Cell C10 in the sheet immediately ahead of this one".

Here is how you find the names of all worksheets:

Sub GetWorksheets()
 num = Worksheets.Count()
 For indx = 1 To num
 
  sheetName = Worksheets(indx).Name
  [ ... ]
 Next
End Sub

Here is how you copy the last sheet into an identical new sheet:

Sub NewSheet(sName)
    Dim iLastSheet As Integer
 
    iLastSheet = Sheets().Count
    Sheets(iLastSheet).Copy After:=Sheets(iLastSheet)
    Sheets(iLastSheet + 1).Name = sName
End Sub

And to copy from one worksheet to another:

Sub CopyFields(cellFrom, cellTo)
 Dim iLastSheet As Integer
 Dim iPrevSheet As Integer
 
 Dim val As String
 
 iLastSheet = Sheets().Count
 iPrevSheet = iLastSheet - 1
 
 val = Sheets(iPrevSheet).Range(cellFrom).value
 Sheets(iLastSheet).Range(cellTo).value = val
 
End Sub

 

[AuntPeggy]

In reviewing, I think you want to write the formula, so:

Sub CopyFormula(cellFrom, cellTo)
 Dim iLastSheet As Integer
 Dim iPrevSheet As Integer
 Dim sValue As String
 Dim sName As String
 
 iLastSheet = Sheets().Count
 iPrevSheet = iLastSheet - 1
 sName = Sheets(iPrevSheet).Name
 
 Sheets(iLastSheet).Range(cellTo).value = "=" & sName & "!" & cellFrom
 
End Sub

 

[Graueradler]

I'm overwhelmed (literally) by the responses. Now I'm going to have to study up on code/macros/script to figure out how to use all this knowledge.

Thanks

 

[SkyHog]

I have Logbook Pro. It's a great program and it did cost; about a hundred books. But, you can see they indeed put a lot of work into it.

You might look at its demo version if you haven't already. I haven't found anything they haven't thought of.

While it might be nice to have a cheaper version, I can certainly understand wanting a return on time invested in such a product. They have written it to accommodate a wide range of pilots.

The only suggestion I can make is if one does go to electronic log books, also invest in subscribing to an on-line storage to update each night as well as a local backup to an external drive. Periodically print hard copies and store them off-site.

Maybe Greg can attest to this. I've heard some of the veteran airline pilots have simply gone to just adding a single line of 600 hours to their log at the end of each year.

I assume this was for me, in the wrong thread. If so, PilotFreeStuff will be about 9000 times more powerful than Logbook Pro when its done. In a few weeks, I'll make some sort of "press" release on it and you'll see what I mean.

Edit: If this was not for me, disregard.

 

[KennyFlys]

I assume this was for me, in the wrong thread. If so, PilotFreeStuff will be about 9000 times more powerful than Logbook Pro when its done. In a few weeks, I'll make some sort of "press" release on it and you'll see what I mean.

Edit: If this was not for me, disregard.

Oops! Yes, it was for you. Both of ya had the same thread title. Don't do that!

9000x, eh? Hmmm... I'll be looking to see what ya got!