Dawn has attended:
No courses
Ecel
How do you reference a whole column of cells into a formula on a different worksheet? I have been able to create a formula referencing just one cell, but not the rest in the column.
Thanks :)
RE: Ecel
Hi Dawn
Thanks for getting in touch. Yes it is possible to reference an entire column, you can do it with "C:C" for example, or "E:J" to reference multiple columns.
=SUM("B:B") will add everything in column B.
What are you trying to do to the cells in the column - add them, find the average... ?
If you give me a little more detail on what outcome you're working towards I can be a bit more specific.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Ecel
Hi Gary,
What I have is a safety inventory log:
My first worksheet has the all my safety items with an original qty, present qty, qty for stock, and qty needed columns with formulas.
My second worksheet has a columns for the date, person taking the item, and items taken.
My goal is to write a formula in the first sheet that will reference items taken from the second sheet,and calculating them into the present qty cell in the first sheet.
I hope I am making sense. It seems like it should be fairly easy. I spoke with someone and they said it would be very complex, and it could break?????? Doesn't make sense to me.
I greatly appreciate your time and help :)
Dawn
RE: Ecel
Hi Dawn
Thanks for clarifying. I've had a go at constructing your worksheet based on your description, and attached a possible solution.
Column C of the inventory sheet contains the COUNTIF function. It looks like:
=COUNTIF(range, criteria)
First you tell it which cells to look at, then which cells to count. Any that it matches an "item code" for is counted. You didn't include a column like this in your question but it's the simplest way something like this would work.
As a bonus, you can have a look at the Stock Pick 2 sheet. Column C has a drop-down list to pick from to make life easier. You can see how this work by going to Data > Data Validation. The List feature allows you to pick from a list of options.
I hope this helps, let me know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
GF Safety Inventory Log.xlsxRE: Ecel
Hi Gary,
I had a look at your spreadsheet. You are quite the Excel Guru :)
The only thing I didn't mention was..... on the second sheet I have columns of all the items so that when the employee takes something, I could just input the qty in the cell of that item. Is there a way to still make it work like that? Or do you think I should make a new workbook?
Thanks again, and have a great weekend :)
Talk to you soon.
Dawn
RE: Ecel
Hi Dawn
Glad it's getting there!
To add in quantities would be fine, you would substitute COUNTIF for SUMIF, which has one extra bit of info:
=SUMIF(range, criteria, sum range)
Where after the criteria you specify the adjacent cells to be SUMmed (your new 'quantity taken' column).
Give it a try and let me know how you get on.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
RE: Ecel
Hi Gary,
I have tried to fiddle around with your formula to no avail :(
So, I thought I would attach mine so you can see how I put the formula in.
Unfortunately it won't let me paste it into this reply.
Is there another way you could take a look?
Best Regards,
Dawn Bunker
I copied the formula as you had it, and it just says "NAME"
RE: Ecel
Hi Dawn
Email your work in progress file to me at gary@stl-training.co.uk and I'll take a look for you.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
