Separating A and B from "A + B"

Hi guys,

This is for an income statement of several businesses, and I have cells that look basically like this: "business1.xslx field 1" + "business1.xslx field 1" + "business1.xslx field 1" etc. with each value corresponding to one of the businesses. A summation of values from different files.

I want to do the income statement per business, without having to redo everything. Is it possible to extract the value for each individual business for each cell from the summed up income statement? E.g. business 1 would just have "business1.xslx field XYZ" etc.

Each value in the summation is identifiable because the filename corresponds to the business, so if I can somehow search or match for business XYZ inside the formula, and extract that value, that would do the trick.. but that's just a concept. I am not an Excel expert.

Thanks so much

Hello
Please attach a copy of the spreadsheet, remember change any personel details to dummy details.
Regards
Peter

Thanks for the response, Peter.

I've attached dummy files that replicate the behavior. Attaching the originals would be far too complicated, but if you can make this example work, I'm sure I can apply it to the original.

Thanks a lot in advance.

One way to do it would be to create a blank dummy file. Then using a copy of your combined file Edit Links and change the link for Business 2 to the dummy file. This will leave just the data for Business 1. You can then repeat this in another copy of your combined file, changing the links for Business 1 to obtain the data for Business 2.

If your actual file has more than 2 businesses you will of course need to change all the links except one each time.

Yeah I do have more than 2 businesses. That would work, but if something a little more elegant is possible I would be glad to hear it.

Hi,

In your All Business's sheet @ cell ref's B3 and B5 you add B6 and B12 from each of the other 2 workbooks.

At Cell B11 and B13 why not just use cell ref's B6 and B12 from Business 1.

Then at Cell B18 and B20 use cell ref's B6 and B12 from Business 2.

No need to separate.

Try it

Cheers

TonyB