External referencing problem from new user

[jers]

I have a spreadsheet I am trying to create for my mother, it is a relatively simple spreadsheet Date, Time, Guest Name and Vendor columns on the totals worksheet. I also have Monday - Friday sheets set up which have the same columns as the totals sheet.

Throughout the week entries will be made onto the daily worksheets showing the sales data (date, time, guest, sales rep, etc.). I'm trying to get my totals sheet to take every entry from each daily sheets (mon - fri) and place themselves into the totals page. I am not sure if I would be able to make just one formula that will look at that particular cell across each worksheet or if it would get far more complicated.

I was wanting to post the file I am using but I was unable to make an attachment. If anyone would have any ideas to help, I would appreciate it.

 

[Yard]

Hi and welcome,

You mean, for example, that in cell A1 in your master sheet you want to see the sum of the values of A1 in the sheets Monday thru Friday?

If so, make sure your sheets Monday thru Friday are located in one block (i.e. no other sheets "in between") and try something like:

=SUM(Monday:Friday!A1)

 

[jers]

I dont think this is gonna work and I probably not have explained this sufficiently enough.

Each column is kinda a different format (times, dates, names, currency, etc.) and I need to pull data for each day for each column. It will need to start on the first day (Sunday) and copy all the entries in all columns from the first sheet (Sunday) to the totals sheet, then move onto the next day (Monday), and so on. Sorry for the poor explanation, I hope this better explains what I'm trying to show.

Thanks for the help though, this is all a good learning experience even if its not the exactly what I was looking for.

 

[Yard]

OK, so you want to actually reflect the values from each sheet in your master sheet?

I thought you said that

I also have Monday - Friday sheets set up which have the same columns as the totals sheet

So...in your totals sheet, you have columns for Date, Time, Guest Name and Vendor, so how do you want to layout those values for each day?

Maybe a short example of your data layout will help you explain properly?

 

[jers]

This is the layout of the file. The layout will be the same as in the weekdays but I just want the totals sheet to basically get all the customers and their data from the weekday sheets. For example if 8 people were on the Sunday sheet I want it to plug in all 8 customers and all their corresponding data into the totals sheet, then move to the next sheet in line and repeat.

Here is a link to the spreadsheet if you would like to take a look at what's in the file right now.

http://rcpt.yousendit.com/660329731/47ca6080c10724729c9fda2f154dc627

 

[Yard]

Hmmm.

My thoughts, FWIW:

You could have a kind of dynamic table which, depending on the number of entries in each weekday sheet, would use INDEX to pick out the relevant values.

Or you could consider going for a more database-style workbook, with one table for all entries (i.e. the same columns you have now, but with each day's data appended to the table). This would, IMHO, be a far easier layout to work with for future reporting requirements.

Or you could consider using MS Access - seems a more suitable application to this kind of database?

 

[jers]

Im doing some research here on the INDEX() function and how to apply it. This is definitely going well above my head at this point but I'm going to keep at it for now, I need to find some way to get this done. I've never used Access before so I'm betting its going to be even more difficult to use, I'll check into that as well though.

Thanks for the ideas.

 

[Yard]

Good work. Something I've used in the past might help you out. It gets a bit cumbersome when you have >5 or so options, but it's easy-ish to understand:

=INDEX(CHOOSE(numberoftheweekday,Sun!A1:B100,Mon!A1:B100,Tue!A1:B100,Wed!A1:B100,Thu!A1:B100,Fri!A1:B100,Sat!A1:B100),rownumber,columnnumber)

You need 3 columns alongside your master table, one to cycle through the days of the week (1-7), one to cycle through the row numbers per weekday sheet, and one to cycle through the column numbers per weekday sheet. Then these 3 columns can feed the

INDEX(CHOOSE(...

function above and it will be dynamic.

The alternative to CHOOSE is to use INDIRECT with a named range per weekday sheet, like:

=INDEX(INDIRECT(weekdaynamedrange),rownumber,columnnumber)

I opt for CHOOSE since it is not volatile and, in a large database, is more efficient (or so I believe!).

HTH and post back if you get stuck

 

[jers]

I have found a good solution to my problem after much toil and research. I originally had 7 Daily sheets (Sunday-Saturday) to draw information from for my Totals and Payroll sheets. I finally came to the conclusion to combine all daily information into 1 sheet instead of 7 and recreating the Daily sheet as my Totals and Payroll sheet, all in one.

The reason this will work so well is because of Excel 2007's Dynamic tables. This removes most of the work from the data entry side and allows the user to sort by any header(s) and show exactly what they need shown at any time to run the daily reports. I really had only to plug a couple formulas into the sheet, make some format updates and she was good to go!

Once again, thanks for the ideas, I'm glad I got the opportunity to research more on this (especially learning a bit about INDEX/MATCH and SUMIF's, some handy tricks), easily improved my competency in using Excel a tad. The Dynamic tables are really pretty awesome, they stole the show when I presented it to my mother!

---CASE CLOSED---

 

[Yard]

Glad to help - and glad that you've taken the best practice route and created a database single table structure. You'll see further benefits when you want to make changes to the structure and expand your reporting options.