Convert Indirect row reference

[Harry Stevens]

To All,
I would like to convert:

=IF(TRIM(LEFT(INDIRECT("'Budget"&RIGHT(YEAR($H$1),2)&"'!A29"),1))<="9",
INDIRECT("'Budget"&RIGHT(YEAR($H$1),2)&"'!A29"), "")

to something that will replace the '!A29 with a row function. When I
copy the formula down a worksheet, I have to manually edit the row
reference each time. I have looked at and tried the ROW function, but
cannot seem to get it to work correctly. Any and all help would be
appreciated.

My data runs from A6 to A40 and I am using excel 2003.

Thanks
Harry

 

[Dave Peterson]

Since you're putting the formula in Row 6 to start and you want to look at row
29 first:

....&"'!A"&row()+23), ...

(in both spots)

 

[Harry Stevens]

Dave,
Thanks for replying. I guess I should have been a little clearer
about the layout...late nights of staring at a worksheet.

I have a sheet "Final Budget" and several sheets named Budget08,
Budget09, etc.. I need to submit our final budget in a different layout
from our budget worksheet. I know I should change the budget worksheet
to the final layout, but for several reason I can not. So I am trying
to work around this.

On the final budget worksheet the formulas start in B5 through B14
and the data in the appropriate budget worksheet starts at A6 and goes
through A15 with a two row break with label text on the budget work
sheet and three on the final worksheet. Then the final budget worksheet
starts again at B18 through B40 and the appropriate budget worksheet
starts again at A18 through A40. So the data in Budget08!A6 goes into
Final Budget!B5, etc.

I had hoped ROW would work, but the rows are not the same in each
area and worksheets. And sometimes I have to insert/delete rows for
clarity.

Does this help?

Harry

 

[Dave Peterson]

I wouldn't use this kind of formula.

I'd try to set up the data so that there were unique keys so that I could use
=vlookup() or =index(match()) to bring back the data I need.

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

 

[Harry Stevens]

Dave,
I had thought about that, but each budget year sheet may contain
different budget item from year to year. So there is no way to set up a
unique key. The data in the budget work sheet is the unique key.

Thanks for some more ideas.

Harry

 

[Dave Peterson]

Can you use multiple columns to determine a unique key?

Saved from a previous post:

If you want exact matches for just two columns (and return a value from a
third), you could use:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100),0))

(all in one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can only use the whole column in xl2007.

This returns the value in othersheet column C when column A and B (of
othersheet) match A2 and B2 of the sheet with the formula.

And you can add more conditions by just adding more stuff to that product
portion of the formula:

=index(othersheet!$d$1:$d$100,
match(1,(a2=othersheet!$a$1:$a$100)
*(b2=othersheet!$b$1:$b$100)
*(c2=othersheet!$c$1:$c$100),0))