MS Excel Help Required Please

[brucemillar]

Friends

Can you help please?

If in excel I have a block (columns & rows) of cells that are all pointing another sheet (In this instance a monthly sheet) Is there a way that I can easily change the address that these cells point at without changing any of the rest of the formula?

I want to change: ='Mar 2016'!G52 To: ='Apr 2016'!G52

Where I am only changing the date and NOT the actual cell address. I want to avoid editing every individual cell.

If I edit one cell then copy it? It retains the date but changes/increments the cell address.

 

[PXW]

A quick google suggest you can use find/replace, selecting formulas, then search for and replace Apr with May etc. If you are using Excel for Mac you apparently have to select to show formulas first.

Alternatively, can you copy the original source sheet, then simply rename the reference sheet?

 

[KillerHERTZ]

Please use the IT section Bruce! Moving.

 

[KillerHERTZ]

When you click on the formula, it should highlight the sheet/cell, can you not re select the one you want?

Otherwise manually type the new address in the field in the menu bar

 

[400ixl]

Find and Replace would be the easiest route.

You will be best to use and extended find. Something like:

Find 'Mar 2016'!
Replace 'Apr 2016'!

That should do what you need.

 

[Rayny]

Alternatively - Edit one cell to
='Apr 2016'!$G52
or ='Apr 2016'!G$52

Then when copying and pasting the dollar sign tells excel to keep either the column ($G52), or row (G$52), and does not increment it.

 

[brucemillar]

Bizarre!

I am using excel on a Mac.

When I use the "find" function y copying in the range I wish to find 'Mar 2016' it says it can't find it? I have tried re-typing and re-copying to no avail?

I think it cant find it inside a formula?

 

[KillerHERTZ]

Cant you just click in the formula and manually change it? - it should highlight it in green and you simply retype it, or point to the sheet in question.

 

[brucemillar]

Cant you just click in the formula and manually change it? - it should highlight it in green and you simply retype it, or point to the sheet in question.

Karl

There are huge amounts to change every month ;^(

What I have a is monthly data input sheet. Then we have graph tabs that point back to each month for their data. We need to have each month to provide an audit as well as month on month graphs.

 

[400ixl]

Long term I would create a cell with the month in it and then point to that as part of the equation. Then you just change the text in that cell each month and it will change the formula.

 

[st13phil]

In the PC version of Excel, in the Find/Replace dialog, there is a "Look in" pull-down that offers the option of find or replace within Formulas, Values or Comments. Is there not something similar in the Mac version?

 

[artyman]

If you are changing this every month, why not look at a solution where you enter the date in just one cell and the formulas are built using the data contained in that cell, that way you change only one cell each month.

 

[KillerHERTZ]

If you duplicate the sheet, does the formula not update automatically for the new sheet?

 

[zipdip]

Wish I could help Bruce,but I just read 12 posts and understood nothing,hope you find out how to do it.

 

[st13phil]

Long term I would create a cell with the month in it and then point to that as part of the equation. Then you just change the text in that cell each month and it will change the formula.

^ This is the most elegant solution.

Use the Data Validation option to create a cell with the valid values that can be selected, e.g. Jan 2016, Feb 2016, etc. and then reference it where you need the data by use of the INDIRECT() function.

Bruce: if you can't work out what I'm talking about above, PM me your email address and I'll send you an example Excel Workbook.

 

[brucemillar]

PM sent to Phil.

Thank you all four suggestions. Please keep them coming.

Sent from my iPhone using MBClub UK

 

[sgh]

Or you could have a cell where you enter the Month number, so 1 to 12 & then use a CHOOSE Formula

 

[markmifsud]

just use the $a$10 as the reference for A10, then have a workbook page named as the month, then you can just copy the formulas to new tabs and they will always reference the same cells

 

[brucemillar]

All

Thank you for all the suggestions to date. What I have is a classic case of starting something that does not scale.

Next Question:

If I have a graph in sheet "March" and I copy it into sheet "April" how can i stop the graph from selecting the data from "March" and start selecting it from "April"?

='Mar 2016'!$G$51

The cells are identical in every month. So the graph data ranges will never change but the data in the cells will change month on month.