Excel

This page is no longer updated, and is the old forum. For new topics visit the New HOL forum.

Excel

Previous Topic | Next Topic

Squidgeedee Maidstone 29 Mar 20 7.40pm
Hope everyone is staying safe Basically I’m that bored I am doing work outside of office hours. So I’ve decided to try and make a new estimating program on excel to help speed up the process. Problem is I’m not the best when it comes to formulas. What I am trying to create is a main sheet which has various questions to each of which have different drop down answers. The hope being that I can link this to a nett cost sheet to work out the material and overall project cost. I also want it to auto fill my labour sheet and purchase order but I don’t know how to make the pages talk to each other to transfer the relevant information. Any excel wizards out there?
Alert a moderator to this post

Squidgeedee

Maidstone 29 Mar 20 7.40pm

Hope everyone is staying safe

Basically I’m that bored I am doing work outside of office hours. So I’ve decided to try and make a new estimating program on excel to help speed up the process. Problem is I’m not the best when it comes to formulas.

What I am trying to create is a main sheet which has various questions to each of which have different drop down answers. The hope being that I can link this to a nett cost sheet to work out the material and overall project cost. I also want it to auto fill my labour sheet and purchase order but I don’t know how to make the pages talk to each other to transfer the relevant information.

Any excel wizards out there?

Alert a moderator to this post

the.universal 29 Mar 20 11.25pm
Originally posted by Squidgeedee Hope everyone is staying safe Basically I’m that bored I am doing work outside of office hours. So I’ve decided to try and make a new estimating program on excel to help speed up the process. Problem is I’m not the best when it comes to formulas. What I am trying to create is a main sheet which has various questions to each of which have different drop down answers. The hope being that I can link this to a nett cost sheet to work out the material and overall project cost. I also want it to auto fill my labour sheet and purchase order but I don’t know how to make the pages talk to each other to transfer the relevant information. Any excel wizards out there? Don’t think it will be too hard based on what you’ve said. As a start I’d suggest googling ‘data validation’ or ‘drop down’ in Excel. It’s not too difficult to do them. Vive le Roy!
Alert a moderator to this post

the.universal 29 Mar 20 11.25pm

Originally posted by Squidgeedee

Hope everyone is staying safe

Any excel wizards out there?

Don’t think it will be too hard based on what you’ve said. As a start I’d suggest googling ‘data validation’ or ‘drop down’ in Excel. It’s not too difficult to do them.

Vive le Roy!

Alert a moderator to this post

davenotamonkey 30 Mar 20 12.04am
Another tip is to make it in google sheets first, then set the sharing to "anyone with the link can edit". You can add in dummy / fake data if it's personal / sensitive. Then, if you're struggling setting it up, one of us can access the sheet and work out what's going on. Once you're happy with it, you can download it as a .xls file, and use it offline in Excel.
Alert a moderator to this post

davenotamonkey

30 Mar 20 12.04am

Send a Private Message to davenotamonkey

Another tip is to make it in google sheets first, then set the sharing to "anyone with the link can edit". You can add in dummy / fake data if it's personal / sensitive.

Then, if you're struggling setting it up, one of us can access the sheet and work out what's going on.

Once you're happy with it, you can download it as a .xls file, and use it offline in Excel.

Alert a moderator to this post

Dan Theolmesdaleroad Up north 30 Mar 20 1.46am
Originally posted by Squidgeedee Hope everyone is staying safe Basically I’m that bored I am doing work outside of office hours. So I’ve decided to try and make a new estimating program on excel to help speed up the process. Problem is I’m not the best when it comes to formulas. What I am trying to create is a main sheet which has various questions to each of which have different drop down answers. The hope being that I can link this to a nett cost sheet to work out the material and overall project cost. I also want it to auto fill my labour sheet and purchase order but I don’t know how to make the pages talk to each other to transfer the relevant information. Any excel wizards out there? I’m certainly no expert, but love playing around with Excel and have designed several spreadsheets over the years, including an Oscars prediction game that I do with a few people every year. A bit sad I know, but it makes the Oscars a lot more fun! Anyway, with regard to auto-filling cells across other sheets with relevant data, the formula you will need to enter (in the cell you wish to auto-fill) should be as follows: ='Exact name of sheet'!E23 That is to say, enter the equals symbol, then an opening inverted comma, followed by the exact name of the sheet containing the data you want to copy, before adding a closed inverted comma. That should then be followed immediately by an exclamation mark and the cell reference containing the data you want to copy (e.g. cell E23). The chosen data will then appear in the cell containing the formula. I’m sure that probably doesn’t cover everything you need to know, but I hope it makes sense and you find it of some help!
Alert a moderator to this post

Dan Theolmesdaleroad

Up north 30 Mar 20 1.46am

Send a Private Message to Dan Theolmesdaleroad

Originally posted by Squidgeedee

Hope everyone is staying safe

Any excel wizards out there?

I’m certainly no expert, but love playing around with Excel and have designed several spreadsheets over the years, including an Oscars prediction game that I do with a few people every year. A bit sad I know, but it makes the Oscars a lot more fun!

Anyway, with regard to auto-filling cells across other sheets with relevant data, the formula you will need to enter (in the cell you wish to auto-fill) should be as follows:

='Exact name of sheet'!E23

That is to say, enter the equals symbol, then an opening inverted comma, followed by the exact name of the sheet containing the data you want to copy, before adding a closed inverted comma. That should then be followed immediately by an exclamation mark and the cell reference containing the data you want to copy (e.g. cell E23). The chosen data will then appear in the cell containing the formula.

I’m sure that probably doesn’t cover everything you need to know, but I hope it makes sense and you find it of some help!

Alert a moderator to this post

jeeagles 30 Mar 20 9.04am
Originally posted by Squidgeedee Hope everyone is staying safe Basically I’m that bored I am doing work outside of office hours. So I’ve decided to try and make a new estimating program on excel to help speed up the process. Problem is I’m not the best when it comes to formulas. What I am trying to create is a main sheet which has various questions to each of which have different drop down answers. The hope being that I can link this to a nett cost sheet to work out the material and overall project cost. I also want it to auto fill my labour sheet and purchase order but I don’t know how to make the pages talk to each other to transfer the relevant information. Any excel wizards out there? Depends on how you put your price together, but if it's done with a BoQ plus labour you need to put together an itemised database of all your rates on one sheet. Then you can have a second sheet that you fill in an item code and qty and then it will work out the cost for you using vlookups. I'd probably use a 3rd sheet to put together subtotals and a total (probably using sumifs). It takes some time to get right, and you'll generally want a second method of checking it to make sure there are no glitches.
Alert a moderator to this post

jeeagles

30 Mar 20 9.04am

Originally posted by Squidgeedee

Hope everyone is staying safe

Any excel wizards out there?

Depends on how you put your price together, but if it's done with a BoQ plus labour you need to put together an itemised database of all your rates on one sheet. Then you can have a second sheet that you fill in an item code and qty and then it will work out the cost for you using vlookups. I'd probably use a 3rd sheet to put together subtotals and a total (probably using sumifs).

It takes some time to get right, and you'll generally want a second method of checking it to make sure there are no glitches.

Alert a moderator to this post

Rudi Hedman Caterham 30 Mar 20 9.23am
I used to be pretty good on excel but moved away from it some time ago. I can still knock up a spreadsheet and can work out how to do new things with the help function, even if it’s difficult getting used to newer interfaces. But too much excel use is damaging for the soul and the personality and brings the career ceiling closer. Very very useful skill for your own business or domestic finances if you have to however. COYP
Alert a moderator to this post

Rudi Hedman

Caterham 30 Mar 20 9.23am

I used to be pretty good on excel but moved away from it some time ago. I can still knock up a spreadsheet and can work out how to do new things with the help function, even if it’s difficult getting used to newer interfaces. But too much excel use is damaging for the soul and the personality and brings the career ceiling closer. Very very useful skill for your own business or domestic finances if you have to however.

COYP

Alert a moderator to this post

Midlands Eagle 30 Mar 20 9.52am
Originally posted by Rudi Hedman I used to be pretty good on excel but moved away from it some time ago. I can still knock up a spreadsheet and can work out how to do new things with the help function, even if it’s difficult getting used to newer interfaces. But too much excel use is damaging for the soul and the personality and brings the career ceiling closer. Very very useful skill for your own business or domestic finances if you have to however. All the accounting records for my business are maintained by myself on Excel and I get round the difficulty getting used to new interfaces by not doing so as I'm still happily using Excel 2003
Alert a moderator to this post

Midlands Eagle

30 Mar 20 9.52am

Send a Private Message to Midlands Eagle

Originally posted by Rudi Hedman

I used to be pretty good on excel but moved away from it some time ago. I can still knock up a spreadsheet and can work out how to do new things with the help function, even if it’s difficult getting used to newer interfaces. But too much excel use is damaging for the soul and the personality and brings the career ceiling closer. Very very useful skill for your own business or domestic finances if you have to however.

All the accounting records for my business are maintained by myself on Excel and I get round the difficulty getting used to new interfaces by not doing so as I'm still happily using Excel 2003

Alert a moderator to this post

Squidgeedee Maidstone 30 Mar 20 11.20am
I’ve got I roughly set up as to what I need it’s just the interfacing of the pages. For example I have the following in 1 book Project sheet - to enter the roof details plus various drop down options to hopefully narrow the nett cost sheet to the specific materials needed. Nett cost sheet- for work our materials needed and work out waste and labour. Purchase order- to be sent to the suppliers for ordering the relevant material. Labour sheet - for the guys carrying out the work so that they know there labour value and what materials have been delivered/what they need to pick up Total sheet - showing the allocation of money to materials waste labour profit ( this is more for my boss for an accurate breakdown) My struggle is getting them talking to each other
Alert a moderator to this post

Squidgeedee

Maidstone 30 Mar 20 11.20am

I’ve got I roughly set up as to what I need it’s just the interfacing of the pages.

For example I have the following in 1 book

Project sheet - to enter the roof details plus various drop down options to hopefully narrow the nett cost sheet to the specific materials needed.

Nett cost sheet- for work our materials needed and work out waste and labour.

Purchase order- to be sent to the suppliers for ordering the relevant material.

Labour sheet - for the guys carrying out the work so that they know there labour value and what materials have been delivered/what they need to pick up

Total sheet - showing the allocation of money to materials waste labour profit ( this is more for my boss for an accurate breakdown)

My struggle is getting them talking to each other

Alert a moderator to this post

Apollofuzz On the edge of reason 30 Mar 20 12.07pm
I am not am expert and get other people to do the difficult bits on my spreadsheets But I know what you are trying to do. Firstly Have you looked at using Pivot tables (Basic Database info in Excel) this when data can be shared across sheets or I Use Vlook ups from one sheet to another. Sometimes I use Vlook ups to create a central sheet with all the data and then reference the cells across the workbook from that sheet. There are great online tutorials on these. Not much help but its the best I have. I ride a GS scooter with my hair cut neat I wear my war time coat in the wind and sleet.
Alert a moderator to this post

Apollofuzz

On the edge of reason 30 Mar 20 12.07pm

I am not am expert and get other people to do the difficult bits on my spreadsheets But I know what you are trying to do. Firstly Have you looked at using Pivot tables (Basic Database info in Excel) this when data can be shared across sheets or I Use Vlook ups from one sheet to another. Sometimes I use Vlook ups to create a central sheet with all the data and then reference the cells across the workbook from that sheet. There are great online tutorials on these. Not much help but its the best I have.

I ride a GS scooter with my hair cut neat
I wear my war time coat in the wind and sleet.

Alert a moderator to this post

PalazioVecchio south pole 30 Mar 20 3.17pm
i thought this thread was about the Excel Exhibition Centre. Some scary numbers & sheets going on there. Kayla did Anfield & Old Trafford
Alert a moderator to this post

PalazioVecchio

south pole 30 Mar 20 3.17pm

Send a Private Message to PalazioVecchio

i thought this thread was about the Excel Exhibition Centre. Some scary numbers & sheets going on there.

Kayla did Anfield & Old Trafford

Alert a moderator to this post

Previous Topic | Next Topic

Crystal Palace FC Supporters' Website - The Holmesdale Online

Excel

Site Map

New users

Existing users

Sponsors