Reverse Formula

User avatar
adam
SilverLounger
Posts: 2346
Joined: 23 Feb 2010, 12:07

Reverse Formula

Post by adam »

Hi anyone,

I have a workbook with two sheets where one is source sheet and the other is the destination sheet. I have formulas in my destination sheet where I collect data from the source sheet according to the format I need. The formulas work fine and does the needful.

My question is how can I edit or modify the formula so that when I place the formulas in A2,B2, And C2 the data from the source sheet gets automatically filled instead of having to write the formulas in each row.

Also how could I remove the square brackets from the column headers when they get copied.

Any help would be kindly appreciated.

I've attached the workbook for refence.

Note: my data in the source sheet will be increasing as the user adds data to the source sheet.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reverse Formula

Post by HansV »

Which version of Excel do you need to support?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2346
Joined: 23 Feb 2010, 12:07

Re: Reverse Formula

Post by adam »

Excel 2019
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reverse Formula

Post by HansV »

Excel in Microsoft 365 has new functions that do what you want, but those functions are not available in Office 2019.
But you can expand the ranges in the formulas so that they will still work when data are added.
See the attached version.

ReverseLookupWhenNumberInTable.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2346
Joined: 23 Feb 2010, 12:07

Re: Reverse Formula

Post by adam »

Thank you for the help Hans.

In that case, can my recommendation be done in googlesheets?

I tried copying the initial formula. It gave me parse error.

Can the formula be changed into an array formula in google sheet for the three columns?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Reverse Formula

Post by HansV »

I have no experience with Google Sheets, sorry.
Best wishes,
Hans

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: Reverse Formula

Post by hamster »

You can try Power Query
s666-PQ-ReverseLookupWhenNumberInTable.xlsx
You do not have the required permissions to view the files attached to this post.

snb
4StarLounger
Posts: 540
Joined: 14 Nov 2012, 16:06

Re: Reverse Formula

Post by snb »

In VBA:

Code: Select all

Sub M_snb()
  sn = Sheet1.Cells(1).CurrentRegion
  ReDim sp((UBound(sn) - 1) * (UBound(sn, 2) - 1), 2)
  
  For j = 0 To UBound(sp) - 1
   x = j \ (UBound(sn, 2) - 1) + 2
   y = j Mod (UBound(sn, 2) - 1) + 2
   sp(j, 0) = sn(x, 1)
   sp(j, 1) = Replace(Mid(sn(1, y), 2), "]", "")
   sp(j, 2) = sn(x, y)
  Next
  
  Sheet1.Cells(1, 8).Resize(UBound(sp) + 1, UBound(sp, 2) + 1) = sp
End Sub

User avatar
adam
SilverLounger
Posts: 2346
Joined: 23 Feb 2010, 12:07

Re: Reverse Formula

Post by adam »

Late reply. Was away. But thank you all for the help. Much appreciated.
Best Regards,
Adam