Excel formatting question

General Questions
#1

I’ve recently combined three spreadsheets into a five-spreadsheet file. Three of the spreadsheets are the three original spreadsheets, the last is one that I made to keep stuff that was at the bottom of those, and which I wanted to move so that I could sort the other pages easier, and the first page is a reformatted combination of the original three spreadsheets.

Since I don’t want to enter dates in one or two places twice, sheet 1 draws the dates from the fields in the other sheets. (i.e., I type ‘=’ and click on the appropriate cell on the other sheet.) The dates on all pages are formatted as mm/dd/yy. On the original sheets the cells are blank when there is no date. On the first sheet, the one that draws the dates from the other ones, blank dates show up as 01/00/00.

How do I make the blank dates appear blank on sheet 1?

#2

instead of
= sheetname!c1
use
= if(sheetname!c1=0,"",sheetname!c1)

where sheetname = the name of the reference page and c1 is the reference cell

#3

Explanation

that uses an “if” statement to cause a blank to appear on page one if the reference cell on one of the other pages is blank.

The term “” in the if statement is interpreted by excel as a blank.

#4

My imaginary friends come through again! :slight_smile:

I changed one blank one, one that has a date, and one that was giving me a #REF! for some reason. 149 to go…

#5

Another question: If I sort the source pages, will the formulas on sheet one automatically change so that the formulas will be correct? What if I insert rows?

#6

OK, I’ve run into a problem re: my last question.

My records are identified by a unique number. Records are sorted by that number. One of the records is identified by two numbers, and column A says ‘123456 + 987654’ (made up for this post, not that it matters if I posted the actual numbers). Since the field is not numeric, it gets sorted to the bottom. So after I changed it to ‘123456’ and put ‘987654’ into the Name field, I sorted again. I also made the change on sheet 2 and sorted that.

I had just finished typing '=[sheet!cell] for all 152 records. After sorting, all of the formulas were off by one for the records that draw from sheet 2. e.g., ‘=sheet2!F91’ became ‘=sheet2!F92’. This is a problem because lines do get inserted from time to time. If I have to re-type the formulas each time, it defeats the purpose of using formulas. It would be easier if I just type the dates into two spreadsheets each time, rather than type the formulas to retrieve a date from one cell on another sheet and the conditional formula to retrieve the other date.

Is there a way to make the formulas dynamic, so that they draw the information from the right places when rows are inserted or deleted or when the sheets are re-sorted?

#7

Do you mean like you always want your formula to point to a spot like “c52” on the second page even if lines are inserted above that spot?

You can force that to happen using an “indirect” function.
Unfortunately the equation gets a little hairier
=IF(INDIRECT(“Sheet2!C52”)=0,"",INDIRECT(“Sheet2!C52”))
The example above will always get it’s information from cell C52 on Sheet2 no
matter what you insert or delete or sort on sheet2.

The indirect statement interprets text not** location **so Excel’s method of automatically remapping cell linkage doesn’t “happen” to text.

#8

Please note that the indirect function can cause some problems.
Many people get used to Excel’s automatic mapping and linking and forget or incorrectly assume what the results will be when working with indirect statements.

The key thing is to never forget that the indirect forces Excel to interpret text not locations.

It’s not a bad idea to check the results whenever you perform modifications to the source pages.

#9

Let’s say I have something in C52. The value in that cell is being used on another page. OK, now I insert a row. The new row is 52. The former row 52 is now row 53. So now I want the value on the other sheet to come from C53 instead of C52.

To use my specific example, E153 says ‘=IF(Easytrieves!H93=0,"",Easytrieves!H93)’. But I really want row 153 to be row 32. So I move it there. No worries, since sheet 2 hasn’t changed. Now I go to sheet 2. The corresponding row that equates to row 153 is row 93, as can be seen by the formula. Only I’d rather row 93 be row 20. If I move sheet 2, row 93 to row 20, Row 153 is still looking for row 93 and the value on sheet 1 is incorrect.

So is there a way to tell Excel ‘I want you to look here for the value. If I move the row (cut & insert) or sort on a different field, I want to to look where the record is instead of continuing to look at a fixed position.’?

#10

If you insert rows or cut and paste, all of your links will follow the cell.

And you shouldn’t be typing in all of your formulas, you need to drag and autofill.

Play with Excel a little, do simple things and watch how the program responds - it’s pretty user friendly if you let it be. But if you start trying to do trickier things without understanding how it works, it will beat the crap out of you.

eta: for example, if I have a cell that says =A2 and I cut and paste A2 into A3, my formula will automatically change to =A3. (The formula will NOT change if I use copy and paste, however).

aeta: if you need to change a bunch of formulas at once, trying using find and replace - super handy. (first find and replace all of the equal signs into && to break the formulas, then use find and replace to change the formulas (add some if statements, whatever) then use F&R to put the = back in place of the &&)

#11

Whoaaaaaaa. Was the data not in a single column? Why didn’t you copy the formulas down?

#12

The dates I’m pulling are from three different spreadsheets.

#13

You could use the replace function to upgrade the formulas you want to change and do it in bulk.

#14

Going back to my specific example:

When I cut and insert it to where I want it, it pulls the dates from the right fields and the other rows also pull the dates from the right fields.

When I cut and insert the source row on sheet 2 to where I want it to be and sort sheet 2, all of the rows that pull dates from that sheet are now incorrect…

Example:
[ul][li]Sheet 1: I modify A153 and re-sort the page. Row 153 is now row 33.[/li][li]The formula is ‘=IF(Easytrieves!H93=0,"",Easytrieves!H93)’[/li][li]Sheet 2: I modify A93 and re-sort the page. Row 93 is now row 20.[/li][li]Sheet 1: The formula is still ‘=IF(Easytrieves!H93=0,"",Easytrieves!H93)’[/li][li]The new row 153 has the formula ‘=IF(Easytrieves!H92=0,"",Easytrieves!H92)’ But the new corresponding record on sheet 2 is now 93, so everything was shifted one row in the sort of sheet 2.[/ul][/li]
.

#15

So let’s say I type the formula in the top row. I copy it all the way down. All of the source pages will be the same, and the source cells will be incremented by one. But I’m drawing from three different pages. I’d have to go back and type all of the source pages and all of the source cells anyway. It’s easier just to type them all to begin with.

#16

The replace function is great, you can seltect a row / column / section of data and it will ‘replace all’ only within that selection.

And another handy thing is that when you click on the bottom right corner of the cell and drag to copy that cell down or across, it will automatically adjust the formulas (adding a number down a23,a24,a25 etc or a letter across a23,b23,c23 etc.

You can alter that by holding down CTRL when you ‘drag’ the formulas, that copies the same data to each cell. So when you need to enter the same reference number or date on mutple lines:
Click & drag= 01/02/03, 01/03/03, 01/04/03
CTRL+click & drag = 01/02/03, 01/02/03, 01/02/03

Sorry, this was meant to follow Magiver’s post

#17

maggenpye: I do those all the time.

In this case, that won’t work. One cell might need to pull from sheet2!F8 and the next cell down might need to pull the information from sheet4!H27.

#18

Ah well, it might be useful to someone else reading the thread.

Just to clarify (without having a clue of the solution), the problem is the sort function changing the source cellname incrementally?

#19

The problem is that when I sort a source spreadsheet (sheet 2, sheet 3, or sheet 4), the target sheet (sheet 1) does not ‘follow’ the source.

In my previous example, sheet2!H93 becomes sheet2!H20. Sheet 1 is still looking at sheet2!H93 and everything else it’s looking for has been shifted.

#20

The sort function is awful in general - try using the index and match functions, or indirect, or offset, or vlookup, or something to do what sort would do, but through formulas. Or build pivot tables (I oppose these too, but less so than sorting). If you intend to use formulas at all, avoid sorting at all costs, and your life will be happier.