Syntax Help
in [General]
|
From: ThunderBlade on 30 Mar 2010 12:51 OK MAX, Hope this reply is correct enough. And if you would have read what I stated carefully, It didn't seem to work. I did use your suggestion of doing the calcs on the host Worksheet, but I'm still not getting the results I need. I Understand the phrase that you gave before, but I'm not seeing the &rows($1:1)+1) portion....can you explain it? "Max" wrote: > > I will need to copy that code from row to row.... > > linking each row as i go down the list. > > .. What would the Syntax need to be to autocopy down a column? > > Hey..if you had read carefully, I covered the above point in this part of my > response: > > And if you need it to increment copying down, > > use something like this: INDIRECT("'"&J2&"'!HZ"&rows($1:1)+1) > > p/s: Btw, learn how to reply properly in the newsgroup. Don't reply to your > own post, reply to the responder > -- > Max > Singapore > --- > "ThunderBlade" wrote: > > That didn't seem to work. It worked as long as the rows didn't have to move. > > Once I have the code line in the "PeriodHours" Worksheet I will need to copy > > that codce from row to row....linking each row as i go down the list. > > > > Basically, the code would need to do this as I go down the rows: > > =IF(B5="Salary",96,INDIRECT((J2)&"!HZ2"-INDIRECT((J2)&"!IB2"-INDIRECT((J2)&"!ID2")))) > > =IF(B5="Salary",96,INDIRECT((J2)&"!HZ3"-INDIRECT((J2)&"!IB2"-INDIRECT((J2)&"!ID3")))) > > =IF(B5="Salary",96,INDIRECT((J2)&"!HZ4"-INDIRECT((J2)&"!IB2"-INDIRECT((J2)&"!ID4")))) > > > > This would populate the data onto the "PeriodHours" Worksheet from the > > subsequent worksheets. What would the Syntax need to be to autocopy down a > > column?
From: Max on 30 Mar 2010 17:06 rows($1:1) is the incrementer term, it returns 1, then 2, 3, etc when you copy it down in any starting cell. rows($1:1)+1 simply adjusts it to start the series at 2 instead of 1, as the example: INDIRECT("'"&J2&"'!HZ2") starts at cell HZ2, not HZ1. You'd probably need to fix the point to the sheetname in J2 as well when you copy down: INDIRECT("'"&$J$2&"'!HZ"&rows($1:1)+1) -- Max Singapore --- "ThunderBlade" wrote: > OK MAX, > Hope this reply is correct enough. > And if you would have read what I stated carefully, It didn't seem to work. > I did use your suggestion of doing the calcs on the host Worksheet, but I'm > still not getting the results I need. > > I Understand the phrase that you gave before, but I'm not seeing the > &rows($1:1)+1) portion....can you explain it?
From: ThunderBlade on 30 Mar 2010 17:42 Thank you. I played around with the ROWS switch and figured out what was going on there. You were a big help. "Max" wrote: > rows($1:1) is the incrementer term, it returns 1, then 2, 3, etc when you > copy it down in any starting cell. rows($1:1)+1 simply adjusts it to start > the series at 2 instead of 1, as the example: INDIRECT("'"&J2&"'!HZ2") > starts at cell HZ2, not HZ1. > > You'd probably need to fix the point to the sheetname in J2 as well when you > copy down: INDIRECT("'"&$J$2&"'!HZ"&rows($1:1)+1) > -- > Max > Singapore > --- > "ThunderBlade" wrote: > > OK MAX, > > Hope this reply is correct enough. > > And if you would have read what I stated carefully, It didn't seem to work. > > I did use your suggestion of doing the calcs on the host Worksheet, but I'm > > still not getting the results I need. > > > > I Understand the phrase that you gave before, but I'm not seeing the > > &rows($1:1)+1) portion....can you explain it? >
|
Pages: 1 Prev: How do I multiply the content of a cell by a number Next: can't open Excel file |