• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

IF formula for page numbers

Hi Experts,
in cel P2 on 5 sheets in a row there is a page of page numbers. eg 1 of 1 or 1 of 3 etc.

I want to do an if formula for each of the P2 cells....
sht1    P2=  if sht2 C4="value" then "1 of 2" or if sht3 C4="value" then "1 of 3" etc etc

I think you get the idea. if sht3 C4 had a value but sht4 didnt then:
sht1 P2= "1 of 3"
sht2 P2= "2 of 3"
sht3 P2= "3 of 3"

Then once that is complete i would like to have a button with a macro to make a single PDF for with pages for sheets - sht1 & sht2 & sht3 with the PDF saved as the job name "sht1 C2" then a space and then the date in "sht1 J2"

Is this possible?? each of the sht's is the size of and A4 landscape.


Thanks

Rob.
Example-for-PDF-making.xlsx
0
RobJanine
Asked:
RobJanine
  • 3
  • 2
1 Solution
 
clampsCommented:
something like this?
="1 of " & IF('Sht 2 - Table 1'!C4="";1;IF('Sht 3 - Table 1'!C4="";2;IF('Sht 4 - Table 1'!C4="";3;IF('Sht 5 - Table 1'!C4="";4;5))))
0
 
clampsCommented:
and here your makro...
just make a button and connect it
and you have to set the pdf printer you use.
In case you don't know what it's called, start the macro recorder in excel and start to print manually, select the pdf printer of your choice and stop the macro recording process and look in the recorded macro for the printer of your choice :-)

Sub PrintToPDF()

Dim toPage As Integer

toPage = 1
If (ActiveWorkbook.Sheets("Sht 2 - Table 1").Cells(4, 3) <> "") Then
    toPage = toPage + 1
    If (ActiveWorkbook.Sheets("Sht 3 - Table 1").Cells(4, 3) <> "") Then
        toPage = toPage + 1
        If (ActiveWorkbook.Sheets("Sht 4 - Table 1").Cells(4, 3) <> "") Then
            toPage = toPage + 1
            If (ActiveWorkbook.Sheets("Sht 5 - Table 1").Cells(4, 3) <> "") Then
                toPage = toPage + 1
            End If
        End If
    End If
End If

Application.ActivePrinter = "PUT YOUR PDF PRINTER HERE!!!"
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=toPage, Copies:=1, Collate:=True

End Sub
0
 
RobJanineAuthor Commented:
will this work for not printing. I want it to make a pdf and save it in a folder...or email to some. Not print.

Cheers
Rob.
0
 
clampsCommented:
as far as I know excel can't create pdfs directly, but you can install a "virtual" pdf printer which will work just like any regular printer except no paper comes out but a safe to disc dialog will appear where you can "print" a pdf to...

a free pdf printer (you can use that in any application where you can print by the way) is this one for example:
http://freepdfxp.de/
It's german but it's running like a charm. But there are many free alternatives for this.
0
 
RobJanineAuthor Commented:
thankyou
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now