Excel Question?

Question

I use Excel to keep track of daily sales at a small store. I have 12 folders, one for each month. In each months folder I have 31 spreadsheets named 1-31. All 31 sheets are identical, and they list the cost, store price, profit, etc of each item in the store. If I want to change the price of an item, how can I do so on one sheet, and have it automatically change on all the others. Right now when I make a change on one sheet, I have to go through the process of using 'File', 'Save As', thirty times.

Answer 1

Here's what I would do. Instead of having 31 workbooks (spreadsheets), one for each day of the month, I would have just one workbook for a month, with separate worksheets for each day. For example, you could have a workbook named "January" with 31 sheets named "1" through "31"

Then to change a price on all 31 sheets at once, you would do the following: select sheet "1" by clicking on its tab. Now shift-click on the tab for sheet "31" and all 31 sheets will be selected, but sheet "1" is displayed. Then whatever you enter in a cell on sheet "1" will be entered in the same cell on all the other 30 sheets. To unselect all 31 sheets, just click on the tab for a sheet other than sheet "1"

Here's how to convert from your current system. Open your spreadsheet named "1" for a given month. Under the Edit menu select "Move or Copy Sheet..." and in the dialog box, select "(new book)" on the drop down menu. Check the box that says "Create a copy" and click OK. A new workbook will be created with Sheet1 being your old "1". Double-click on the tab for Sheet1, then rename it "1" by just typing "1". Now open your workbook named "2." Do the same "Move or Copy..." but this time tell it to move it to the end of Book1 (or whatever the name of the new workbook is). Keep doing this for all days in the month, then save your new file with, say, the name of the month.

Answer 2

Organizing in the manner you just described works if you are dealing with paper. To carry the metaphor further, for each new day you would have to photocopy the lastest update of the previous day. The whole thing fails if prices change and they include backdated ones. Any automation process is risky as they 'correct' those that should be left unchanged.

For excel, it is best you keep all your entries in a list. One month for one sheet. Headers on the top row, no empty rows, no subtotals between rows, and no grand totals. This way you can use Excel powerful filters to do the job of revising your figures quickly.

Once done, you employ another power excel tool, excel pivottables to extract out the information you want. You can arrange it by day, or by product lines, or by salesperson, whatever. I got a boss from hell who permutates and re-permutates. Excel is the only tool to keep me sane and keep him contented as a baby.

Email me a sample. I show you how this is done.

Answer 3

Change a data consolidation of multiple worksheets
After you have consolidated data from multiple worksheets, you may want to change the way that the data is consolidated. For example, you may want to add worksheets from new regional offices, delete worksheets from departments that no longer exist, or change formulas with 3-D references (3-D reference: A reference to a range that spans two or more worksheets in a workbook.).

What do you want to do?

--------------------------------------------------------------------------------

Change a consolidation made by position or category
Note You can change the consolidation only if you did not previously select the Create links to source data check box in the Consolidate dialog box. If the check box is selected, click Close, and then re-create the consolidation.


Click the upper-left cell in the consolidated data.
On the Data tab, in the Data Tools group, click Consolidate.
Do one or more of the following:
Add another source range to the consolidation

The new source range must have either data in the same positions (if you previously consolidated by position) or column labels that match those in the other ranges in the consolidation (if you previously consolidated by category) .

If the worksheet is in another workbook, click Browse to locate the file, and then click OK to close the Browse dialog box.
The file path is entered in the Reference box followed by an exclamation point.

Type the name that you gave the range, and then click Add.
Adjust the size or shape of a source range

Under All references, click the source range that you want to change.
In the Reference box, edit the selected reference.
Click Add.
Delete a source range from the consolidation

Under All references, click the source range that you want to delete.
Click Delete.
Make the consolidation update automatically

Important You can only select this check box if the worksheet is in another workbook. Once you select this check box, you won't be able to change which cells and ranges are included in the consolidation.

Select the Create links to source data check box.

To update the consolidation with the changes, click OK.
Top of Page

Change a consolidation by formula
You change a consolidation by formula by editing the formulas, such as changing the function or expression. Regarding cell references, you can do one of the following:

If the data to consolidate is in different cells on different worksheets

Add, change, or delete the cell references to other worksheets. For example, to add a reference to cell G3 in a Facilities worksheet that you have inserted following the Marketing worksheet, you would edit the formula as shown in the following example.

If the data to consolidate is in the same cells on different worksheets

To add another worksheet to the consolidation, move the sheet into the range that your formula refers to. For example, to add a reference to cell B3 in the Facilities worksheet, move the Facilities worksheet between the Sale and HR sheets as shown in the following example.

Because your formula contains a 3-D reference to a range of worksheet names, Sales:Marketing!B3, all worksheets in the range are included in the new calculation.


click on help and type the first sentence at the top of this reply to see an example and everything I just pasted.

Answer 4

well.. i can help you on this.. but i need to look at the sheets and understand it better from u to help you on this.. can u just message me at [e-mail] so that i can help you rite away..