Hi Guys,
i am using formula like here:
the issue what i have is that indirect has to be applied. Why? Because source table is always created from scratch - removed and recreated.=INDEX(INDIRECT("RepositoriesCopy[Credentials]");MATCH("Topo";INDIRECT("RepositoriesCopy[Topos]");0)
It is any way to replace indirect like this?
Best,
Jacek
Yes you can use table reference in indirect.
What is your issue now?
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Hi,
thank you.
My issue is that table will be removed and created one again and this is why i ahve to use indirect which is volatile and i am afraid that this can cause issues in the future like calculating workbook all the time,
Best,
Jacek
It's true, In your case indirect is the only inbuilt option which is readily available for you with volatility as side effect.Originally Posted by jaryszek
Otherwise you need to create your own UDF using VBA.
Thank you,
ah it is a pity i have to add this for ideas to microsoft to get rif of indirect function somehow
Best,
Jacek
There would be another option if you just used cell ranges instead of a table...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
It would work assuming specific column is always in the same place in workbook?
What if i will add new column and my desired one will be moved?
Best,
Jacek
Then you're out of luck if it moves!!
thank you Glen
Best,
Jacek
Further to this...
Open the file. IM in purple cells working.
Delete the table.
Create a new one, beginning in same column (A).
Name any other column "Target".
Populate with a few values.
IM still works.
Is that not what you had wanted?
Thank you Glenn!
It is very nice approach. It is close!
the issue still can be that column A can be also moved into for example column C.
So this should refer to entire column based on column Name: "$1:$1048576"
and this "$A:$A" should also be dynamic.
Best,
Jacek
OK... try it now (it still assumes that your Table starts in row 1....).
wow this is amazing!
the best discovery for me in 2022 year for Excel. It is changing my approach to building excel models!
Disadvantage is that formula is not easry to write but is will work like a charm!
Best,
Jacek
LoL. I might believe you if this was 1st January... not 14th April.
It is a bit cumbersome... but no INDIRECT. It just occurred that I had been a bit stoopid in my last reply on this, yesterday. Of course, you can restrict it a bit to n columns, rather than all of them... but it's so easy to select the entire sheet with a single mouse click....
hehe Glenn
yes it will be robust.
I prefer in Excel the simplest solution. Not using very complicated functions. writing lambdas on full screen.
It is easy and nice.
Best,
Jacek
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks