Ask Your Question
0

LibreCalc- correspond few columns and rows across sheets

asked 2017-04-07 16:30:48 +0200

zewny gravatar image

Hello, I'm Libre Calc newbie that never went beyond SUM function. I have a complex and specified problem- I don't expect help, but if there is even a slight chance I'll get it, it's worth trying, so

I have three sheets:

  • Governments revenues in domestic currency (sheetA),
  • exchange rates- US$ per national currency (sheetB),
  • and a sheet where I need government revenues shown in $dollars (sheetC). image description

SheetA has columns with countries and revenue type listed (central gov/general) and rows with years listed. XY positions are different countries revenues in domestic currency in exact year. I need each XY position to correspond with SheetB which has three columns: country name, year and corresponding exchange rate.

Now I need to match SheetA country name column and year row with SheetB country name and year columns in a way that revenue in domestic currency A is multiplied by exchange rate B then value is returned to required C cell.

Thus, SheetC is built like SheetA - two columns with country name and revenue type (central government/general) and one row with years, giving accurate SheetA XY positions. For curious- next I will compare 2000 private corporations and their factors with countries organisations in a scale of time, and try to make sense of their relative dynamics.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-04-21 12:22:29 +0200

zewny gravatar image

It was all about proper formula: =LOOKUP(REVENUE_DOLLAR.D$1,REVENUE_DOMESTIC_CURRENCY.D$1:REVENUE_DOMESTIC_CURRENCY.AU$1,REVENUE_DOMESTIC_CURRENCY.D3:REVENUE_DOMESTIC_CURRENCY.AU3)*SUMIFS(EXCHANGE_RATES.$D$2:EXCHANGE_RATES.$D$10313,EXCHANGE_RATES.$C$2:EXCHANGE_RATES.$C$10313,D$1,EXCHANGE_RATES.$B$2:EXCHANGE_RATES.$B$10313,$B3)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-04-07 16:30:48 +0200

Seen: 51 times

Last updated: Apr 21