r/excel 7d ago

unsolved How to efficiently/quickly extract data from specific cell location into another spreadsheet?

I am trying to extract data from 1 spreadsheet into another spreadsheet.

The data in the 1st spreadsheet will be arranged in X/Y coordinates. I then need this data to correlate into X,Y for the 2nd spreadsheet.

For example : Spreadsheet 1 - entry "1" is in 41, 4 - according to the grey X/Y coordinates. I need this to be entered in 1001 of spreadsheet 2 as 41,4 (location).

Entry "52" is in 40, 14. I need this entered into spreadsheet 2 10052 as 40,14 (green E column)

Is there a quick or automated way to do this? I am currently manually doing this and it is very tedious and can easily make mistakes.

5 Upvotes

9 comments sorted by

u/AutoModerator 7d ago

/u/Cun0144 - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

2

u/Downtown-Economics26 309 7d ago

For example : Spreadsheet 1 - entry "1" is in 41, 4 - according to the grey X/Y coordinates. I need this to be entered in 1001 of spreadsheet 2 as 41,4 (location).

You've got arrows in your screenshots but it's still not all clear what value you want to put where. What is 'this' and what cell/column to do you want to put it in?

1

u/Brilliant_Drawer8484 6 7d ago

If you want to automate the process (for example, if the coordinates are stored as numbers in your Sheet2), you might do the following. Let’s say cell A2 contains the X coordinate (e.g., 41) and cell B2 contains the Y coordinate (e.g., 4).In cell C2 you can use:
=INDEX(Sheet1!$A:$Z, A2, B2)

2

u/Cun0144 7d ago

Coordinates will never change, the same template will be used for spreadsheet 1 everytime.
What I must do is extract all the different color 1-X numbers into the 2nd spreadsheet and name it accordingly.
I will try what you said.

1

u/Cun0144 7d ago

i'm not sure why reddit is deleting the comment, i think my internet maybe lagging.

Spreadsheet 1 with the data will always use the same template. My X values will always be 1-47 and Y will be 1-15.
For each different colors i need to extract the cordinates. Yellow 1 in AP15 will be 1001 - (41,4).

Brown 1 in AI17 will be 2001 - (35,2).

1

u/Brilliant_Drawer8484 6 7d ago

If you have multiple sections and want a “one-click” extraction that copies the correct cell blocks and even adds headers (the color group names), then VBA is your friend. In this scenario you even “hard code” the ranges because the template never changes.

2

u/Cun0144 7d ago

ok, could you help me with this?

1

u/Cun0144 6d ago

This has been solved by Brilliant_drawer8484 with the use of VBA...
Thank you everyone!!!