You are here: Home > Message Board > General Talk > Excel
March 29 2024 7.42am

Excel

Previous Topic | Next Topic


 

View Squidgeedee's Profile Squidgeedee Flag Maidstone 29 Mar 20 7.40pm Send a Private Message to Squidgeedee Add Squidgeedee as a friend

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 Alert a moderator to this post Edit this post Quote this post in a reply
View the.universal's Profile the.universal 29 Mar 20 11.25pm Send a Private Message to the.universal Add the.universal as a friend

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 Alert a moderator to this post Edit this post Quote this post in a reply
View davenotamonkey's Profile davenotamonkey Flag 30 Mar 20 12.04am Send a Private Message to davenotamonkey Add davenotamonkey as a friend

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 Alert a moderator to this post Edit this post Quote this post in a reply
View Dan Theolmesdaleroad's Profile Dan Theolmesdaleroad Flag Up north 30 Mar 20 1.46am Send a Private Message to Dan Theolmesdaleroad Add Dan Theolmesdaleroad as a friend

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 Alert a moderator to this post Edit this post Quote this post in a reply
jeeagles Flag 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 Alert a moderator to this post Quote this post in a reply
View Rudi Hedman's Profile Rudi Hedman Flag Caterham 30 Mar 20 9.23am Send a Private Message to Rudi Hedman Add Rudi Hedman as a friend

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 Alert a moderator to this post Edit this post Quote this post in a reply
View Midlands Eagle's Profile Midlands Eagle Flag 30 Mar 20 9.52am Send a Private Message to Midlands Eagle Add Midlands Eagle as a friend

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 Alert a moderator to this post Edit this post Quote this post in a reply
View Squidgeedee's Profile Squidgeedee Flag Maidstone 30 Mar 20 11.20am Send a Private Message to Squidgeedee Add Squidgeedee as a friend

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 Alert a moderator to this post Edit this post Quote this post in a reply
View Apollofuzz's Profile Apollofuzz Flag On the edge of reason 30 Mar 20 12.07pm Send a Private Message to Apollofuzz Add Apollofuzz as a friend

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 Alert a moderator to this post Edit this post Quote this post in a reply
View PalazioVecchio's Profile PalazioVecchio Flag south pole 30 Mar 20 3.17pm Send a Private Message to PalazioVecchio Add PalazioVecchio as a friend

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

 


the 'Net-We-had' at the Etihad....again

Alert Alert a moderator to this post Edit this post Quote this post in a reply

 


Previous Topic | Next Topic

You are here: Home > Message Board > General Talk > Excel