Need help with formula to bring information ($$) from multiple sheets to one main sheet

I am trying to figure out the best way to get information between sheets. I have formulas (very basic) to move from one sheet to another and then back, but I can't figure out the best way to do it. I attached my course fees spreadsheet.

I cannot use a macro (not allowed by IT) or else I would have done that but I need to figure out the best way to get the money amounts from another sheet back to my main one. I have a pivot table so I can see the overall totals since I can't resort my Home page. Sheet F-1360 has some information that shows what I need to do. I attempted to change things with Sheet F-Draw, but that doesn't seem to be prudent either.

I feel like I am close on this and just need to know what formula would actually work. I thought about VLOOKUP but it seems to me this is only for finding information and repeating it back in another place. I am not an expert by any means, but I am happy to learn and will do my best to understand what you guys think would work.

You CAN attach a workbook!

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Mmmm. I am going to have to be cruel to be kind here, so avert your eyes if your are at all sensitive!

The layout of the workbook needs completely rethinking. Data needs to be collated in normalised format. All data should be collated in one central place and then read from in other areas where you can have your more aesthetic reports. Having so many different worksheets is going to cause you grief in the long run. You are falling into what we sometimes refer to as 'rookie mistakes', and therefore you are about to recreate Frankenstein's monster in an Excel workbook.

I agree with Ali. Is there any compelling reason why you have a sheet for each "name"?

I combined a few sheets, (just copied to the right of the existing data each time), and the format of 1's I did, look to be pretty much identical - except for some names and amounts.

Perhaps consider just adding 2 extra columns, 1 for what you now have a sheet names, and the 2nd for the name (B3 etc), then just list all data 1 below the other. It can even be mixed up and swapped around, it wont matter. Also, wont need to keep searching for which sheet to use

Then on another sheet/s you could do extracts, summaries etc (far easier) based on what you want to see

Originally Posted by Agenduh

Not super sensitive. I know enough to make me dangerous, but not enough to actually get things done efficiently. So, you think I should have one sheet with the ordering information and what class they go to and then another sheet where it adds up the classes? I don't know how to do that without using a macro. Is there a formula I should be looking towards using?

Sorry, just saw this.

Yes, there are many formulas that could be used to do what you want. You tell us, and we can (probably) tell you.

Originally Posted by Agenduh

As you can see I have confused myself and am not sure where I should go with this.

Thats what we are here for, to add to the confusion (sorry, I jest lol). No, we can help reduce the confusion, and offer decades of experience (probably centuries, if you add up all the members experiences here) to you. There is unlikely to be any topic/problem, that at least 1 or 2 of our members have not already run into, and most of the seniors here, even if you have not seen it, can probably do it, given enough info.

What you don't want to be doing is adding things on satellite sheets that then need adding to other sheets: do it all centrally. Who needs access to this?

Trying to combine a few sheets for you, they all seemed to be names ART-and then a number. What does the name next to it represent (Barwick)?

Im thinking that for headings, you would need Class, "that" name, Vendor, Amt Spend, Balance, then you have value above each table?

OK, so if it's just you, then there is absolutely nothing stopping you from having one central point of data entry. One table will be enough, which can then be filtered, sliced and pivoted to produce the reports you need.

Think what constitutes a record. It could be this:

Worker ID - department - supervisor - payment type - payment amount - income type - income amount

Am I on the right tracks here in terms of the data that you need to analyse?

Yes - it's the KISS principle, isn't it? What more do you need? You can then extract the data in whatever format you need in order to send them their summaries.

Anything that makes this simpler and easier for you - is right. There may be other, easier (to us) ways to do this, and if we see them, we will advise. But, whats most important for me, is that YOU need to understand what has been done, and if that means using a slightly less efficient method or function, then so be it. YOU are the 1 that needs to maintain this, and if it is full of formulas you really dont understand, it could become a pain for you to change anything.

You already seem to know stuff like sumif and countif and PT's, but there is also sumifS and countifS that could be useful (they allow for more that 1 criteria to be tested)

Originally Posted by Agenduh

Act Spending Spent Left to spend
14 1260 202 1058

Spending =SUM(I2*90)
Spent =SUMIF(Sheet2!C2:C30, "104509", Sheet2!B2:B30)
Left to spend =SUM(J2-K2)

This seems to simplify my workbook and keeps everything to 2 sheets. Does it seem like this will work in the long run?

My suggestion is you set up your data extract sheet to show what you need to show (have several, if that works for you). No need yet to add formulas, just set it up how you think you need it. THEN start adding formulas to return what you want. If you hit a bump, we are here to help you further.

Again, the more you can do for yourself, the better off YOU will be, in the long run - you will learn more and be more confidant in your abilities to maintain AND add/change as needed.
(give a man a fish, and all that)

NO WAY did either of us say or even imply that you are stupid!!! However, I am so glad that you were open to suggestion - it's a case of not being able to see the wood for the trees sometimes.

If it appeared that I implied you were stupid, then please accept my sincere apologies - that was not intended.

This is just a case if "if you dont know, you dont know" We have seen this situation many times with new users of excel, and generally always offer the same advice. It is similar to someone asking - so what can I do in excel?

Once you start playing around, and trying to find better/other ways of how you have always done things, you will uncover all sorts of functions and tricks that we take for granted - and each 1 you find will be a "ahaaa" and/or "duh" moment for you

I already gave 1 piece of advice - YOU have to understand what you have done.
My other piece of advice is - dont be afraid to play and test things. The more you play around, the better you will become.

Look at some of the questions on here, and see what suggested formulas there are. Try and see how they work (for complex 1's, generally start inside and work outwards), see if you can follow along and then see if you can figure out a different way.

You will become expert if you do what Ford has suggested - all great advice. We are always happy to help - it's our main motivation for being here (by 'we', I mean those of us who offer solutions here).

What were the immortal words of Forrest? "stupid is as stupid does" Well you certainly didnt "does" here

We always try and push members here. the trick is in knowing how MUCH to push. We could show you some really complex stuff that would take you ages to try and understand - but that really wouldnt help you, and may even discourage you. Baby steps

Originally Posted by AliGW

You will become expert if you do what Ford has suggested - all great advice.

aaww shucks, thanks Ali, you made me blush

My pleasure.