Hi,
Greetings for all!
I have a formula at Sheet2 as ...something(from this beautiful forum)..Sheet1!$A$2...
When I insert 10 rows in Sheet1, formula in Sheet2 changes into ....Sheet1!$A$12..
How to prevent/fix formula to $A$2, how to fixate it to A2
Thank You in advance!!
You could try changing it to INDEX(Sheet1!A:A,2)
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Could not. Coupe of thousands rows, so, slow calculation.Originally Posted by TMS
Cannot, also, use volatile ADDRESS, CELL, INDIRECT, they ask "Do you want to save changes...
For a cou-le of thousand rows:
=INDEX(Sheet1!A$1:A$2500,2)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
"Thanks for the suggestion", maybe?
Not sure why it would be slow as the row index is fixed.
Whatever, you could try putting INDEX(Sheet1!A:A,2) in a cell on Sheet2 and referring to that instead.
And what about range? Sheet1!$A$2:$A$11?? Maybe, INDEX(Sheet1!A$1:A$2500,{2,3,4,5,6,7,8,9,10,11}) right??
I have no idea what the original formula was, what the worksheets and data are like, or what you are trying to do.
You have a couple of solutions, and your own idea based (presumably) on what has been proposed. I suggest that you test them out
For my relatively complicated problem, Excel 2013 version, is to use INDEX(whole column,2):INDEX(whole column,counta(whole column)). The condition was because I didn't want OFFSET, ADDRESS or similar, also the data set is not a table, also, the data set is updated daily with new data that is in the first 10 rows and everything below the row 3000 is deleted. THANK YOU all for your interest and love for Excel
Please Login or Register to view this content.
You're welcome.
I would recommend you use the same column to determine the range size for all ranges. This ensures a consistent row count. You need to choose a column that you can guarantee is fully populated. Other columns may have blanks and return a differnt row count.
For example:
Formula:
Please Login or Register to view this content.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
"INDEX(whole column,2):INDEX(whole column,counta(whole column))." is VOLATILE, uhh, HELP PLEASE, TMS, zbor, AliGW......
I can't help further with Excel 2013 - sorry.
INDEX isn't volatile, as far as I know.
If you do not want to use a volatile function and want to sum column B based on conditions in rows and strings in column G, with the number of rows from 2 to the maximum (e.g., 4000), you can use the following structure:
where "S-10;S-11" are the values to exclude in column G.Please Login or Register to view this content.
So, for 1st 10 rows:
or for rows >11Please Login or Register to view this content.
Please Login or Register to view this content.
Quang PT
Using :INDEX() as the second part of a range reference is semi-volatile only (i.e. recalcs at workbook open).
Everyone who confuses correlation and causation ends up dead.
The INDEX function itself is not volatile.
Using INDEX in a formula to create a sub-range does not make it volatile,
but
it may affect calculation performance if used in complex formulas or with large ranges
This is volatile INDEX(whole column,2):INDEX(whole column,counta), at least for me(Excel 2013,32bit).
Can I use some sort of "helper sheet" with rows =Sheet2!A2....nah, after row insertion they would become A12.
bebo021999's That part "(ROW($B$2:$B$4000)>1)*" is completely useless for my version of Excel, it simply do not work, only INDEX work.
How are you determining that? It is only semi-volatile in any version I have used.
"(ROW($B$2:$B$4000)>1)*" is completely useless for my version of Excel, it simply do not work
see attached ...
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Semi or not, it's volatile, my older colleague just hate "Do you want save changes.." prompt.
It is working. Currently at home(Excel2016 x64). But my Sheet1 "There(sheet2) will be insert 10 rows from 2-11(new wday new report), also deleting everything below 3511 row.", hence, cell range $B$2:$B$4000 would become B12:B4000
see attached ...
Then you appear to be out of luck as you have ruled out every alternative, and your entire approach, from what detail you have given, appears to be inefficient to me.Semi or not, it's volatile, my older colleague just hate "Do you want save changes.." prompt.
I also tried split formula(cell S7) into two part, that also wasn't working. This also gives #VALUE!Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks