Excel Question #46

[Bornieo: Fully Loaded]

Its been a while but I "think" you can highlight the column and the right click to get to the box where you can make the changes or "Remove all hyperlinks".

[Alex]

Originally Posted by Kevy Baby

It took me 45 minutes of searching before I found it: how long would it have taken for you to provide the answer?

That one was an answer I'd have known off the top of my head, so as long as it took to quickly test to make sure I wasn't misremembering.

[Kevy Baby]

Originally Posted by Alex

That one was an answer I'd have known off the top of my head, so as long as it took to quickly test to make sure I wasn't misremembering.

See; you ARE brilliant!

[Kevy Baby]

Rather than starting another Excel Help thread...

I know that there are probably more elegant ways to achieve the end goal, but this project has sort of evolved to needing to ask this question.

I have a spreadsheet with several tabs. The first tab is the "master" where I can enter information needed for all of the other tabs. There are then individual tabs for each output. The output tabs reference back to the master: sometimes to information that is static to all output tabs and some information that is variable.

I actually have questions on two levels, though they are interconnected

Duplicating tabs
This is a 'one time' question. Currently, there are 13 output tabs and I need to increase to 27 (this will be my maximum needed for this project). Is there a way to have the new tabs reference the correct cell on the master tab for the variable data.

Simplifying: If

• Cell A1 in Output Tab 1 references Cell B3 on the master tab (=Master!B3)
• Cell A1 in Output Tab 2 references Cell B4 on the master tab (=Master!B4)
• Cell A1 in Output Tab 3 references Cell B5 on the master tab (=Master!B5)

How can I create Output Tab 4 to have Cell A1 reference Cell B6 on the master tab (=Master!B6)?

Master and Slaves

I am sure there is some name to what I am looking for and if it does exist, it may trump the first question above. And no, I am not inquiring about B & D.

Can I set up a system where if I need to make a global change to the output tabs, I can make it on one tab and all of the other tabs will automatically update?

For example, if I need the calculation in cell D14 to change from "=sum(A1 * A2)" to be "=sum(A1 / A2)", is there a way to set this up to change automatically on each output tab? Or is the only way to accomplish this beb to make the change in cell D14 in each Output tab (which I know I could accomplish via copy and paste, but there is always the danger or missing something).

OK Alex: prove your mettle!

[Betty]

I think asking about B&D would have been more fun.

I have no help for you on the excel question - but it did give me an idea for a spreadsheet of my own. Thanks.

[Ghoulish Delight]

I assume you want a solution that doesn't involve learning VBA?

[Alex]

I'm not sure I understand the first question. Do you mean you want it, when you create a new worksheet that it will automatically know that cell A1 should have a certain formula in it?

But yeah, I may just be fried (it has been a rough day) but I can't think of any way of doing either half that doesn't involve macros.

[Ghoulish Delight]

The quick and dirty way I can think of answering question 1 does involve a small amount of VBA knowledge, but not a lot. Depending on how large each sheet is, it could take some tedious initial setup.

But the basic steps are:
1) start recording a macro
2) copy a sheet to a new sheet
3) manually change everything you want to change
4) stop recording the macro
5) (this is the tricky part, and I'd need to do some learning myself to get it right) edit the macro.
5a) You should see that it's saying to copy specifically whichever sheet you recorded the macro from. Change it to whatever the syntax is that means "current sheet'.
5b) you'll see a line for each edit you made explicitly setting the cell to whatever you set it to. Change that to a formula that increments it from the same cell on the current sheet.

If you've got a mind to go that route, I can help you get steps 5a and 5b right later this evening.

[Kevy Baby]

First and foremost, thank you to GD and Alex for responding - I know I can always count on you to offer assistance on this.

And thank you to Betty for, well... thinking that way!

Originally Posted by Ghoulish Delight

I assume you want a solution that doesn't involve learning VBA?

Yes

However, I found a fairly painless solution to Question 1. I just made several copies of the last tab and did a replace in each tab from "14" (the row on the main tab that all of the data was in that pertained to that output tab) to "15" (or "16", etc.). Since most all of the info in the tabs is cell referencing and text, it was a safe thing to do.

Took about 15 minutes to create all the copy tabs, rename them all (there were 14 new tabs to create) and fix the cell references using search and replace.

Originally Posted by Alex

I'm not sure I understand the first question. Do you mean you want it, when you create a new worksheet that it will automatically know that cell A1 should have a certain formula in it?

Yes, that was the goal. But as noted above, I found an inelegant, albeit quick and painless solution.

Now, how about the second question? That one has me stumped.

[Drince88]

For the second part - highlight ALL the tabs you want to make a change in (use the shift or control key) - it'll say [Group] at the top next to the workbook name. And then on the first tab, change the equation in the cell, and it should change it in all of them. I just tested it and it worked.

The BIG thing on this, though - is to un-group the tabs before you go and do more work - otherwise you run the risk of making changes to every sheet, that you didn't really mean to (not that I PERSONALLY have ever done anything like that - nope, not me!)