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?

I'm an excel whizz at work and my immediate thought was sumproduct. They do most things a pivot will, and for something like this and someone who isn't too excel savvy and wants a quick fix they're perfect. They basically do what vlookup and hlookup do but for multiple rows / dimensions, very like a pivot table. In fact they're better than pivots for some things.

Very happy to try and implement Sun products f you pm me and are happy to send your spreadsheets to my email (annonymised if you prefer)

The VBA will probably do this, my concern would be how to mend/change this in the future if needed if you don't know VBA. I'd say sumproduct are easier to edit, at least they're formulae visible in the spreadsheet!

I'm sure arrays can do this too, I never got on with them myself, I think they were brought in when I could already do everything I needed to do with sumproduct... Old dog new tricks!

I'm confused. How could I use a sumproduct to match on multiple criteria? Or create a min(if statement? The only way I know how to do those is through array formulae. I'm actually doing them at the moment so if there is a way it would be helpful!

Pivot table on a separate sheet and password protect the master document.
I've just spent hours untangling a report document which just needed to pivot tables and graphs from them.
The person who had set it up was doing all sorts of copying, pasting, filtering etc.

I’m back!

Ok I’ve created a dummy spreadsheet which will hopefully paste here

What I’d them like to be able to do is input the job number on a new spreadsheet and get what’s on the next picture.

I would passowrd prote t and filter.

I can’t see how to do this with pivot tables although my pivot knowledge is very basic and I know nothing g about arrays, VBAs or macros

I can’t password protect and filter. That won’t do what I want it to do.

After looking at that I definitely think VBA. You could use 1 workbook but have a hidden sheet for the main data and save as read only. As long as the notes in the VBA are detailed enough editing should not be difficult.

Thanks, don't think I can do it then, I have no idea what VBA is and I need more than one workbook, each job will need its own spreadsheet for the managers to check. Thanks for all you help everyone, I really do appreciate it

What do they do when they check, do they edit? Are you updating it in the background? Are you expect g to run this process to generate the sheets once a week or are you expectijng them to update themselves as you update the main sheet?

No they don't edit, they just need all the costs. Yes I'll be updating the master list and I'll create the individual spreadsheets, they will just be able to look at it whenever they want to so they can check whether an invoice has been received and paid and what the final costs are when everything has been received.

What I'm doing at the moment is creating a new spreadsheet for each job and adding the details, this is quite time consuming (but I believe in the long run faster than what was being done before) and I thought that by just adding costs to a master template and then theses costs being split based on the job number it would be quicker. I thought it would be easy - I thought wrong!

Pivot table?

It's always easy if you know how. If you can post the spreadsheets I think all of us would have a go at getting it working.

JS82 - how would I post the spreadsheet, I posted a photo of a dummy one I made up at 9.24

You would need to swap email addresses with someone via pm

You can do this with a pivot table. The trick is to know how to get the data to display in a proper table format rather than the default pivot format. Screenshot attached shows the output. You would need to put Job No into the Filters section and then all other columns into the Rows section.

In the PivotTable Design area, make the following adjustments:

1. Subtotals > Do Not Show Subtotals
2. Grand Totals > Off For Rows and Columns
3. Report Layout > Show In Tabular Form

In the PivotTable Options Box, go to Display and uncheck 'Show expand/collapse buttons'

If you want to stop some of the text appearing in bold within the table, uncheck the Row Headers box under PivotTable Style Options on the Design menu.

There is also a way you can do this with formulas but the formulas are complex because you are looking to return more than 1 row from your query. I can send you the formula version in an excel file if you need it.

Hope this helps.

If your original data Range starts at A1 try the following.

Insert 2 rows before A so that your original data starts in column C
In column B Starting in row 2 type the following formula:
=IFERROR(SEARCH(Sheet2!$A$1,C2)+ROW()/100000,"")
Sheet2!$A$1 is the cell you want to enter your seach criteria in on your 2nd worksheet
C2 is the start of the column you want to search in
The copy this formula all the way down the B column (just drag it down)

In coloumn A starting at row 2 enter the following formula
=IFERROR(RANK(B2,$B$2:$B$50,1),"")

Change 50 to whatever the last row is (or if like me things will be constantly added to the workbook pick a really high number like 500!!

Now you can hide column A and B

On the 2nd Sheet:
Under your search cell so starting A2 type the numbers 1- 20 so tat 1 is in cell A2, 2 is in cell A3 etc:
Then in B2 type the following forumla:
=IFERROR(VLOOKUP(B2,sheet1!$A$2:$C$50,3,FALSE),"")

copy this formula down the column

then in the next column over (C) type the following formula:
=IFERROR(LOOKUP($B2,Sheet1!$A$2:$C$50,Sheet1!D$2:D$50),"")
Then copy by dragging this formula down the column to row 21, then also copy it by dragging along the columns by however many columns of data you need it to show eg probably to row H for your data.

I think this should work but mine is all on the same sheet so you need to make sure you link where i've but sheet1 as your sheet/workbook will have different names.
I'm rubbish at explaining but give it a try or someone else may be able to explain the above better! alernatively if you want to email me a copy of the spreadsheet I can have a fiddle.

Thanks DoubleW and BrieandChilli I will look at these tonight and let you know how I get on.

I can't believe the longest thread I've ever started as been about excel!!!

If you can share your sheet I can do this for you.

@StealthPolarBear I use sumproducts for most things.

I string them together (hope this makes sense on a phone) :

= sumproduct( (Big data array all the answers are in) (test 1, eg a1:a1000 = z$1001$) (test 2, eg b1:b1000 = 'gas') )

All the tests have to be the same dimension as the array, and the results of each test give a series of 1s and 0s.

The fact that all these series of 1s and 0s are multiple together means that only cells which satisfy all criteria (ie between 2001 and 2008, Gas, Blue, and Detached) get summed

You can multiply these by other things too. My example above is completely made up but notionally refers to gas bills in blue detached houses. You sum all the gas usage but if you stick another bracket inside the sunproducts you can multiply the usage by different unit prices too

Very difficult to explain without proper examples, but I will happily have a go if anyone wants to pm me asking for my email and send over some example data

If you want to use formulas BrieAndChilli has absolutely nailed it. Just check your cell references / ranges and you'll be good to go.

Forgot to say to copy the formula in column A all the way down