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