Anyone on here really good at excel?

[ThanosSavedMe]

That’s it really. I don’t know if what I wasn’t to do is even possible!

I want to set up a master spreadsheet listing all cost information for each job. I then need to be able to transfer this info onto a new workbook. I’m tearing my hair out trying to figure this out. Can anyone help me?

You can use a vlookup.

Create first workbook with job costs. Leaving it open, open second workbook. In the cell type = then click back into the relevant cell in workbook 1 and hit enter. You have created linked workbooks!

If you right click top left cell, choose copy. Open new page by clicking next free tab on the bottom of the workbook (rename tabs if req'd) then in new page right click in top left cell (or any free cell believe), choose paste and voila! Probably not what you mean 😆.

Thanks for so many quick responses. Love this site!

I tried vlookup, but it doesn't do what I need, it only picks up one row, I'll have several rows per job.

I was hoping that I would be able to but the job number in one cell and then a formula put in other cells would pick up everything from the master sheet..

As I said, not sure if its possible but I don't think I'm explaining myself very well!

Can you give us a clear example of what you want?

I’ll try.

So hopefully this photo shows an example of what I’ll have

I then want to create another spreadsheet where if I type in SP1111 it will pull over all those rows with that job ref

What are you trying to put in the new work book that's different to the first?

Can you use a pivot table?

Yes you can do it but we can't help with the formula without an example.

If you just want some sort of summary containing all the data associated with a given job, you probably want a pivot table. I hate the things, but i think they're designed to do what you're asking.

You could insert a pivot table and pull info out that way.

I’d use a pivot table too

Why does it need to be a new sheet? You can just filter by the first column if you only want to look at relevant rows.

Thanks again.

In the master list, it will contain all jobs, on the new spreadsheet, I only want info from one job

I'm not sure a pivot table would work, I might have a play around and see what I can come up with. The trouble is that won't automatically update

You'd be better with an Access database for what you're.wantimg
It should be easy enough to do for someone who has basic access knowledge (not me, I'm crap with access)

I've never heard of access - will see if I have it!

Can you not just refresh the pivot table each time you want it updating with new data?

A VLOOKUP should do it, as long as you have a unique identifier in each row to look up against. Say, for example your unique ID is in column A of the master spreadsheet, what you need to do is, in the new spreadsheet, in cell A1 type ‘=‘ then click on cell A1 of the master. Copy that formula all the way down column A of the new spreadsheet. Then in cell B1 of the new spreadsheet, do your vlookup to pick up data from whichever column of data you want in the master, using the data in column A as the reference to look up against. I’m not on my laptop right now otherwise I’d send you an example.

Almost anything is possible but it might be complicated!

Pivot table pointing to original data would work I think, but you might need a unique reference line number in source table to prevent the pivot table summing up.

I think I could do it with a macro so if you choose a project from a drop down box on spreadsheet 2, it copies over all the relevant lines from spreadsheet 1.

Someone with better database knowledge than me might be able to create a linked table

I think you can set it so pivot tables update when you open the sheet, but I'm not sure where that option is since the last version update.

Ah, just saw you only want the info from one job. So ignore the bit about copying the formula down column A. But the vlookup bit is still relevant. Are you thinking of doing something whereby someone puts in the job ref and the relevant info appears?

Easy solution: can you not just filter on the job ref column?

Techy solution: you need to create a dynamic named range of the original table and then make the pivot table off the back of that. To view all the transactions for one job, put the job code as the filter. By creating a dynamic named range, if you add rows to the master list it will include them in your table when you refresh.

Link below which explains what I mean:

www.cogniview.com/articles-resources/revealed-create-dynamic-pivot-tables-with-this-expert-tip

You could do it with advanced filter and a macro. But a spreadsheet probably isn't the best tool.
What are you trying to achieve?

A vlookup won't work because it only returns the first thing it matches - OP might have a job identifier with several rows of costs.

I'd do a pivot with the job identifier as the filter, and each time you open it refresh. You could have one worksheet containing a pivot table for each job.

I need to read your updates properly! Not sure excel can do it but a google sheets query definitely can because I do something similar with my data.

I'm so grateful for all these replies, thanks guys

"A VLOOKUP should do it, as long as you have a unique identifier in each row to look up against."

The trouble is several rows will have the same code as there are many costs for one job

A Macro might be what I need, though I've never done that before, not sure where to start.

Sparkler, that sounds like what I'm after

I've not been able to make a pivot table work yet, but I'll have a look at your link SpaceCadet