PDA

View Full Version : [SOLVED:] INDIRECT Row Reference and Increments


lordhelmet
12-14-2016, 05:15 AM
I've been plugging away at this darn INDIRECT formula for days now and can't seem to get where I need to.

I need cells in the Employee sheet to reference cells in the Times sheet that is horizontally listed. The Employee sheet has reference cells like. A for sheet name. B for row reference. C for column.

A B C
1Employee 2 2


So Employee sheet needs to reference the correct row, and each cell with the indirect formula needs to column increment +1 to return next value in the Time sheet. Hope that makes sense

=INDIRECT("'"& $H$1 &"'!"&CELL("address",B2))

The above formula works ok but has to be dragged over multiple cells.
This seems counter intuitive if working with multiple sheets, which I am.
Each sheet should have the same INDIRECT formulas but use different reference cells right?
mancubus
12-14-2016, 07:49 AM
?

=INDIRECT(A1&"!"&ADDRESS(B1,C1))


when you copy the formula down row references increment automatically.
lordhelmet
12-14-2016, 08:05 AM
?

=INDIRECT(A1&"!"&ADDRESS(B1,C1))


when you copy the formula down row references increment automatically.

This achieves the same of what I have. I just need a way to increment the column location on the next cell regardless of location. Like for Time the indirect code would be the same but Column +1. Check attached

17832
lordhelmet
12-14-2016, 09:33 AM
Found the answer. Seems to be working.



=INDIRECT("'" & H1 & "'!"&ADDRESS(J1,K1+1))

I was hoping to Concatenate the last and first name. How can you do that with an indirect formula? I've been trying this but something is off with it



=CONCATENATE(INDIRECT("'" & H1 & "'!"&ADDRESS(J1,K1)),” “(INDIRECT("'" & H1 & "'!"&ADDRESS(J1,K1+1))
mancubus
12-14-2016, 11:36 PM
ah, yes. enclose the sheet name with single quotes in case it contains any space characters.

btw, i never use "spaces" in the object names.


pls mark the thread as solved if you are sorted.
lordhelmet
12-15-2016, 05:28 AM
There were a few parenthesis missing but Returning a strange value of #NAME?


=CONCATENATE(INDIRECT("'" & E1 & "'!"&ADDRESS(F1,G1)),” “,INDIRECT("'" & E1 & "'!"&ADDRESS(F1,G1+1)))
mancubus
12-15-2016, 07:34 AM
change ” “ to " ".
lordhelmet
12-15-2016, 10:06 AM
Solved! Thanks so much!