Excel Sorcerers!

[George]

1. Take Polaroid of whiteboard
2. Scan it and save to desktop
3. Insert picture in blank spreadsheet
4. Crack open beer, prop feet on desk, and return to web-surfing.

[Kchrpm]

You presume that they actually have numbers on the example they're giving me

[FaultyMario]

Just finished doing the monthly bills account.

I suppose teh sorcery has its limits...

[George]

Mighty Excel Champions, please lend me your ears.

I have:

An Excel workbook with between 400 and 500 worksheets. Each worksheet is just one page if printed.

"Cute PDF Writer" printer driver - this lets me save a document as a PDF file

A folder on my desktop

What I know I can do:

Select any worksheet and print/save it as a PDF file into the folder on my desktop

Select any number of worksheets, including all of them, and print/save them as one multi-page PDF file

What I would like to do, but don't know how:

Select all worksheets and have the computer to save them as 400+ individual PDF files - one PDF file per worksheet.

I realize I can select each worksheet one at a time and save them as individual PDFs, but I don't want to have to do that over 400 times.

I have looked around all my print options but can't see an easy way to do this. I am also familiar with creating macros in Excel, but don't know if a macro is what I need here.

Thanks for any help or wild-ass guesses!

[Kchrpm]

http://www.mrexcel.com/forum/excel-q...pdf-files.html

Or apparently you can open up a PDF in Acrobat and then use Extract Pages to separate them out.

[George]

http://www.mrexcel.com/forum/excel-q...pdf-files.html

The code in post #2 of that thread works perfectly. All I had to do was change the generic saving path of "C:\Users\JohnDoe\Documents\" to my own.

THANK YOU KCHRPM! Super-solid advice, as always.

Or apparently you can open up a PDF in Acrobat and then use Extract Pages to separate them out.

Probably so in Acrobat Pro or whatever the top-of-the-line version is called, but apparently I have only Acrobat Reader and I don't see Extract Pages as an option in any of the menus.

Metrics to add to your Excel advice-giving resume:

After I posted my question here, I told my manager I posted a question on the internet, but for now I didn't know how to do this. She assigned someone to go in and manually break out 100 of the 450-ish tabs. That took one hour, according to the unfortunate employee who had to suffer through that process.

I was able to press the Run button and watch the computer save 100 PDFs in perhaps ten or fifteen minutes. I went and fixed my lunch while it was running, so I don't know the exact time.

By doing the next 350 or so in the same way, we should save 3.5 hours of labor.

[Kchrpm]

Good

[George]

Personal boast:

I may have mentioned before that everyone in my department is losing his job because we are being outsourced to India. Well, a short time ago I was copied on an email containing a spreadsheet that needed nine columns of details added to each line, such as date, amount, identifying number, person who "owns" the item, current status, etc.

The email I was copied on was to the new department manager in India, who I met, trained, and was impressed by as a knowledgeable professional.

His reply to the email requesting the status of everything on the tiny little 142-line spreadsheet was "Too hard", followed by a frowny face smilie in Outlook. He said he would see if he could find someone to help.

26 minutes later, I sent the finished spreadsheet to the person who sent it to India - to that person only, who is someone I trust not to mention my name. It was nicely formatted and looking good, too.

I mentioned I'd be interested to see what India sends back, and when, if they ever do. I think she'll probably forward it to me so I can compare my skills with theirs. Who knows - I may be humbled, but I don't think so.

Okay, that's the end of shameless bragging (for now).

Question for the Erudite Expert of Excel, that Master of Macros, the Viceroy of Vlookups, and the Fearless Foreman of Function Formulas: is it possible to vlookup cell colors, as well as content? I have a couple spreadsheets to combine, and one of them has rows that are color-coded, as well as categorized.

If not, I'll just filter on each color and add the necessary content to the category cell. Example: if Green = Completed, I'll can add the text "Completed" after the current contents of each cell, with a formula such as =A1&" Completed", or maybe better yet, I could just add a column for Status.

However, I'm just curious and always looking to learn more about this awesome video game...er...boring spreadsheet program. Thanks as always.

[Kchrpm]

Nope, can't use VLOOKUP to directly lookup cell formatting. I usually do a setup where, rather than changing a color for a status directly, I set up conditional formatting so that the color changes automatically when I write in Completed/Yes/No/whatever. Depending on the goal, I'll even change the color of the text to match the background so it's not visible, and if I'm doing that I might just use numbers to represent the various states.

[George]

I've probably worn out my welcome in here, but if not, here's one that is making my brain hurt today.

I'm doing vlookups.

In the worksheet where my "answers" are, I have columns A through H.

A is the number I'm looking up, from the spreadsheet where my "questions" are, and then columns B through H contain the "answers" I want to move over my my "questions" spreadsheet.

So, my formula looks like this in the first row, and then I've copied this down through the last row.

Of course these go horizontally in Excel instead of vertically, as I had to list them here.

=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,2,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,3,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,4,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,5,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,6,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,7,FALSE)
=VLOOKUP($G2,'[2.10 comments.xlsx]Sheet1'!$A:$H,8,FALSE)

My problem is I have five values (and no blanks) in Column H, which is "Column 8" in the formula, but only one are showing up after the vlookup formulas have run.

I've manually compared to make sure what I saw at first glance is true: data in the columns "2" through "7" seem to be correct, but Column "8" (Column H) is only showing the one value for everything. I've retyped the formulas many times but still can't get around this error.

Here's a crude example with only two columns of vlookup results:

Let's say this is my list of "answers", and I'm trying to move the model name and color over to match a spreadsheet of "questions" that only has One, Two, Three, etc. listed vertically in Column A.

Correct information:

One - Camaro - Blue
Two - Corvette - Green
Three - Corvair - Red
Four - Chevette - Brown

VLOOKUP results:

One - Camaro - Blue
Two - Corvette - Blue
Three - Corvair - Blue
Four - Chevette - Blue

I'd love any suggestions, including to quit and go home.

Oy, I have a headache!