• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Relative reference with Index/Match function

R

Ramirez

New Member
Hi Hui,


Good evening,


I need to use a combination of index/match function in a cell and that should be copied 7500 rows downwards and 356 columns across with only some cells carrying relative reference. Which is the best choice of action in that kind of scenario. For eg: If I use this reference (H$13)with a row lock I have difficulty in copying the formula across with a row column lock ($H$13) with another variable in the formula.


In other words, in a formula, one variable goes with a rowlock which goes 7500 rows deep(to be copied to 7500 rows deep and that should be copied across as well) and another variable with row column lock which goes 356 columns across (to be copied downwards as well)


I have difficulty in copying the formula to 7500 rows downwards and 356 columns across which is tedious to make changes to the variables with opposite reference in a formula. Is there any shortcut method ?? I know one method giving correct reference to higher side no of cells sacrificing another side eg: giving correct ref for 7500 row side and sacrificing column side (356 columns which will be left with incorrect ref to be corrected later).


Please try to understand the picture of the problem with what I have tried to say above. In case more info needed, will be given as you need.


Thanks for everybody who want to give a try.


Regards

Carlos
 
I'm afraid I can't picture what you're trying to do. It sounds like you are having trouble figuring out which references need to be absolute/relative.


Perhaps you could give a short example, like this

Original formula:

=A2+B2

Copied to right should become:

=A2+C2

Original formula copied down becomes:

=A3+B2


In which case, solution would be:

=$A2+B$2


Letting us know what you have and what you want this way will give us the best chance at helping you.
 
Hi Carlos,


Thank you for posting in Separate Personal Forum.. :)


Yes, with perfect use of $ in different place, all types of combination is possible..

I also agree that Sometime its not possible to manage both side.. ROW and COLUMN


Beside that excel have a Cheat Code.. so that you don't have to sacrifice any ROW or COLUMN..

Just use Find & Replace with the combination of Look in : Formula

and replace the exact thing..


But that's also depend upon the demand of the sheet...

Can you please upload the file with requirement.. so that I can decide.. exactly what need to find and replace..


Regards,

Deb
 
Carlos


Sharing even a small part of the data, just enough to show the problem may be the best way to help you here, Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi All,


Anyways this is the replica of the problematic thing.


https://hotfile.com/dl/165055522/b91f674/State_Rainfall.xlsx.html


My problem starts at the "look-up_array" variable in the index function. It behaves in a different way with others. See the other relative references. I want to see the Top 5 rainfall states by taking inputs from their respective figures. The actual data almost works in the same way. I want to see the names for thousands of figures seeing their respective figures. Here we have 10+/- columns and 50 rows where as in the actual data it is 356 columns and 7500 rows.


Regards

Carlos
 
Carlos


I am confused as everything looks ok and like it is working


I would change C61 to
Code: 
=INDEX($B$2:$B$51,MATCH(C54,C$2:C$51,0),0)

That will allow you to copy the formula across and down


I would also suggest putting your summary area at the Top of the Data in Rows 1:20

then have the data below that
 
Hi, Carlos!

Adding my two-cents, I'd change C54 formula to:

=K.ESIMO.MAYOR(C$2:C$51;FILA()-FILA($B$53)) -----> in english: =LARGE(C$2:C$51,ROW()-ROW($B$53))

so as to let copying formula across and down too.

Regards!
 
SirJB7,


This is working fine. Thanks for your effort.


Hui,


What you can see there is the processed data before my enlightenment, worked with each cell separately. What I am trying to do there is " trying to write a formula that contains some variables with relative ref and some with absolute ref, But the formula should be applied downwards and across. Being in the C61 and selecting till N65, try to give a single shot that should achieve my required result i.e. "Showing the Top 5 State names".
 
SirJB7,


This is working fine. Thanks for your effort.


Hui,


What you can see there is the processed data before my enlightenment, worked with each cell separately. What I am trying to do there is " trying to write a formula that contains some variables with relative ref and some with absolute ref, But the formula should be applied downwards and across. Being in the C61 and selecting till N65, try to give a single shot that should achieve my required result i.e. "Showing the Top 5 State names".


Thanks for everybody


Regards

Carlos
 
You must log in or register to reply here.
Back
Top