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...
RE: 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