A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

[INDIRECT] Syntax Help



 
 
Thread Tools Display Modes
  #1  
Old March 30th, 2010, 05:51 PM posted to microsoft.public.excel.misc
ThunderBlade
external usenet poster
 
Posts: 11
Default [INDIRECT] Syntax Help

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?

Ads
  #2  
Old March 30th, 2010, 10:06 PM posted to microsoft.public.excel.misc
Max
external usenet poster
 
Posts: 8,574
Default [INDIRECT] Syntax Help

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?


  #3  
Old March 30th, 2010, 10:42 PM posted to microsoft.public.excel.misc
ThunderBlade
external usenet poster
 
Posts: 11
Default [INDIRECT] Syntax Help

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?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 OfficeFrustration.
The comments are property of their posters.