I'm sure there are but everyone has tended to learn on the job in my team - the thing is, once you know how formula work and how to look them up you can usually find out what you need. Most firms will have their own functions built it (eg mortality tables) too.

Format > Conditional Formatiting
Formula Is.. =A1="Paid"

i thought you could do it without specifically referencing the cell but i just checked and it doesnt work

Cool, feel free to answer some questions.

+1 to never reading an excel book you will probably just be wasting your time

From PM:
The fantasy site was www.ultimatefooty.com, they have stats for the last few years on goals, kicks, handballs, marks, tackles

1. Import results into excel
2. ran a macro to randomise a draft with 12 players then worked out what the average weekly score would need to be to win in each category (i checked at the end of the season and it was almost exact)

3. as the draft was going, kept track of the two stats that were the biggest % away from average and looked at all available players weighted by % behind e.g.
cat1pct = tmpcat1diff / (tmpcat1diff + tmpcat2diff)
cat2pct = tmpcat2diff / (tmpcat1diff + tmpcat2diff)

then id rank the two categories added together (multiplied by each weighting) and the lower score is the best player available in those two stats

"=RANK(" & col1 & "2,$" & col1 & "$2:$" & col1 & "$600)*" & cat1pct & "+RANK(" & col2 & "2,$" & col2 & "$2:$" & col2 & "$600)*" & cat2pct

Excel is Microsoft's killer app and is substantially responsible for the success of Windows. Joel Spolsky, who was one of the early project managers (now sort of a software guru), says that by far the most common use for Excel is manipulating lists. So while all the programmers want to replace Excel with an RDBMS (even one as miserable as Access), this is mostly missing the point.

Btw, if you are just learning to code, you will be amazed at how much time and hair-pulling date arithmetic requires. For a while I tried to get everyone to store dates as integers formatted YYYYMMDD.

Also, cool story bro re: obscure date functions, young Spolsky and Bill Gates: http://www.inc.com/magazine/20080701...lory-days.html

why do array tables kill my cpu?

I have been using Excel since around version 5.1 and I know all of the basic stuff. I have never written a macro, nor do I know were to begin to write one. Where do I start?

Lets say I have several sheet inside of a workbook that have 4 columns. I'll use random numbers here. Multiple sheets would have similar data.

Each row is a different data point consisting of a footage, 2 data measurements, and remarks.

Column A would represent a common measurement say footage for instance. The footages between sheets may or may not match exactly every time.

Column B and C would represent different data measurements at that footage.

Column D would be remarks

Sorry the columns don't line up since this site does not like spaces. (, = next column), * = blank cell in column

Sheet 1
A, B, C, D,
1, 25, 35, Random Remark,
2, 19, 26, Random Remark,

Sheet 2
A, B, C, D,
1.5, 15, 55, Random Remark,
2, 49, 86, Random Remark,

I want to make a master sheet inside of the workbook that would organize these numbers together for evaluation. So the master sheet would look like this:

A, B, C, D, E, F, G,
0.5, *, *, *, *, *, *,
1, 25, 35, *, *, Remark Sheet 1, *
1.5, *, *, 15, 55, *, Remark Sheet 2
2, 19, 26, 49, 86, Remark Sheet 1, Remark Sheet 2,

Column A would be a series of numbers in 0.5 foot increments and may or may not have any information transferred from the individual sheets.

Columns B and C would take the measurements from sheet 1 and place them at the correct footage in the master sheet. Column F would be the remark from that footage.

Columns D and E would take the measurements from sheet 2 and place them at the correct footage in the master sheet. Column G would be the remark from that footage.

Does this make sense? How would I begin to write a macro that would accomplish this. I have seen some (If this = that then do this) type of stuff before but I have no idea how it works. I think that is what I have to do here.

Any ideas? Thanks,

So basically you excel at Excel

wow, really interesting thanks for that

Do you mean array formulas? they take huge amount of processing power if you do it on huge ranges. The formula i posted earlier:


SUM((data!$A$2:$A$1000=$A2)*(d ata!$B$2:$B$1000=B$1)*(d ata!$C$2:$C$1000))

froze my excel when i did

SUM((data!$A:$A=$A2)*(d ata!$B:$B=B$1)*(d ata!$C:$C))

because it multiplies every cell by every cell in that range

Use the macro recorder then edit it as you need, i posted an example of a recorded sort earlier

For future reference if you use CODE tags it will preserve your spacing

You wouldn't need to use a macro unless you wanted to automate the populating of column A, but if you are happy entering .5 increments then all you need is a vlookup

Master Sheet

B2: =if(ISERROR(vlookup(A2,sheet1!A: D,2,0)),"*",vlookup(A2,sheet1!A: D,2,0))

C2: =if(ISERROR(vlookup(A2,sheet1!A: D,3,0)),"*",vlookup(A2,sheet1!A: D,3,0))

etc

Is that what you meant?

Basically, I also just realised that you are not "deeznuts"

One very simple question that will greatly improve the lives of many restaurant managers.

1. Is there a way to just plug in numbers to a cell without using the "=" (instead of =343.34+343.34+909.99 I type 343.34+343.34+909.99 into the cell).

We just converted to Excel from Lotus and moving your left hand all the way over to the equal button is just too much to bear. (for my colleagues)

I would be a hero if I could solve this.

Woops, i forgot if you add a + sign first it will act as an equals so

+343.34+343.34+909.99 is the same as =343.34+343.34+909.99

This is very close. I have never heard of vlookup before.

I have been messing around with the insert vlookup function and I keep getting an error. I tried pasting your formula in directly but that also didn't work.

I think the problem is my master sheet contains some footage numbers in column A that don't match the table array that I have selected and it causes an error.

Is there a way to make it ignore the rows that do not match any of the footage numbers in the master sheet column A and have it leave those rows blank or put in an *?

Sorry i didn't actually test it, i had to put a space in between the colon and the D because it changed it to an emoticon

=if(ISERROR(vlookup(A2,sheet1!A,2,0)),"*",vlooku p(A2,sheet1!A,2,0))

the part If(ISERROR(vlookup(A2,sheet1!A: D,2,0)) checks if the footage is not found (it will return an error) and returns "*" if its not an error (i.e. it is found) then it return the result of the vlookup

try removing the space between the colon and D and make sure the sheet names are correct and see if it still doesnt work

You can just try =vlookup(A2,sheet1!A: D,2,0) but will return an N/A if the value isn't found

DATA -> Remove Duplicates is a nice feature

Or in 2007 (and 2010):
=iferror(vlookup(A2,sheet1!A: D,2,0),"*")

(remove the space after : )

Is there any way to have Excel increment the row instead of the column when filling a row, and vice versa?

For example, if I have =A1 in a cell and fill horizontally, have it fill in =A2, =A3, ..., =An instead of =B1, =C1, ..., =Cn.

Thanks

Amazing!

There is no easy way, you would have to use the INDIRECT function

If you put:
=INDIRECT(ADDRESS(COLUMN( )-1,1))

in Cell B1 that is the equivalent of "=A1" (column refers to the column the formula is in, then minus 1 makes it A1)

If you copy that across it will then be =A2, =A3 because it is using the current column making it: address(2,1) address (3,1) etc

hope that makes sense

That's what was afraid of. Oh well, thanks.

Does anyone know if there is an easy way to select a range by reference to another column? Eg if I want to copy a formula from cell B1 down to B100 because column A has data down to row 100. In Lotus 1-2-3 I think you just click on cell B1, then press shift+left, shift+down, shift+right and then you can just copy down as you have highlighted range B1:B100. I know you can do something in Excel with the mouse but it's fiddly and the 1-2-3 way is so much quicker.

If you are just talking about shortcuts:
1) Copy cell B1
2) left, ctrl+down
3) right, ctrl+shift+up
4) paste

If you don't mind doing two steps you could enter the formula "=A$1" then copy down (making it a2, a3 etc) then copy the range and right click > paste special then tick the transpose box and it should work

Are you really good with VB as well?

I've actually never written a standalone VB app, but from what I understand they are essentially the same thing except VBA has to be launched from within an application.

I think originally VBA was a stripped down version of VB but now VB uses VBA as its code base and other than a couple of commands you should be able to copy a VB program into VBA and run unchanged.

Is there a way I can set up a cell to automatically enter the timestamp of a certain entry? I know there is the NOW() function but that changes everytime Excel is calc'd. I also know I can copy the time entry and paste special values. Just too manually bothersome to do. I have a workbook where I enter my time spent on certain projects. For now, just date and # hours spent is fine. But I'd like to be able to know what time of day I spent those hours. Thanks!

You might be able to forgive them that one eh ?

You could do something in the sheet change macro. Right click on the sheet tab and go "view code" then paste
Whenever you enter data in range A1:A10 it will put a date in column B

or you could automatically change it like this
yes yes