VlookUp

Sue van Niekerk

Hi all,

I have an issue that I am hoping one of you can come up with a solution.

I have a confidential HR register, I would like to link Sheet A to it just extracting names and department, that will update as staff leave and join. are there any ideas ( My Manager for obvious reasons doesn't want anyone but himself accessing that register - so need away to extract info ( cant use a report as the sheet A needs to be able to link to another sheet for drop downs

Genevieve P.

Hi @Sue van Niekerk

Is there a unique identifier on the rows we could use to match across sheets, that you can set up in Sheet B well ahead of time?

For example, if you have an auto-number column in Sheet A, then you can set up your current sheet to have all the same numbers + 50 extra (as an example).

Then you can use an INDEX(MATCH formula to bring across the Name in one column and the Department in another column, using the number as the MATCH between the sheets.

An INDEX(MATCH works like this:

=INDEX({Column with value to return}, MATCH([Value to match]@row, {Column with value to match}, 0))

You'll just need to ensure there are always extra blank rows in your current sheet so when new rows are added to Sheet A they will start to populate Sheet B. Does that make sense?

Cheers,

Genevieve

Sue van Niekerk

HI Genevieve

No there is no unique identifier, for now i have just used to achieve something that we can use

Genevieve P.

Hi @Sue van Niekerk

Cell linking will work, but it links each cell with a specific cell on the other sheet. This means if rows are added and removed it won't automatically bring in the new row information to this second sheet.

Perhaps you could set up a formula on the source sheet to check if each row has a match in the second sheet? It would be a COUNTIF formula to see if there's a COUNT of 1 in the other sheet or not. Then you can flag rows with 0, so you know to cell-link in the information into Sheet B.

Sue van Niekerk

Hi Genevieve - this is the second sheet - it is being used to create "Drop Down" linking in multiple other sheets

So as it currently stands - Primary column and Col 2 are linked to our Master HR Register

In this second sheet we have a formula creating the columns on the right

My problem is there is no unique number in the master sheet- its all names - and i dont want a count i need the names

hope this makes sense :-)

Genevieve P.

Hi @Sue van Niekerk

Yes that makes sense! Are you able to add columns to both sheets though? Then you could add an auto-number column to use to match across your sheets.

The way I would do this is to set an Auto-Number column, then a Helper Number Column to Rank your rows so they always stay in the number of rows.

=RANKEQ([Auto Number]@row, [Auto Number]:[Auto Number], 1)

Example Source Sheet:

See that I deleted the original rows 3 & 5. This means the Auto Number kept counting higher (it skips 3 and 5), but my helper formula column uses RANKEQ to ensure the number is still the row number.

Then I can use this Helper Number to Match across the sheets and bring back the Name. All you need to do is list more numbers than your original source sheet has:

That way as new rows are added in the source sheet it will automatically come in to this destination sheet.

=IFERROR(INDEX({Names}, MATCH([Helper Number - Manually Added]@row, {Helper Number}, 0)), "")

When you see that all your rows are filled up, just drag down the number on the left to make sure there aren't any new matches. Does that make sense? You'll always want extra blank rows in your destination in case your numbers increase.

Otherwise, you can keep cell-linking if that works for you!

Cheers,

Genevieve