How to simplify a file with too many tabs?

**LFG530** · 03-05-2016, 03:31 PM

Hi,

I happened to make a file (a real estate DCF if it helps you understand) were I spread data and results over multiple tabs and the file is too tough to navigate now (my director thinks so anyways). I'd like to be able to just copy and paste some data into a single tab but I can't since most tabs are dependent on each other trough formulas (Lookup, If, SUMIF and so on). Is there a way I dont know about to copy and paste without messing up all the dependent cells in the other tabs or needing to change every formula?

My first language isn't english, so don't hesitate if you need me to clarify.

Thank you,
LF

**Tsjallie** · 03-05-2016, 03:52 PM

If you move cells with formulas to other tabs Excel will adjust the references in the cell.
Just try it and see for yourself.

**Kevin#** · 03-05-2016, 03:58 PM

Try "CUT" and "paste"
(not "COPY" and "paste")

That will work for most things BUT you may have a few problems to sort.
I suggest you try it on a copy of your file

**newdoverman** · 03-05-2016, 04:07 PM

The formulae will probably be "shot to shreds" in the copy and paste and would most likely not update with new data.

It might not be as bad as you think but without seeing what you are dealing with, a definitive answer isn't possible.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachements to open the upload window.

View Pic

**LFG530** · 03-05-2016, 05:08 PM

Hi,

I could not upload my file since it exceeds the size of 1mb so here is a link to dowload it: http://speedy.sh/EmWj4/Backup-modele-essai.xlsx
... The file is in french, but I think it is still fairly easy to understand. I'd like to merge "Marchés"/"Bâtiment"/"Logements" and "Données"/"Profil" and "Occupation"/"Indexation"/"Loyers" and so on. All that without having to rework the formulas ideally.

It's a pretty straightforward DCF (Discounted Cash Flow analysis) as far as the output goes, but it's made to take into account monthly cash flow on a 30 years period. It takes into account, in separate tabs that I'd like to regroup, hypothesis on the markets, buildings and appartments and economic projections. Those hypothesis are all used to calculate the cash-flows (for example, the interest rate for each year in the economic tab is used to calculate mortgage payment). Then, the cash-flow tab is used, in another tab to calculate financial ratios, profitability, rate of return, etc.

Thank you,
LF

**newdoverman** · 03-05-2016, 05:11 PM

If you ZIP the file, you can upload 9.77MB

If you have groups of similar worksheets, you may be able to combine the worksheets within the groups to simplify the workbook.

It is pretty difficult to tell with just a description.

**LFG530** · 03-05-2016, 05:54 PM

I put up a link to it if you want to take a look... Here is another link to it (trough google drive): https://drive.google.com/folderview?...Ek&usp=sharing (will be quicker than zipping it).

**newdoverman** · 03-05-2016, 07:55 PM

I can't see a simplification that would help you. You might use hyperlinks to the the various tabs for easy navigation of the workbook.

The workbook isn't overly populated with worksheets but they are tied together by formulae that refer to other worksheets in a manner that isn't always obvious. You can't just copy and paste and have the workbook still function. You could however very carefully copy the worksheets one at a time into one worksheet and change the formulae as you go. This will be a nightmare to do but it can be done if you take your time and are extremely cautious.

I think that you are better off not disturbing what you have. Give the boss hyperlinks to the various sections of the workbooks so that he/she can navigate easily throughout the workbook.

**LFG530** · 03-07-2016, 02:08 AM

Could you give me an example of what you mean? I'm not familiar with the use of hyperlinks to navigate an excel file.

**newdoverman** · 03-07-2016, 10:08 AM

Here is your file with some hyperlinks inserted. On the Result worksheet there are hyperlinks at the top of the worksheet that when clicked on will take you to the worksheet name clicked. On a couple of worksheets I have inserted a hyperlink to return to the main worksheet.
This is done through the INSERT tab, Hyperlink.