Excel spreadsheet Question

[Seaford Saint]

I have a question on spreadsheets

 

In my job i use on a daily basis the following excel sheets

 

a master spreadsheet - containing amongst other things info on power costs by model, maintenance costs and I reference the following 3 other spreadsheets

 

a power consumption by model spreadsheet

a maintenance spreadsheet containing maintenance costs per model

An excel spreadsheet of every asset in the company, the model number

 

Currently i add the asset name to the master spreadsheet and manually add info from the 3 other spreadsheets below.

 

So my question is this...would it be possible to enter into column a in my master spreadsheet an asset name which could trigger a formula to see if the asset is in the excel spreadsheet and add some data into my master spreadsheet into specific fields.

If this was possible and I am sure it is, once it has populated the model another formula could interrogate the other 2 spreadsheets and add data into the same line.

 

Currently i spend a lot of time doing this manually....occasionally making mistakes.

[Colinjb]

I believe this is possible, can't remember how though....

 

It's some form of formula function. Will have a look later.

[saintbletch]

Sounds like meat and drink for a spreadsheet Twentytwentyvision.

 

Take a look at VLookup and/or HLookup and as long as you've got a little spreadsheet nous that should set you in the right direction.

[bridge too far]

Can you set up your 'reporting' spreadsheets first and then, in the master sheet, put in = followed by clicking back into the reporting spreadsheet and doing copy and paste on the relevant cell? Then every time you amend a reporting spreadsheet, it will automatically change the info in the master spreadsheet.

 

It's been a few years since I've done spreadsheets, but I'm pretty sure that's what I used to do when I was picking up NHS data provided on an internal server (i.e. data another department had entered but that I could pick up on).

Edited 2 June, 2012 by bridge too far

[northernboy]

i link spreadsheets a work extensively.

'vlookup' and 'hlookup' are probably the best for begginers, or move onto 'match' function later.

for speed of use, always have the other spreadsheets open, and refresh the link function when you start

[Seaford Saint]

Thanks for the replies so far....BTF, would it be possible for you to elaborate please, I haven't understood what you mean...I have used vlookup Northernboy, but I was hoping that there might be a way of automating this.

 

I was reading a little on sql databases...would this be something i could utilise?

[bridge too far]

Thanks for the replies so far....BTF, would it be possible for you to elaborate please, I haven't understood what you mean...I have used vlookup Northernboy, but I was hoping that there might be a way of automating this.

 

I was reading a little on sql databases...would this be something i could utilise?

 

After reading other replies, I think my 'solution' wouldn't cover what you're trying to do. My answer covered linking in changeable data from other sources but it could be applied to link different sheets in the same document. But I think you're looking for something more sophisticated than that.

 

However, if you haven't got the answer you're looking for by Wednesday night, I'll ask my daughter on Thursday (if I remember). She is an IT project manager and does programming and very complicated spreadsheeting all the time.

[stu0x]

Thanks for the replies so far....BTF, would it be possible for you to elaborate please, I haven't understood what you mean...I have used vlookup Northernboy, but I was hoping that there might be a way of automating this.

 

I was reading a little on sql databases...would this be something i could utilise?

Vlookup would automate the process... It's difficult to explain in words, you're best off just working through the Help on vlookup. Or upload (a sample of) the spreadsheets so that someone can download them and show you.

 

You could equally do it with a database using queries, but it sounds like a relatively simple formula so its just as easily done in Excel.

[St Jim]

yes it's possible. As mentioned H & V lookups may work but for may be better to use Index & Match function (and the ISNA function to trigger an action once a value can be looked up). Also you may be able to incorporate a VB macro to assist which is great but I don't know what your excel skills are like. Without seeing what you are aiming to achieve I can't recommend the best functions.

[Ruffo]

note v or h lookups do not work if you have multiple entries for that asset on your data page (within the colomn or row). if you do have multiple then you need to use a sum function within index and or match.

[sfcuk fan]

whoooosh . . . . there it all goes .. . . right over the top of my head !

 

 

.