Attempting to build cell reference from index in other cell

[awmperry]

I'm working on a spreadsheet to output payslips for my father-in-law's company. (I'd hoped to build a whole payroll system in Access, but it's an... irregular system that doesn't lend itself very well to that.)

Anyway, the workbook consists of two worksheets; one called "Payslips" that will contain 80 copies of the payslip (I'm sure there's a way to do it automatically like some sort of mail merge, but that's rather beyond me.), each with a small payslip number in the top corner (H2 in the case of the first one).

The other worksheet is called "Staff", and has the staff information in columns for employee number, name, surname and so on.

The idea is that I want to pull data from those cells into cells in Payslips, and I'd originally tried to do it with this experiment:

=Staff!(CONCATENATE("A", H2))

Yeah... it didn't work. Then I tried another one I can't remember - ADDRESS, I think - and that didn't work either.

Now, the ideal thing would be if there is some ingenious mail mergey way of doing it, so if there is I'd love to hear about it.

More likely, I guess, is finding a way to just pull that index number into the cell reference. Can anyone help?

 

[Ralajer]

The function that I believe you are looking for is INDIRECT. It converts a text string to reference. So from your example

=INDIRECT("Staff!A"&H2)

where [H2] = 3 then this formula will return the value in A3 on the Staff sheet. Let me know if this is what you were after.

 

[awmperry]

That worked nicely, many thanks!

I don't suppose there's any way to have just one copy of the form there and populate it from another spreadsheet, maybe outputting a full set of auto-completed payslips to PDF or something? Rather like Mail Merge?

My basic problem is solved, but that would save me from having to go through and set up 80 forms manually...

 

[Ralajer]

I am not 100% on this but I believe that Excel doesn't have native mail merge functionality. It can be used as a source for information for Word's mail merge.

I stepped through Word's Mail Merge wizard real quick and it seems to be straight forward process; however, I haven't actually set one up for over a decade so I am no expert on this. With that caveat aside I would think it would be best to create Word template for the payslip then import the information from Excel using mail merge.

It would also be possible to write a macro to replicate the mail merge functionality solely in Excel.

Do some searching and you might find an existing solution or get a better idea of how to handle it. Start Here

Good luck.

 

[awmperry]

Yeah, I tried mail merging the data into a Word document last month. The problem with merging into Word from Excel is that they're not entirely compatible - every number getting imported gets stuck with extra characters. Which is fair enough, I suppose - after all, it's not like Word 2007 and Excel 2007 are part of the same office suite or anything...

Anyway, it seems to be working now. Thanks for the help, folks.