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?

VLookup would work with unique identifier.

Table 1

I'd. Box 1 Box 2 Box 3

Table 2

I'd. This would match the two ids and then return the values for box 1 above, put the same formula in and range but tell it to return the value for box 2 etc. A

So in essence you need to repeat the vlookup for the three cells of data you want, using the same range but specifying the different entries.

I think Spacecadet has the solution for you, in excel at least, it should be fairly straightforward 😀

You can do this with a combination of formulas. VLOOKUP, INDEX etc... more details on this link: basicexceltutorial.com/vlookup/how-to-return-multiple-values-vertically-using-vlookup-in-excel

I did something similar last week.
I had a large list of items with various columns of info.

I then created a search bar at the top (but you could create it in another workbook) then when I type something in the search bar (in your example SP111) then it pulls up and displays that item and all the data in its relevant columns.

If you can wait until tomorrow I can look at me spreadsheet at work and let you know the formulas (it took me a while to work it out!!)

You should definitely be able to do with pivot table and you could have them auto refresh. I use them for that type of thing a lot

BrieAndChill that would be amazing if you don't mind. I think my brain is fried now, I'm going to be dreaming about bloody excel and sreadsheets tonight!

I thibk you just want to be able t filter the original sheet. Is it for the purposes of printing an invoice?

First table contains your data. 2nd table uses a vlookup so if you change the ID, columns 2 and 3 return the data from the master table relative to that ID which would be your code. Formula should be visible in this one

Hi Stealth I need the costs on a different spreadsheet as I don't want anyone else anywhere near my master spreadsheet!

The individual sheets are for the managers of each job, so they can see the costs for their jobs so a filter wouldn't work

So what you type in A1 in sheet 2 matches A1 in sheet 1 and then returns the data relative to that entry but reading along.

Definitely pivot tables on different sheets, filtered by job!

Hide and password protect your master sheet so your managers can't touch it.

What do they need to do - just refer to them or do they need to edit

I think array formula might hold the answer. You can return multiple values to a unique identifier unlike standard vlookup which will just return the first value. It's a couple of years since I last used them so a bit rusty but Google should be able to help.

I've had a look at pivot tables, it's just not working

Stealth, they just need to be able to refer to them, but they just want the info, they won't want to do anything to get it

I'll have a look at Array formula Camelos

Array formulas don't return the info on separate lines.
Id write some simple vba to loop through all options and print a pdf version for that person

I think I'm well over my head, Never heard of a vba!

Pivot tables can do exactly what you want but are more of a learning curve. The screenshots I posted above show a vlookup will do what you need if you can use unique job codes for each entry.

Hi Blistory, I can't see any screenshots I'm afraid.

The trouble with vlookup is not every entry will have a unique code. Each job will have several costs on different rows

And I cannot get pivot tables to work. Not to say they won't, I just obviously don't have the skill!

I'm off to have nightmare about spreadsheets. I'll be back tomorrow to update or scream some more!

Night y'all and sweet dreams

Array formulas are even more advanced I'd say. Harder to get your head around. I'm working on them at the moment. If you want to pm me and send the sheet I'll see what I thibk?

Bugger, I posted on the app which explains the lack of screenshots. My posts won't make any sense then.

Saw this on my Google feed so signed up to offer my 10 pence worth. My job is literally working with Excel, VBA and SQL etc...

Anyway, sounds like you want to allow your data to be queried without risking it's security. I would create a separate macro enabled document linked to your master workbook, and the end user is only able to use the new separate document. In the macro workbook, set some VBA up to access the master (read only), filter the data and paste it into the macro document. Once done it will then close the master completely. Could potentially present the user with a userform to select their unique ID, job number etc. Happy to set something up for you if you want to send me some example data.

Use Power Query to connect a new worksheet to your master worksheet and then import whatever the relevant part is
Enable the power query add in.
Then data - new data source - excel workbook - file path of new the master data sheet.

If the master data sheet is updated with new costs, the worksheet will reflect this as soon as you hit refresh

Power query is brilliant, well worth learning it

VBA is definitely the way to go. If you are only familiar with excel, Access is probably not something you should use. Although it is probably the best for this sort of thing. I can sort something out or try to talk you through what you would need to do. It is what i do in my job so it wouldn't take much time. Maybe 1hr max.

This is some quick code I have knocked up haven't tested it, as I am on the bus. This should work within the same work book. I haven't got file paths so can't link workbooks. Two sheets one called data one called summary. To get to the code alt and F11. Copy this in and press F5.

Sub Get_Data()
Dim Job_Code As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Job_Code = Range("A1") ' Cell where the job code will be in the summary sheet

Sheets("Data").Select ' Name of master data sheet

Lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row ' the "A" relates to the column letter

Range("A1").AutoFilter Field:=1, Criteria1:=Job_Code ' Field:=1 is the column number the job code is in.

Range("A2:H" & Lastrow).Copy 'A is the starting column, 2 is the starting row, H is the last column in the table you want to copy

Sheets("Summary").Select 'Name of sheet you want to copy to

Range("A3").PasteSpecial xlPasteValues ' A3 is the cell you want to paste into

End Sub