×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Power Query - Referencing to cells

Power Query - Referencing to cells

Power Query - Referencing to cells

(OP)
Hi
Hoping someone can assist.

1. I am running a power query to extract the file address and also the amount from the columns from a file location
2. Then, i used a vlookup formula to pull through the numbers extracted above

Currently, without using power query, I would reference the cell from the other workbooks and double clicking the cell will open up the workbook.

Is there a method to achieve pulling through the number and also the number in the cell being a hyperlink?

Thanks.

RE: Power Query - Referencing to cells

(OP)
Is there any way for power query to extract cell address?
Instead of the value in the cell address, I would like it to return the cell reference number.
E.g. A1

I have only been able to pull through path and the sheets so far.

Thanks.

RE: Power Query - Referencing to cells

With Table.AddIndexColumn you can add index to your table and next search it keeping index. Knowing top-left cell of searched range and index of matched data there is no problem to build address.

combo

RE: Power Query - Referencing to cells

(OP)
Hi Combo
I have added Index as suggested.
Not sure what you meant by the next step to build address?
What i need is the cell address from the source file.
Thanks.

RE: Power Query - Referencing to cells

Guess that index is related to row in the worksheet, may require adjustment by adding constant. So combining, say, "A" with the query result for modified index you should get addresses.

combo

RE: Power Query - Referencing to cells

(OP)
Hi Combo
After adding index column, it gives me a sequence starting from 1 or whatever number i choose to start with.
A bit confused. if you dont mind elaborating a bit.

E.g. Source file - Data is is cell B24

Thanks.

RE: Power Query - Referencing to cells

If your table starts in row 1, data in row 2:
1) before any filtering, add index to new Col_1, 1 refers to row 2, 2 to row 3 etc.,
2) add new column Col_2, add 1 to Col_1, values in Col_2 correspond to rows in source,
3) add new Col_3, concatenate "B" and Col_2, you get a list of addresses in column "B" in source,
4) delete Col_1, Col_2,
5) process your data keeping data in Col_3.

combo

RE: Power Query - Referencing to cells

(OP)
Hi Combo
I previously added 2 columns
1. Filtered to get Balance
2. Filtered to get $

The problem is that the cell address referencing to "Balance" text and $ amount is never in the same row.

Will the suggestion above work?

Thanks.

RE: Power Query - Referencing to cells

VLOOKUP in PQ is replaced by joining tables. If each table has, just after Source definition, the above row calculation, you can process row IDs separately for each table. The problem can be if the table left-top cell has no fixed address.
If you join the same table, create support query to add only proper row to output and some clean-up data formatting, leave it only as connection. This query can be a source for the data processing (Source=QueryName).

You can also add =ROW() in excel source to get row directly in table.

For query output in the worksheet you can add calculated column, you will get a combination of query result and excel structured table formulas (as HYPERLINK) in single table.

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close